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
transaction is considered one to be avoided but this scenario seems to have all the makings to use one. Since assumption is that:
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
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
Great, thanks. Dates and fixtime are in EPOC or seconds, how i can convert to human?
... | 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
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.
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.
Thank you! I have an syntax error near to line 2:
Error in 'stats' command: The argument 'Events' is invalid.
Hi. Fixed with
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?
| 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)?
@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"