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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...