Calculate time between events - query taking a long time

[giguere1]

Here is my query:

index=something st=something (EventID=9999 OR EventID=9998 OR EventID=9997 OR EventID=9996) | transaction maxspan=10s maxpause=2 host startswith=eval(EventID=9997 OR EventID=9996) endswith=eval(EventID=9999 OR EventID=9998)

The first 2 events are the start of the event and the last 2 events are the end of the event. Any help would be appreciated. Thanks.

[giguere1]

Both streamstats and transcation are taking forever. I talked to our enterprise admin and ran job inspector on the search. Everything is failing because of a misconfig in dispatch.conf. I guess we have a ticket in with splunk right now, but it looks like that will need to get cleaned up before any of this gets better. Thanks for all your help. I am still open to more ideas.
-pg

[martin_mueller]

All glory to the job inspector.

[woodcock]

As far as multiple transactions/hosts, so long as transaction events with the same host do not intermingle, this can also be handled without transaction like this:

index=something st=something (EventID=9999 OR EventID=9998 OR EventID=9997 OR EventID=9996) | eval startswith=if((EventID=9997 OR EventID=9996),_time,null()) | streamstats current=f last(startswith) AS startswithByHost by host | eval duration=startswithByHost-_time

Once this is run, every event will contain 2 new fieds: startswithByHost is the _time value of the nearest previous 9997 or 9996 that shares the same host value as this event and duration is the difference between this event's _time value and startswithByHost. Now the user can tack on any additional pipes/commands that he needs to finish his summary, all without the heavy overhead (which was the OP's original complaint, that of the "long time") of the transaction command.

[martin_mueller]

eventstats would assign one value for all events, giving you incorrect durations. This may be made to work with streamstats though.

[woodcock]

Correct, I meant streamstats; good catch!

 index=something st=something (EventID=9999 OR EventID=9998 OR EventID=9997 OR EventID=9996) | eval startswith=if((EventID=9997 OR EventID=9996),_time,null()) | streamstats last(startswith) AS startswithByHost by host | eval duration=startswithByHost-_time

[giguere1]

AWESOME! I'm trying this now. Sorry guys, I was on vacation most of last week.
-pg

[woodcock]

The transaction does many things that you don't appear to need; try this instead (simpler and quicker):

index=something st=something (EventID=9999 OR EventID=9998 OR EventID=9997 OR EventID=9996) | stats earliest(_time) AS firstTime latest(_time) AS lastTime by host | eval duration=lastTime-firstTime

[martin_mueller]

This is going to break terribly as soon as one host has more than one transaction in the searched timespan.

[martin_mueller]

To see what's taking up the time you could post the job inspector output.

[martin_mueller]

Help with what exactly?

[giguere1]

I want to show the amount of time on a barchart per day that is transacting for all hosts in one number. Right now this query, they way i have it, is taking far too long to gen. I am looking for more efficient ways to do this.