I have data that looks like this.
2018-12-13 18:48:05.411 +0000 Tag="Door_Locked" Value="1"
2018-12-13 19:42:41.885 +0000 Tag="AirTC02" Value="1110"
2018-12-13 19:42:41.010 +0000 Tag="VacXdcr2" Value="1663"
2018-12-13 20:48:05.411 +0000 Tag="Door_Locked" Value="0"
2018-12-14 19:42:41.885 +0000 Tag="AirTC02" Value="1220"
2018-12-14 19:42:41.010 +0000 Tag="VacXdcr2" Value="1964"
2018-12-14 20:48:05.411 +0000 Tag="Door_Locked" Value="1"
2018-12-15 19:42:41.885 +0000 Tag="AirTC02" Value="1220"
2018-12-15 19:42:41.010 +0000 Tag="VacXdcr2" Value="1963"
2018-12-15 20:48:05.411 +0000 Tag="Door_Locked" Value="0"
What I want. I would like to have a table with Time for the event. And have the unique tag and value and then a value for whether or not the most recent value for Door_Locked was a 1 or a 0. Stretch goal.
I'd like to identify anytime the Door_Locked Value is a 1 with a counting ‘Session_Num”. Session_Num if possible should increment and be available only if the door_locked is equal to 1. I know I can always throw these away with another search for any non 1 (true) door_)locked event.
Make sense?
I do already have the tagged events parsed out so I can do something in my searches already for Door_Locked=1 Door_Locked=0. What is the best way to accomplish this. I have tried with streamstats but am not getting the Door_Locked state to stick as a field for every event.
Is temporal lookups another option? Thank you for your help.
A Table something like
Hello @rvoninski_splunk,
I think I have figured out how to nullify the session number for events where Door_Locked equals 0. It is actually quite simple. I just added the eval line:
your base search
| streamstats first(Value) as Door_Locked reset_before="("Tag==\"Door_Locked\"")"
| streamstats count(eval(Tag=="Door_Locked" AND Value=="1")) AS Session_Num
| eval Session_Num=if(Door_Locked==0,null(),Session_Num)
Here is a screenshot:
This helped me alot. And is getting me so darn close. Let me explain and show you where i am and see if you can help me with the final mile. OK. (The events are supposed to be in order i fabricated them since i actually have only 8 door locked events over millions of events). Heres where we are now.
I have a field called Door_Locked. It is a boolean 1 for locked and 0 for open. As i said that event only pops up every rare occcasion. I really need to have a field on every event called (for lack of better verbage) Door_Locked_Value and have it take the most recent state that has occurred for Door_Locked. See for the autoclave i only really care about the times that it is locked to investigate whats going on.
So the first part of your streamstats isnt doing much for me. Since it just finds one state change and then sets itself to zero. BUT the second part of your streamstats (even by itself) always picks up when a new Door_Locked event occurs and of course gives it a new session number. So check my visualization below. Green line is the autoclave session and blue line is a temperature. As i said the starts are perfect on the green lines. BUT i need to have it stop when the door is open. And right now its just staying green til the next 'door_locked=1' event occurs. If I had a value for actual state like Door_Locked_Value i could change the session variable for events with an If or Case Eval to a null state or -1 or whatever when the Door_Locked_Value = 0. Make sense?
Heres the current streamstats im using. Any help is appreciated.
index="testIOTdata"
| streamstats first(Door_Locked) as Door_Locked_Value reset_before="(Door_Locked==0)"
| streamstats count(eval(Door_Locked==1)) AS Session_Num
| timechart avg(AirTC01) values(Session_Num) span=15min
