Splunk Search

How to aggregate sequences of consecutive events into transactions?

tomasmoser
Contributor

Hi Team,

I need to aggregate sequences of all consecutive events with a field Door=''Open" delimited with sequence of events with a field Door="Closed" into multiple transactions.

I am just starting with Splunk, still do not have much practical experience.

Source:

event 1: Door=Closed Temperature=1.0
event 2: Door=Closed Temperature=1.5

event 3: Door=Open Temperature=1.5
event 4: Door=Open Temperature=1.6

event 5: Door=Closed Temperature=1.6
event 6: Door=Closed Temperature=1.6

event 7: Door=Open Temperature=1.8

event 8: Door=Closed Temperature=1.8
...

Goal:

event 1: Door=Closed Temperature=1.0
event 2: Door=Closed Temperature=1.5

Transaction 1:
event 3: Door=Open Temperature=1.5
event 4: Door=Open Temperature=1.6

event 5: Door=Closed Temperature=1.6
event 6: Door=Closed Temperature=1.6

Transaction 2:
event 7: Door=Open Temperature=1.8

event 8: Door=Closed Temperature=1.8
etc.

How can I achieve that? I tried transaction command with startswith and endswith but it fails. Does not work as expected.

For all transactions I plan to get: duration and difference between temperatures at the beginning and the end of each transaction.

At least ideas which directions to get would be appreciate. Code example would be great.

Tomas

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

your base search
| streamstats current=f window=1 values(Door) as prev
| eval transfield=if(Door="Open" AND prev="Closed",1,0)
| accum transfield
| stats min(_time) as StartTime max(_time) as EndTime first(Temperature) as StartTemperature last(Temperature) as EndTemperatur by transfield

View solution in original post

0 Karma

niketn
Legend

For transaction command to work, you would need to add transaction IDs to your events, this way there will be one time effort for logging however, query performance will be better (even stats can be used instead of transaction based on transaction ID).

Seems like your current logs are being generated at periodic intervals and logs the Door status at that time. It does not seem to be based only on actual Door Open or Closed events. Because of which, there are multiple Open and Closed events together.

Give following query a try, query is resetting Count using streamstats after every event where Door is Closed. SPL **accum command is used to generate cumulative Count. Based on the types of transaction scenarios provided, following are some of the cases to change the cumulative count to generate transaction IDs.

1) If the Door is Open and the Count = 1, then it is first event of the transaction. Increment the Transaction ID to be used for current transaction.
2) If the Door is Open and the Count >1, then the door is still open and has not been closed, then keep the same Transaction ID as the first Open status.
3) If the Door is Closed and Count > 1, then keep transaction ID same as first Open status.
4) If the Door is Closed and Count =1, then use the incremented Transaction ID as the is only event in the Transaction.
5) Default use the incremented Transaction ID.

PS: You can change the case conditions here based on other scenarios/changes as per your requirements.

<Your Base Search>
| sort _time
| streamstats count as Count reset_after="("Door==\"Closed\"")"
| eval transID= 1
| accum transID
| eval transID=case(Door=="Open" AND Count==1,transID,Door=="Closed" AND Count>1,transID-(Count-1),Door=="Open" AND Count>1,transID-(Count-1),Door=="Closed" AND Count=1,transID,1=1,transID)
| table Count _time Door Temperature transID
| stats count as EventCount first(_time) as StartTime first(Temperature) as StartTemp last(_time) as EndTime last(Temperature) as EndTemp values(Door) as DoorStatus by transID 
| eval duration=tostring(EndTime-StartTime,"duration")
| eval tempChange=StartTemp-EndTemp
| eval StartTime=strftime(StartTime,"%c")
| eval EndTime=strftime(EndTime,"%c")
| search EventCount>1 AND DoorStatus="Closed" AND DoorStatus="Open"
| table transID EventCount DoorStatus duration tempChange

PS: Final search with EventCount and DoorStatus can be used to identify various scenarios like Door Opened but Not Closed etc.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

your base search
| streamstats current=f window=1 values(Door) as prev
| eval transfield=if(Door="Open" AND prev="Closed",1,0)
| accum transfield
| stats min(_time) as StartTime max(_time) as EndTime first(Temperature) as StartTemperature last(Temperature) as EndTemperatur by transfield
0 Karma

tomasmoser
Contributor

Hi Somesoni2,

I got your idea. Slight adjustment of your coded did what I wanted. I had to filter all events with Door=Closed withing each transaction.

Thanks!

This code works as expected:

index=test sourcetype=arduino
| streamstats current=f window=1 values(Door) as prev
| eval transID=if(Door="Opened" AND prev="Closed",1,0)
| accum transID
| search Door="Opened"
| reverse
| stats min(_time) as StartTime, max(_time) as EndTime, first(Temperature) as StartTemperature, last(Temperature) as EndTemperature by transID
| eval Duration=EndTime-StartTime, TempDiff=EndTemperature-StartTemperature
| eval DurationHuman=tostring(Duration, "duration")
| eval EndTimeHuman=tostring(EndTime, "duration")
| fields Duration, TempDiff | where TempDiff>=0
| dedup 1 Duration TempDiff
| sort Duration
| rename Duration as "Duration (s)", TempDiff as "Temperature Increase (°C)"

Tomas

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 ...