topic Re: How can I do a subsearch in group by? in Splunk Search

How can I do a subsearch in group by?

sureleo — Mon, 03 Aug 2015 16:02:03 GMT

Hey folks,

I am really new to Splunk and this has bothered me for several days. I have following data by a query:
DateTime UserName ID Route Action
07/30/2015 09:56:41 AMSEyerushalmi 15142095186 CallIntake New
07/30/2015 09:33:59 AMSHjansen 30780945-d17b-4785-a1b1-11426cfedfa5 Agent EndCall
07/30/2015 09:33:59 AMSHjansen 15142087154 Autodoc Update_ICase
07/30/2015 09:34:00 AMSHjansen 30780945-d17b-4785-a1b1-11426cfedfa5 Application StartCall
07/30/2015 09:35:58 AMSHjansen 30780945-d17b-4785-a1b1-11426cfedfa5 CallIntake New
07/30/2015 09:35:58 AMSHjansen 15142091213 Application StartCall
07/30/2015 09:35:59 AMSHjansen 15142091213 ProductSearch SearchLodging
07/30/2015 09:35:59 AMSHjansen 15142091213 ProductSearch SearchLodging
07/30/2015 09:35:59 AMSHjansen 15142091213 ProductSearch SearchLodging

and my search is:

index=app sourcetype="***"
| convert ctime(_time) as DateTime
| table DateTime  UserName ID Route Action
| sort UserName by DateTime

I want to find all the Application/StartCall routes and in the same second or previous second there is a CallIntake/New with the same UserName.

So for this one, it should return me 07/30/2015 09:35:58 AMSHjansen 15142091213 Application StartCall. Because in the same second, there's a CallIntake/New and also it is "AMSHjansen".

Should I do this by a subsearch? This has bothered me for several days.

Any help will be appreciated.

Re: How can I do a subsearch in group by?

lguinn2 — Mon, 03 Aug 2015 16:18:34 GMT

Try this

index=app sourcetype="***" Application StartCall [ search index=app sourcetype="***"  CallIntake New
                   | eval earliest=_time| eval latest=_time+2 
                   | fields UserName earliest latest 
                   | FORMAT "(" "(" "" ")" "OR" ")" ]

First, the subsearch finds all "Callintake New" actions. Then the outer search uses the timeranges and UserNames to choose the "Application StartCall" events.

Re: How can I do a subsearch in group by?

woodcock — Mon, 03 Aug 2015 16:30:51 GMT

This should do it:

index=app sourcetype="***" | eval timePlus1 = _time + 1 | eval myTimes=if(route="CallIntake" AND action="New", _time . ":" . timePlus1,_time) | makemv delim=":" myTimes | stats values(*) AS * by UserName myTimes | where action="StartCall" AND route="CallIntake"

Re: How can I do a subsearch in group by?

woodcock — Mon, 03 Aug 2015 16:43:52 GMT

I had a typo, try again.