Splunk Search

Transaction command returning 'no results found'

dfwissman
New Member

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:
alt text

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
alt text

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

0 Karma
1 Solution

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

dfwissman
New Member

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:
alt text

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.

0 Karma

dfwissman
New Member

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...