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!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

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 ...