Looking for a way, via a sql query, to find the original close time of an incident ticket. (329 Views)
Reply
Trusted Contributor
lwvirden
Posts: 544
Registered: ‎04-22-2008
Message 1 of 10 (329 Views)

Looking for a way, via a sql query, to find the original close time of an incident ticket.

A user of our system is trying to find a query that would provide his query the original close time of a ticket - if it has been closed at all, of course.

 

While there is a field for the close time, if someone reopens the ticket and then recloses it, that field is modified.

This user is trying to determine the original close time.

 

There are many tables, with lots of information in them, so I was wondering if one of them might have information about tickets and their close times.

 

Thank you for your help!

Please use plain text.
Honored Contributor
Vadim Gorda
Posts: 5,685
Registered: ‎11-10-2008
Message 2 of 10 (311 Views)

Re: Looking for a way, via a sql query, to find the original close time of an incident ticket.

Hello,

I would advice you not to try to dig for that and just configure Audit for the table or just make a new activity action record which will trace every close or reopen made by operator.

Please use plain text.
Honored Contributor
Vadim Gorda
Posts: 5,685
Registered: ‎11-10-2008
Message 3 of 10 (310 Views)

Re: Looking for a way, via a sql query, to find the original close time of an incident ticket.

Actually I really advice to just set activity action.

As example you can take any OOB. In that new activity action you can set condition to be written only if the close action was performed  (it can be status field or an anything).

 

If Closing happens then as activity detail you will set information about who has done that and what was the time when it was actually closed. This way you will capture any number of reopening and reclosing

Please use plain text.
Trusted Contributor
lwvirden
Posts: 544
Registered: ‎04-22-2008
Message 4 of 10 (305 Views)

Re: Looking for a way, via a sql query, to find the original close time of an incident ticket.

I understand what you are saying.
The problem is that the user has a working sql query that gives him a set of records in which he is interested in using to generate reports.
Right now, when using the open time and close time, the reports are skewed incorrectly when someone opens the ticket a week or a month later to add a comment, fix a misspelling, etc.
So having something that could be used in place of the column he is currently using would definitely be better.
Please use plain text.
Trusted Contributor
lwvirden
Posts: 544
Registered: ‎04-22-2008
Message 5 of 10 (304 Views)

Re: Looking for a way, via a sql query, to find the original close time of an incident ticket.

Thank you for this suggestion as well. I am doubtful that this would be able to be "plugged into" his query in a way that would satisfy his need, but it is a good idea in general.

I think a paper on correct steps in writing activity actions - and a different one on correct ways to write notifications - would be useful things for someone to write and make available in the knowlege base!
Please use plain text.
Honored Contributor
Vadim Gorda
Posts: 5,685
Registered: ‎11-10-2008
Message 6 of 10 (298 Views)

Re: Looking for a way, via a sql query, to find the original close time of an incident ticket.

For your task you can tailor additional field for example first.close.time and tailor in logic (format control, display option or JS  or trigger  any spot you prefer) to set to tod() if it is empty.

In this case it will be first filled out when operator closes the ticket for the first time, and for the second time (click, format control anything) it wont be replaced.

Please use plain text.
Trusted Contributor
lwvirden
Posts: 544
Registered: ‎04-22-2008
Message 7 of 10 (289 Views)

Re: Looking for a way, via a sql query, to find the original close time of an incident ticket.

That is a good idea. We will have to look into what all we need to do that.

Thank you so much.
Please use plain text.
Honored Contributor
Vadim Gorda
Posts: 5,685
Registered: ‎11-10-2008
Message 8 of 10 (285 Views)

Re: Looking for a way, via a sql query, to find the original close time of an incident ticket.

Please use plain text.
Honored Contributor
Piku
Posts: 3,908
Registered: ‎06-17-2010
Message 9 of 10 (281 Views)

Re: Looking for a way, via a sql query, to find the original close time of an incident ticket.

OR
you if you are using activity action for 'close' then just create a variable field on form and query the activity table to display the time stamp of first record return.
Small JS of few rad expressions will do the work.


hth,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Assign Kudo, if found post useful and mark it accepted if solves the issue.
http://h30499.www3.hp.com/t5/Tips-and-Tricks/Kudos-what-where-how-and-why/m-p/5677925#U5677925
Please use plain text.
Trusted Contributor
lwvirden
Posts: 544
Registered: ‎04-22-2008
Message 10 of 10 (278 Views)

Re: Looking for a way, via a sql query, to find the original close time of an incident ticket.

thank you. this thread has provided several ideas for ways to do this. Right now, the change has not been formally requested. I have been doing research because I expect the change to come eventually, and you have given me ideas on how to do this!
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation