topic Re: Calculate time between events - query taking a long time in Splunk Search

Calculate time between events - query taking a long time

giguere1 — Tue, 12 May 2015 21:37:41 GMT

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.

Re: Calculate time between events - query taking a long time

martin_mueller — Tue, 12 May 2015 22:06:53 GMT

Help with what exactly?

Re: Calculate time between events - query taking a long time

giguere1 — Tue, 12 May 2015 22:08:38 GMT

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.

Re: Calculate time between events - query taking a long time

martin_mueller — Tue, 12 May 2015 22:21:23 GMT

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

Re: Calculate time between events - query taking a long time

woodcock — Thu, 14 May 2015 00:08:37 GMT

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

Re: Calculate time between events - query taking a long time

martin_mueller — Thu, 14 May 2015 09:15:16 GMT

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

Re: Calculate time between events - query taking a long time

woodcock — Thu, 14 May 2015 13:53:59 GMT

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.

Re: Calculate time between events - query taking a long time

martin_mueller — Thu, 14 May 2015 14:35:04 GMT

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

Re: Calculate time between events - query taking a long time

woodcock — Thu, 14 May 2015 14:57:50 GMT

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

Re: Calculate time between events - query taking a long time

giguere1 — Mon, 18 May 2015 15:40:13 GMT

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

Re: Calculate time between events - query taking a long time

giguere1 — Tue, 19 May 2015 16:55:42 GMT

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

Re: Calculate time between events - query taking a long time

martin_mueller — Tue, 19 May 2015 17:04:05 GMT

All glory to the job inspector.