Splunk Search

How find the first event of type A after the last event of type B?

gtamaki
Engager

I'm trying to extract a report for devices in my network. Home assistant sends a log record with a value of 1 when a device is present and 0 when it's not, but sometimes it loses the record of the devices. On the sample data below, I need to find the first (value=0) occurrence after the last (value=1) for each "Friendly Name" per day. Can someone help me with this, please ?

TimeStampFriendly Namevalue
9/3/22 12:48User B0
9/3/22 11:58User B0
9/3/22 10:32User B1
9/3/22 10:27User B0
9/3/22 7:44User B1
9/3/22 7:22User B1
9/3/22 0:15User B0

 

In this case I need the second record.

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

There might be an easier way to do this, but try something like this:

| eval day=relative_time(_time,"@d")
| fieldformat day=strftime(day,"%F")
| sort _time
| streamstats current=f global=f latest(value) as previous by day Friendly_Name
| eval previous=if(previous=1,previous,null())
| sort - _time
| streamstats sum(previous) as previous by day Friendly_Name
| eval flag=if(value=0 AND previous=1,"true",null())

View solution in original post

0 Karma

gtamaki
Engager

Worked like a charm. Tks!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

There might be an easier way to do this, but try something like this:

| eval day=relative_time(_time,"@d")
| fieldformat day=strftime(day,"%F")
| sort _time
| streamstats current=f global=f latest(value) as previous by day Friendly_Name
| eval previous=if(previous=1,previous,null())
| sort - _time
| streamstats sum(previous) as previous by day Friendly_Name
| eval flag=if(value=0 AND previous=1,"true",null())
0 Karma
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 ...