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, an upvote 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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...