Hi,
I have this data
2017-09-27 15:56:42 ID="108065999", PREMISE_FK="1004152", EVENT_TYPE="Camera Trouble", EVENT_SUB_TYPE="com"
2017-09-27 15:56:45 ID="108065999", PREMISE_FK="1004152", EVENT_TYPE="Camera Trouble", EVENT_SUB_TYPE="comRes"
2017-09-27 15:56:42 ID="108065671", PREMISE_FK="1001566", EVENT_TYPE="Camera Trouble", EVENT_SUB_TYPE="com"
Goes on for 4500 events with various different event types. Camera trouble is just one.
Event #1 is a communication failure for a given premise ID
Event #2 is a communication restore for the same premise ID in event #1
Event #3 is a communication failure for a different premise ID
If there is a failure and a subsequent restore I want to ignore it, but if there is a failure without a subsequent restore (Event #3) I want to report on it? How would I go about getting a premise ID and searching the same event log for each premise ID??
I need to end up with a graph like the below. As you can see by the graph there is one device that is flapping that is skewing the results.
@dbcase - We're going to pull the solution from the other question, and then show you how to engineer it into this question.
What we need to do is to take all the different Trouble events, pipe them through an "appendpipe" that uses the code we developed over there to collapse them, then keep all the new ones for this EVENT_TYPE and delete all the old ones.
Looking a little deeper, we note that we need to add back the EVENT_TYPE and EVENT_SUB_TYPE because we'll need them for your graph.
your search that gets the records EVENT_TYPE=*Trouble*
| fields _time ID PREMISE_FK EVENT_TYPE EVENT_SUB_TYPE
| appendpipe
[| where EVENT_TYPE="Camera Trouble"
| stats max(_time) as Time
max(eval(case(EVENT_SUB_TYPE="com",_time))) as comTime
max(eval(case(EVENT_SUB_TYPE="comRes",_time))) as resTime
by ID PREMISE_FK EVENT_TYPE
| where isnull(resTime) OR resTime<comTime
| rename Time as _time
| eval EVENT_SUB_TYPE="com"
| eval keepme="keepme"
| fields _time ID PREMISE_FK EVENT_TYPE EVENT_SUB_TYPE
]
| where (EVENT_TYPE!="Camera Trouble") OR (keepme="keepme")
| fields - keepme
We'll assume the rest of your search looks something like this...
| eval TROUBLE = EVENT_TYPE." - ".EVENT_SUB_TYPE
| top 10 TROUBLE
...or...
| eval TROUBLE = EVENT_TYPE." - ".EVENT_SUB_TYPE
| stats count by TROUBLE
| sort 10 - count
The above assumes there is no reason to show comRes events in the output.
@dbcase - We're going to pull the solution from the other question, and then show you how to engineer it into this question.
What we need to do is to take all the different Trouble events, pipe them through an "appendpipe" that uses the code we developed over there to collapse them, then keep all the new ones for this EVENT_TYPE and delete all the old ones.
Looking a little deeper, we note that we need to add back the EVENT_TYPE and EVENT_SUB_TYPE because we'll need them for your graph.
your search that gets the records EVENT_TYPE=*Trouble*
| fields _time ID PREMISE_FK EVENT_TYPE EVENT_SUB_TYPE
| appendpipe
[| where EVENT_TYPE="Camera Trouble"
| stats max(_time) as Time
max(eval(case(EVENT_SUB_TYPE="com",_time))) as comTime
max(eval(case(EVENT_SUB_TYPE="comRes",_time))) as resTime
by ID PREMISE_FK EVENT_TYPE
| where isnull(resTime) OR resTime<comTime
| rename Time as _time
| eval EVENT_SUB_TYPE="com"
| eval keepme="keepme"
| fields _time ID PREMISE_FK EVENT_TYPE EVENT_SUB_TYPE
]
| where (EVENT_TYPE!="Camera Trouble") OR (keepme="keepme")
| fields - keepme
We'll assume the rest of your search looks something like this...
| eval TROUBLE = EVENT_TYPE." - ".EVENT_SUB_TYPE
| top 10 TROUBLE
...or...
| eval TROUBLE = EVENT_TYPE." - ".EVENT_SUB_TYPE
| stats count by TROUBLE
| sort 10 - count
The above assumes there is no reason to show comRes events in the output.
Hi DalJeanis,
I ended up using yours but then adding an additional stats command
index=cg_troubles|rex "(?i) PREMISE_FK=\"(?P<premise>[^\"]+)"|rex "(?i) EVENT_TYPE=\"(?P<event_type>[^\"]+)"|rex "(?i) .*?=\"(?P<EVENT_SUB_TYPE>[a-z]+)(?=\")"|stats max(_time) as Time
max(eval(case(EVENT_SUB_TYPE="com",_time))) as comTime
max(eval(case(EVENT_SUB_TYPE="comRes",_time))) as resTime
values(event_type) as et values(EVENT_SUB_TYPE) as est by premise
| where isnull(resTime) OR resTime<comTime
| rename Time as _time|eval ts=et+" - "+est|stats count by ts|sort by -count|head 10
For each Camera trouble incident, does a new Promise ID (PREMISE_FK) is created (means every failure is unique)?? If yes, try this
your base search | stats list(EVENT_SUB_TYPE) as EVENT_SUB_TYPE by PREMISE_FK, EVENT_TYPE
| where mvcount(EVENT_SUB_TYPE)=1
You can do so by utilizing the transaction command.
If you want something less processing intensive you can do so by evaluating a new field, maybe "status", and then use stats. For instance:
A very simple example might look like
eval status=if(EVENT_TYPE=="Trouble" AND EVENT_SUB_TYPE=="comRes", "Good", "Bad") | stats latest(status) as LatestStatus | where LatestStatus=="Bad"
This would indicate all that are still bad and can be alerted on. Should get you started - will definitely need tweaking.