topic Re: How to write a search to display the end date of field values and the time difference? in Splunk Search

How to write a search to display the end date of field values and the time difference?

changux — Sat, 12 Nov 2016 03:37:55 GMT

Hi all.

I have a sourcetype with PENDING orders in a field: ORDERID. In other sourcetype i have ANSWERED orders with the same ORDERID. I need to show in a table the ORDERS PENDING that were ANSWERED with the respective date (the same index time in both cases).
If i have the ORDERID in the ANSWERED sourcetype should be considered as ANSWERED (no special field with statuses).

I can do a join to know common ORDERIDs, but i don't know how display the date when the ORDERID is on ANSWERED sourcetype.

sourcetype=pending ORDERID=* returns orders pending.
sourcetype=answered ORDERID=* return answered.

I can do:

sourcetype=pending | join ORDERID [search sourcetype=answered]

I need to show something like:

ORDERID   |  Date published     |  Date closed         |  Total time to fix 
4424424   | 10/1/16 12:00:00    |  10/2/16 12:00:00    | 24:00:00

Any suggestion?

Re: How to write a search to display the end date of field values and the time difference?

gokadroid — Sat, 12 Nov 2016 04:41:37 GMT

Even though transaction is considered one to be avoided but this scenario seems to have all the makings to use one. Since assumption is that:

"Date published" will always be earlier than "Date Closed"
"Date published" is the first event and "Date Closed" is the last event in the set of events for a particular ORDERID.
None of the events will be closed before they open.
No ORDERID will be published or closed twice.

Hence based on above please try below query with transaction ORDERID which will provide _time which is the first event's time and can be used as publish time, duration to compute the closed time and the eventcount to check we capture events which have both ORDERID being open and closed:

index=* sourcetype=pending OR sourcetype=answered ORDERID=*
| transaction ORDERID
| eval datePublished=_time
| eval dateClosed=_time+duration
| eval totalFixTime=duration
| eval datePublished=strftime(datePublished,"%Y/%m/%d %H:%M:%S")  
| eval dateClosed=strftime(dateClosed,"%Y/%m/%d %H:%M:%S")
| eval totalFixTime=totalFixTime/(60*60*24)
| table ORDERID, datePublished, dateClosed, totalFixTime, eventcount | where eventcount>=2 | fields -eventcount

Updated to human readable time as per comments

Re: How to write a search to display the end date of field values and the time difference?

niketn — Sat, 12 Nov 2016 11:06:12 GMT

Like gokadroid has mentioned transaction will be an expensive command to run for the following use case where the events may be from longer duration (several days). Transaction will run slow and might even drop orphaned records. Refer to following documentation on when to choose which event correlation techniques. Transaction would have been suitable only if you were looking for specific ORDERID across various sourcetype and events.

http://docs.splunk.com/Documentation/Splunk/latest/Search/Abouteventcorrelation

Kindly try the following stats command instead which should hopefully be faster.

index=<YOUR INDEX NAME HERE> (sourcetype="pending" OR sourcetype="answered") orderid=*
 | stats values(sourcetype) as Events count as EventCount min(_time) as DateTimePublished max(_time) as DateTimeAnswered by orderid
 | eval closureDurationInDays= round((DateTimeAnswered-DateTimePublished)/(60*60*24),2)
 | eval DateTimePublished=strftime(DateTimePublished,"%Y/%m/%d %H:%M:%S")
 | eval DateTimeAnswered=strftime(DateTimeAnswered,"%Y/%m/%d %H:%M:%S")
 | search  EventCount>=2 AND Events="pending" AND Events="answered"
 | table orderid, Events, EventCount, DateTimePublished, DateTimeAnswered, closureDurationInDays

Some of the query optimization facts:
1) Define sourcetypes and index in first filter.
2) Add orderid="*" in the first filter to remove any unwanted events upfront.
3) Since you do not have separate status field perform values(sourcetype) stats aggregation to get distinct values of sourcetypes as Events.
4) Use search EventCount>=2 instead of where clause. Also search for both Event="published" and Event="answered". This is just for special scenario like what if there were two queries published for same order id and no answers etc. (Might not be in present in your use case or might be even more complicated).
5) You can play around with Event and EventCount fields to capture other scenarios like Ticket pending but not answered etc. Compare DateTimePublished with now() to get whether they have remained opened for longer than expected SLA.

Let me know if you need any other info regarding the same.

Re: How to write a search to display the end date of field values and the time difference?

changux — Sat, 12 Nov 2016 16:56:23 GMT

Great, thanks. Dates and fixtime are in EPOC or seconds, how i can convert to human?

Re: How to write a search to display the end date of field values and the time difference?

changux — Sat, 12 Nov 2016 17:02:18 GMT

Thank you! I have an syntax error near to line 2:

Error in 'stats' command: The argument 'Events' is invalid.

Any idea?

Re: How to write a search to display the end date of field values and the time difference?

changux — Sat, 12 Nov 2016 17:13:04 GMT

Hi. Fixed with as Events.
Now, my problem is that

... | eval DateTimePublished=strftime(publishedTime,"%Y/%m/%d %H:%M:%S")   | eval DateTimeAnswered=strftime(closedTime,"%Y/%m/%d %H:%M:%S")

Doesn't format the dates. Other suggestion?

Re: How to write a search to display the end date of field values and the time difference?

changux — Sat, 12 Nov 2016 17:21:28 GMT

Again, fixed:

| eval DateTimePublished=strftime(DateTimePublished,"%Y/%m/%d %H:%M:%S")   | eval DateTimeAnswered=strftime(DateTimeAnswered,"%Y/%m/%d %H:%M:%S")

About your point #5, how i can show Tickets pending (no answered)?

Thanks again!

Re: How to write a search to display the end date of field values and the time difference?

changux — Sat, 12 Nov 2016 17:35:55 GMT

Solved! Thanks!

... | eval datePublished=_time  | eval datePublished=strftime(datePublished,"%Y/%m/%d %H:%M:%S")  | eval dateClosed=_time+duration | eval dateClosed=strftime(dateClosed,"%Y/%m/%d %H:%M:%S")   | eval totalFixTime=duration | eval totalFixTime=totalFixTime/(60*60*24) | table ORDERID, datePublished, dateClosed, totalFixTime, eventcount | where eventcount>=2 | fields - eventcount

Re: How to write a search to display the end date of field values and the time difference?

niketn — Sat, 12 Nov 2016 18:48:46 GMT

@changux... Sorry had made some last minute changes to post trying to reduce number or fields and make more sense out of field names, so there were some corrections I missed out. I have corrected my post, also changed EventCount >=2, in case there are multiple published and multiple answered events for the same orderid.

For Pending tickets you need only those tickets which are Published but not answered, so change the search as following (condition >=1 in place just in case there is possibility of more than one pending Events for same orderid):

| search  EventCount>=1 AND Events="pending" AND Events!="answered"