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

[jyab6z]

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!

[jyab6z]

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

[adonio]

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

[jyab6z]

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!

[adonio]

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

the search logic should cover also different users

[jyab6z]

Yea, never mind, my bad! Thank you!

[adonio · Accepted solution]

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