topic Re: How to check and count the status before a certain event? in Splunk Search

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

jyab6z — Mon, 18 Mar 2019 07:31:05 GMT

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!

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

adonio — Mon, 18 Mar 2019 12:07:17 GMT

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

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

jyab6z — Mon, 18 Mar 2019 14:28:41 GMT

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!

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

adonio — Mon, 18 Mar 2019 17:03:56 GMT

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

the search logic should cover also different users

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

jyab6z — Tue, 19 Mar 2019 08:24:54 GMT

Yea, never mind, my bad! Thank you!

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

jyab6z — Tue, 26 Mar 2019 16:00:02 GMT

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

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

adonio — Tue, 26 Mar 2019 16:27:16 GMT

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