topic Re: Remove erroneous results from a search in Reporting

Remove erroneous results from a search

dbcase — Tue, 29 Sep 2020 16:03:17 GMT

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.

Re: Remove erroneous results from a search

hortonew — Fri, 29 Sep 2017 17:09:58 GMT

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.

Re: Remove erroneous results from a search

somesoni2 — Fri, 29 Sep 2017 17:30:36 GMT

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

Re: Remove erroneous results from a search

DalJeanis — Tue, 29 Sep 2020 15:59:37 GMT

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

Re: Remove erroneous results from a search

dbcase — Fri, 29 Sep 2017 20:41:10 GMT

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