Reporting

Compare Earliest Duration of different field value

limalbert
Path Finder

How can I compare if worldTime happened before helloTime by combining the below 2 searches?

 

 

 

index=search Type=Hello 
| stats first(Time) as helloTime by Account WorkOrder
index=search Type=World 
| stats first(Time) as worldTime by Account WorkOrder

 

 

 

 

 

Expected Column Result:
Account WorkOrder list(Type) list(Time) worldBeforeHelloFlag

 

 

 

Labels (1)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Search is case-insensitive whereas straight comparisons are not. Try:

index=search (Type=Hello OR Type=World) 
| stats first(Time) as typeTime by Type Account WorkOrder
| eval helloTime=if(match(Type,"(?i)Hello"),typeTime,null)
| eval worldTime=if(match(Type,"(?i)World"),typeTime,null)
| stats values(helloTime) as helloTime values(worldTime) as worldTime by Account WorkOrder
| where worldTime < helloTime

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Try this

 

index=search (Type=Hello OR Type=World)
| eval helloTime=if(Type=Hello, Time, null), worldTime=if(Type=World, Time, null)
| stats list(Type), list(Time), first(helloTime) as helloTime, first(worldTime) as worldTime by Account WorkOrder 
| eval ehelloTime=strptime(helloTime, "<insert time format here"),
eval eworldTime=strptime(worldTime, "<insert time format here")
| eval worldBeforeHelloFlag = if(eworldTime < ehelloTime, 1, 0)
| table Account WorkOrder "list(Type)", "list(Time)" worldBeforeHelloFlag 

 

---
If this reply helps you, Karma would be appreciated.
0 Karma

limalbert
Path Finder

Unfortunately, this is only returning the helloTime

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Does this help

index=search (Type=Hello OR Type=World) 
| stats first(Time) as typeTime by Type Account WorkOrder
| eval helloTime=if(Type="Hello",typeTime,null)
| eval worldTime=if(Type="World",typeTime,null)
| stats values(helloTime) as helloTime values(worldTime) as worldTime by Account WorkOrder
| where worldTime < helloTime
0 Karma

limalbert
Path Finder

Unfortunately, no.

Error in 'stats' command: Repeated group-by field 'EventType'.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

How have you used "EventType" in your search?

0 Karma

limalbert
Path Finder

You're right. I actually saw there was a duplicate typo.

The next issue is that it's only returning the eval for helloTime, so it can't compare against worldTime. The first(Time) only returns a single time. Instead of 2 first(Time) for type=hello and type=world.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Search is case-insensitive whereas straight comparisons are not. Try:

index=search (Type=Hello OR Type=World) 
| stats first(Time) as typeTime by Type Account WorkOrder
| eval helloTime=if(match(Type,"(?i)Hello"),typeTime,null)
| eval worldTime=if(match(Type,"(?i)World"),typeTime,null)
| stats values(helloTime) as helloTime values(worldTime) as worldTime by Account WorkOrder
| where worldTime < helloTime
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...