Reporting

Remove erroneous results from a search

dbcase
Motivator

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.

alt text

Tags (1)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

dbcase
Motivator

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
0 Karma

somesoni2
Revered Legend

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
0 Karma

hortonew
Builder

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.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...