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
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??
@dbcase - It is almost always a bad idea to think in terms of recursion in splunk.
In these cases, you usually need to think in terms of sequence. Select all the records you need, sort them in forward or backwards time order, and use streamstats
to copy the information you need forward or backwards onto the records. Alternately, you can often use eventstats
to copy group information among a group of records without losing the individual records.
In this case, however, due to the simple record format, clear key structure, and basic requirements, a plainstats
will get you what you need.
your search that gets the records 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
| where isnull(resTime) OR resTime<comTime
| rename Time as _time
Hi DalJeanis,
I think your answer is a step in the right direction. I didn't give you all the details though (sorry thats my fault)
The final version of this needs to produce a chart that looks like the attached. What is happening is there are some devices that are "flapping" (i.e. com(fail), then comRes over and over throwing off the chart) that need to be eliminated, which is what your query does (thank you!) but as a result I'm missing the remaining data get the graph built properly.
@dbcase - okay, I'll answer this new part on the other question. Please accept the answer to close this question.