Transaction command returning 'no results found'

[dfwissman]

I'm trying to manipulate some data from our incident management software to calculate the amount of time an incident spends in a particular status (Open, Assigned, Work in Progress, Resolved, Pending, Closed). I've started with a search along these lines to calculate epoch time from the update time and then sort and streamstats to calculate the delta. At the end, I'm trying to lump them back in as a transaction but for some reason it fails

index=Incidents  | eval timey=strptime(UpdateTime,"%Y-%m-%d %H:%M:%S") | reverse | table Incident UpdateTime Status AssignmentGroup timey | sort -Incident |  streamstats window=2 last(timey) as t1,first(timey) as t2 BY Incident reset_on_change=true | eval timediff=round((t1-t2)/60,2) |fields - timey t1 t2 |rename timediff AS "Duration(minutes)"

resulting in something like this:

But when I modify it to this so that I put the records into a transaction by Incident:

index=Incidents   | eval timey=strptime(UpdateTime,"%Y-%m-%d %H:%M:%S") | reverse | table Incident UpdateTime Status AssignmentGroup timey | sort -Incident |  streamstats window=2 last(timey) as t1,first(timey) as t2 BY Incident reset_on_change=true | eval timediff=round((t1-t2)/60,2) |fields - timey t1 t2 |reverse | transaction Incident | table Incident UpdateTime Status AssignmentGroup timediff |rename timediff AS "Duration(minutes)"

I get a no results found

Any ideas on what I might be missing? Is there a restriction to transaction that might be preventing this? Thanks!

[niketn · Accepted solution]

Transaction command should not be run for a very wide range of time (try using stats command instead).

There is also a possibility of events getting evicted/orphaned, if searched for longer duration. So try using keepevicted=true in transaction.

In your case the transaction is for each incident belonging to a particular assignment group for specific status. So add all three fields to transaction command. Also, transaction command calculates duration field by itself. If possible you should also consider using startswith, endswith, maxpause and maxspan criteria. Transaction command is specially suitable if you provide transaction ID in search criteria for example Incident#.

Try the following code (in order to test the search add one incident number in the main search filter query i.e index=Incidents Incident="INC######") :

index=Incidents | table Incident UpdateTime Status AssignmentGroup | sort - Incident AssignmentGroup Status | transaction Incident Status AssignmentGroup keepevicted=true | eval durInMin=(duration/60) | table Incident UpdateTime Status AssignmentGroup durInMin | rename durInMin AS "Duration(minutes)"

For when to use stats vs transaction, refer to the following Splunk documentations. In this particular scenario I would believe stats would work better than transaction.

http://docs.splunk.com/Documentation/Splunk/6.5.0/Search/Abouteventcorrelation
http://docs.splunk.com/Documentation/Splunk/6.5.0/Search/Abouttransactions

[dfwissman]

I bypassed the transaction using stats list ... here's the information below if anyone is interested. It's a bit kludgy as I'm still working on the query:

index=Incidents  | eval timey=strptime(UpdateTime,"%Y-%m-%d %H:%M:%S") | table Incident UpdateTime Status AssignmentGroup timey | sort -Incident +timey | streamstats count as listIndex by Incident reset_on_change=true  |  streamstats window=2 last(timey) as t1,first(timey) as t2 BY Incident reset_on_change=true | eval timediff=round((t1-t2)/60,2) |fields - timey t1 t2 | stats list(Status) list(timediff) list(AssignmentGroup) max(listIndex) by Incident | rename list(Status) AS Status, list(timediff) AS timediff, list(AssignmentGroup) AS AssignmentGroup, max(listIndex) AS listIndex | where Status="Closed" AND Status="Assigned" | eval field1="Opened" | eval state=mvappend(field1,mvindex(Status,0,listIndex-2)) | table Incident Status state timediff AssignmentGroup

This outputs the below:

I shift the Status to align them with the duration in that state (field is state). That's most of that last bit with the mvappend. I could modify the mvappend to retain the 'Closed' state and then append a '0.00' to the timediff at the end to keep all ticket states.

[dfwissman]

Your recommendation of using stats is the best way to approach this, I was just being lazy and noticed I could use transaction at the start of the SPL but if I manipulated the results in the fashion above it wouldn't return a result set. Struck me as odd and I wanted to follow up on it. I'll be playing around with the job inspection to see if anything comes to light.

Again thank you for your response.