Splunk Search

How can I do a subsearch in group by?

sureleo
New Member

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.

Tags (3)
0 Karma

woodcock
Esteemed Legend

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"
0 Karma

woodcock
Esteemed Legend

I had a typo, try again.

0 Karma

lguinn2
Legend

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.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...