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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...