Splunk Search

How to check and count the status before a certain event?

jyab6z
Path Finder

Our log looks like as following after basic search:

Date..............Time...........................UserID..................Function/Status
20190227 03:56:22:788 [njdh00t2ldqwuocvtdzdywcr] - Function
20190227 03:55:09:933 [njdh00t2ldqwuocvtdzdywcr] - Status 1.
20190227 03:46:35:503 [njdh00t2ldqwuocvtdzdywcr] - Function
20190227 03:46:32:587 [njdh00t2ldqwuocvtdzdywcr] - Function
20190227 03:45:14:681 [njdh00t2ldqwuocvtdzdywcr] - Function
20190227 03:44:56:292 [njdh00t2ldqwuocvtdzdywcr] - Status 2.
20190227 03:33:15:450 [njdh00t2ldqwuocvtdzdywcr] - Status 1.

I want to count percentage of Status 1 and Status 2 right before Function event for same Date and same UserID.
In this case, the result should be as follow:

Status...........................count
Status1 ........................1
Status2 ........................1

Due to first Status 1 event was not counted. The search need to check the Status before Function event, only the Status followed by a Function event should be counted.

Any idea? Thanks in advance!

0 Karma
1 Solution

adonio
Ultra Champion

hello there,

hope i understood your question.
try and run the below search anywhere, i added extra lines with another Status, Status 3:

| makeresults count=1 
| eval data = "20190227 03:56:22:788 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 03:55:09:933 [njdh00t2ldqwuocvtdzdywcr] - Status 1;;;20190227 03:46:35:503 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 03:46:32:587 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 03:45:14:681 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 03:44:56:292 [njdh00t2ldqwuocvtdzdywcr] - Status 2;;;20190227 03:33:15:450 [njdh00t2ldqwuocvtdzdywcr] - Status 1;;;20190227 03:56:22:788 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 04:55:09:933 [njdh00t2ldqwuocvtdzdywcr] - Status 2;;;20190227 04:46:35:503 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 04:46:32:587 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 04:45:14:681 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 04:44:56:292 [njdh00t2ldqwuocvtdzdywcr] - Status 3;;;20190227 04:33:15:450 [njdh00t2ldqwuocvtdzdywcr] - Status 1"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<YMD>[^\s]+)\s+(?<HMS>[^\s]+)\s+(?<u_id>[^\s]+)\s+\-\s+(?<function>[^\|]+)"
| table YMD HMS u_id function
| rename COMMENT as "the above generates data below is the solution" 
| reverse
| streamstats window=1 current=f last(function) as previous_function
| stats values(function) as original_function values(previous_function) as origial_previous_function by YMD u_id
| mvexpand original_function
| mvexpand origial_previous_function
| eval should_count = if(origial_previous_function LIKE "Status%" AND original_function=="Function",1,0)
| stats sum(should_count) by YMD origial_previous_function
| where like(origial_previous_function,"Status%")

Hope it helps

View solution in original post

0 Karma

jyab6z
Path Finder

Hi again @adonio,

Now I need to calculate total count of Function after Status 1, how to do that?
The result should be as follow:

Status...........................count of Function
Status1 ........................3

0 Karma

adonio
Ultra Champion

can you open another question?
this one is marked accepted to the world so less chances other community members will open it and help

0 Karma

jyab6z
Path Finder

Hi @adonio,

It helps alot! Thank you! One more question, what if the log continue with other user's info,? For instance:

Date..............Time...........................UserID..................Function/Status
20190227 03:56:22:788 [njdh00t2ldqwuocvtdzdywcr] - Function
20190227 03:55:09:933 [njdh00t2ldqwuocvtdzdywcr] - Status 1.
20190227 03:46:35:503 [njdh00t2ldqwuocvtdzdywcr] - Function
20190227 03:46:32:587 [njdh00t2ldqwuocvtdzdywcr] - Function
20190227 03:45:14:681 [njdh00t2ldqwuocvtdzdywcr] - Function
20190227 03:44:56:292 [njdh00t2ldqwuocvtdzdywcr] - Status 2.
20190227 03:33:15:450 [njdh00t2ldqwuocvtdzdywcr] - Status 1.
20190227 03:32:32:587 [new user here] - Function
20190227 03:31:14:681 [new user here] - Function
20190227 03:30:56:292 [new user here] - Status 2.
20190227 03:29:15:450 [new user here] - Status 1.

Can I add an empty line between different users somehow?
Thank you alot!

0 Karma

adonio
Ultra Champion

not sure what do you mean by "add an empty line ..."

the search logic should cover also different users

0 Karma

jyab6z
Path Finder

Yea, never mind, my bad! Thank you!

0 Karma

adonio
Ultra Champion

hello there,

hope i understood your question.
try and run the below search anywhere, i added extra lines with another Status, Status 3:

| makeresults count=1 
| eval data = "20190227 03:56:22:788 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 03:55:09:933 [njdh00t2ldqwuocvtdzdywcr] - Status 1;;;20190227 03:46:35:503 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 03:46:32:587 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 03:45:14:681 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 03:44:56:292 [njdh00t2ldqwuocvtdzdywcr] - Status 2;;;20190227 03:33:15:450 [njdh00t2ldqwuocvtdzdywcr] - Status 1;;;20190227 03:56:22:788 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 04:55:09:933 [njdh00t2ldqwuocvtdzdywcr] - Status 2;;;20190227 04:46:35:503 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 04:46:32:587 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 04:45:14:681 [njdh00t2ldqwuocvtdzdywcr] - Function;;;20190227 04:44:56:292 [njdh00t2ldqwuocvtdzdywcr] - Status 3;;;20190227 04:33:15:450 [njdh00t2ldqwuocvtdzdywcr] - Status 1"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<YMD>[^\s]+)\s+(?<HMS>[^\s]+)\s+(?<u_id>[^\s]+)\s+\-\s+(?<function>[^\|]+)"
| table YMD HMS u_id function
| rename COMMENT as "the above generates data below is the solution" 
| reverse
| streamstats window=1 current=f last(function) as previous_function
| stats values(function) as original_function values(previous_function) as origial_previous_function by YMD u_id
| mvexpand original_function
| mvexpand origial_previous_function
| eval should_count = if(origial_previous_function LIKE "Status%" AND original_function=="Function",1,0)
| stats sum(should_count) by YMD origial_previous_function
| where like(origial_previous_function,"Status%")

Hope it helps

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...