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.
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"
I had a typo, try again.
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.