How many time a string occurs in a transaction and not the number of events that string appear

[le_barbucheron]

Hi,

I've got a machine splitted in two unit A and B who gave me their state of preparation and their Failure level. I wanted to count the number of failure per unit when the unit is ready.

So, i created a transaction to group my event when my machine is ready and tried to count the number of time my machine wasn't OK, but every query i tried return me the number of event and not the number of time the string i was looking for occurs.

This is my base search :

 (index="index1") Equipement="Machine1" (New_State=ready OR New_State=Not_ready  OR id ="*Machine1_B_FAILURE_LEVEL" OR id ="*Machine_A_FAILURE_LEVEL") | transaction Equipement host startswith="New_State="Ready" endswith="New_State=Not_ready"

And I tried :

 stats count  by Equipement, New_State limit=100

And :

   |eval Failure_machine1_A=if(searchmatch(".*machine1_A_FAILURE_LEVEL") AND (searchmatch("Not_ok"),1,Failure_machine1_A)  | | transaction Equipement host startswith="New_State=Ready" endswith="New_State=Not_ready"
     | stats sum(Failure_machine1_A) by Equipement

also :

| transaction Equipement host startswith="New_State=Ready" endswith="New_State=Not_ready"
     |eval Failure_machine1_A=if(searchmatch(".*machine1_A_FAILURE_LEVEL") AND (searchmatch("Not_ok"),1,Failure_machine1_A)  |  stats sum(Failure_machine1_A) by Equipement

And :

|eval Failure_machine1_A=if((match(id,".*Machine1_A_FAILURE_LEVEL") AND match(_raw, "NOT_OK" )),1,Failure_machine1_A )| transaction Equipement host startswith="New_State=Ready" endswith="New_State=Not_Ready"|  
stats count(Failure_machine1_A) by Equipement

And the last try :

|eval Failure_machine1_A=if(like(id,"*Machine1_A_FAILURE_LEVEL") AND New_State=Not_ok,1,Failure_machine1_A )| transaction Equipement host startswith="New_State=Ready endswith="New_State=Not_Ready"|  
stats count(Failure_machine1_A) by Equipement

I also tried to use some subsearch without success.

As a beginner, I don't know what I can try next or if I use the right method to achieve my goal.

Thank you in advance for your answers and sorry for my english.

[le_barbucheron · Accepted solution]

Thank you guys with your help I found the answer !

|rex field=_raw "A_FAILURE_LEVEL;([^;])+;([^;])+;([^;])+;([^;])+;([^;])+;([^;])+;(?<state>.*)" max_match=0
 |mvexpand state
  |stats count by Equipement, state

and untable/fields - the state i don't want

Thank you so much for your help @DavidHourani and @Sukisen1981 !!

[DavidHourani]

Awesome please upvote our answers and comments for karma points and accept if possible. Also check my last comment I gave you an improvement for this regex 😉

Cheers,
David

[le_barbucheron]

I tried your regex improvement but i don't know why, this gave me no result,

Thank you so much again 🙂

Cheers

[DavidHourani]

Hi @le_barbucheron,

Try this :

    index="index1" Equipement="Machine1" (New_State="ready" OR New_State="Not_ready" OR id ="*Machine1_B_FAILURE_LEVEL" OR id ="*Machine_A_FAILURE_LEVEL") 
| transaction Equipement host startswith="New_State=Ready" endswith="New_State=Not_ready"
|rex field=payload "FAILURE_LEVEL;XXX;(?<failed>.*);" max_match=0
|mvexpand failed
|stats count by failed,Equipement, New_State 
|where failed="NOT_OK"

Cheers,
David

[le_barbucheron]

Hi @DavidHourani ,

I tried your query but that returns me no results ,

and when i modify the query like this :

|mvexpand New_State
|stats count by Equipement, New_State
 |where New_State="NOT_OK"

Thx for your answer 🙂
Because my failure_level is in the extracted field "New_State", and i'm really bad with REGEX, that return me again the number of events that field occurs and not the number of occurence of the value "NOT_OK"

[DavidHourani]

you will need the regex for the query to work on the NOT_OK..try with this instead :

 |rex field=payload "FAILURE_LEVEL;([^;])+;(?<failed>.*);" max_match=0
 |mvexpand failed
 |stats count by failed,Equipement, New_State 
 |where failed="NOT_OK"

[le_barbucheron]

Hi again @DavidHourani

I tested it and again Splunk return me no results, maybe because their is 6 ";" between "Failure_LEVEL" and the state "NOT_OK" like this

2019-04-11 19:08:48.230;2019-04-11 17:08:48.230;2019-04-11 19:08:48.230;Machine1_A_FAILURE_LEVEL;XXX;XXX;XXX;XXX;XXX;XXX;NOT_OK;\x00;\x00;\x00;0;0

So I tried :

|rex field=_raw "FAILURE_LEVEL;([^;])+;([^;])+;([^;])+;([^;])+;([^;])+;([^;])+;(?<failed>.*);" max_match=0
  |mvexpand failed
  |stats count by failed,Equipement, New_State 
  |where failed="NOT_OK"

But again no result 😕

[DavidHourani]

ohhh yeah that explains... I thought it's just one set of semicolons, your regex should be something like this then :

|rex field=_raw "FAILURE_LEVEL;([^;]+;)+(?<failed>.+OK);" max_match=0

The rest should work fine 🙂

[Sukisen1981]

Try this:

| makeresults 
|  eval payload=" 05/18/2019 17:08:322;SOME STRING;MACHINE1_STATE;READY;
 05/18/2019 18:05:355;SOME STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;OK;
 05/18/2019 18:22:460;SOME STRING;Nameofthemachine_Machine1_B_FAILURE_LEVEL;XXX;OK;
 05/18/2019 19:48:127;SOME STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;NOT_OK;
 05/18/2019 21:05:237;SOME STRING;Nameofthemachine_Machine1_B_FAILURE_LEVEL;XXX;NOT_OK;
 05/18/2019;SOMESTRING;MACHINE1_STATE;NOT_READY;"
|  rex field=payload "FAILURE(?<failed>.*)" max_match=0
| eval failed_count=mvcount(failed)
| table failed_count

here failed count is coming as 4, is that correct? If so, all you need to do is modify the above query to

 <your index>|
    |  rex field=_raw "FAILURE(?<failed>.*)" max_match=0
    | eval failed_count=mvcount(failed)
    | table failed_count

[le_barbucheron]

Thank for your quick aswer, i'll try this tomorrow because i don't have acces to my splunk instance now.

In fact what I desire it's when I got multiple transactions like this :

05/18/2019 17:08:322;SOME STRING;MACHINE1_STATE;READY;
 05/18/2019 18:05:355;SOME STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;OK;
 05/18/2019 18:22:460;SOME STRING;Nameofthemachine_Machine1_B_FAILURE_LEVEL;XXX;OK;
 05/18/2019 19:48:127;SOME STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;NOT_OK;
 05/18/2019 21:05:237;SOME STRING;Nameofthemachine_Machine1_B_FAILURE_LEVEL;XXX;NOT_OK;
 05/18/2019;SOMESTRING;MACHINE1_STATE;NOT_READY;

 05/16/2019 10:07:254;SOME STRING;MACHINE1_STATE;READY;
 05/16/2019 11:12:523;SOME STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;OK;
 05/16/2019 12:27:520;SOME STRING;Nameofthemachine_Machine1_B_FAILURE_LEVEL;XXX;OK;
 05/16/2019 13:58:454;SOME STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;NOT_OK;
 05/16/2019 20:23:258;SOME STRING;Nameofthemachine_Machine1_B_FAILURE_LEVEL;XXX;NOT_OK;
 05/17/2019 16:07:145;SOME STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;OK;
 05/17/2019 16:22:220;SOME STRING;Nameofthemachine_Machine1_B_FAILURE_LEVEL;XXX;OK;
 05/17/2019 17:48:235;SOME STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;NOT_OK;
 05/17/2019 23:08:652;SOME STRING;Nameofthemachine_Machine1_B_FAILURE_LEVEL;XXX;NOT_OK;
STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;OK;
 05/17/2019;23:53:232;SOMESTRING;MACHINE1_STATE;NOT_READY;

I got in return :

Machine1_A_Fail : 3
Machine1_B_Fail : 3

And in fact splunk return :

Machine1_A_Fail : 2
Machine1_B_Fail : 2

Because he counts the 2 transactions and not the 3 occurrence of the string

[le_barbucheron]

Hi @Sukisen1981,

I tried your query and that return me the number of time the string "FAILURE_LEVEL" occurs and that nearly what I wanted 🙂

How can I modify your query to be more precise on the string searched ?

thx for your answers

[Sukisen1981]

try this

  |  rex field=Name "Nameofthemachine_(?<machine>.*?)_FAILURE_LEVEL;XXX;(?<state>.*);" max_match=0
    | eval x=mvzip(machine,state)
    | mvexpand x
    | fields x
    | rex field=x "(?<machine>.*?),(?<state>.*)" 
    | table machine,state
    | where state ="NOT_OK"
    | stats count by machine

Before the first rex you need to give your events, like -
(index="index1") Equipement="Machine1" (New_State=ready OR New_State=Not_ready OR id ="*Machine1_B_FAILURE_LEVEL" OR id ="*Machine_A_FAILURE_LEVEL") | transaction Equipement host startswith="New_State="Ready" endswith="New_State=Not_ready" |...apply my code...

[Sukisen1981]

hi @le_barbucheron please try the above and check

[le_barbucheron]

Hi,

I tried your query and I got not result, but when i use :

  |rex field=_raw "FAILURE_LEVEL;([^;])+;([^;])+;([^;])+;([^;])+;([^;])+;([^;])+;(?<state>.*)" max_match=0
     | table state

I got the right state in the table

so now I just got to count the number of time the state I want is in the table

[Sukisen1981]

Hi, this is doable although we might need some more iterations.
I am still not getting your requirements.
You say - Machine1_A_Fail : 3
Machine1_B_Fail : 3

But from your example log immediately above this, I can see machine A fail 4 times , 2nd line,4th line,6th line,8th line and 10th line
Machine B fails 4 times - 3rd,5th,7th and 9th line.
So on what basis are you expecting count of 3 fail for both machine a and b?

[le_barbucheron]

Their is two statement possible :
-Ok
-Not_ok

And the statement Not_ok for the machine1_A and machine1_B appear 1 time in the first transaction and 2 time in the second, that's why I wanted to count 3 for each

[Sukisen1981]

hmm its a bit complex to understand. Are you saying that these 2 texts -*Machine1_B_FAILURE_LEVEL" OR id ="*Machine_A_FAILURE_LEVEL") are occurring multiple times within a single event?
stats will calculate the event count and not the individual count for string occurrence.
Can you please confirm and share a snapshot of your events?

[le_barbucheron]

Yes due to the transaction my events are grouped in one and looks like that :

05/18/2019 17:08:322;SOME STRING;MACHINE1_STATE;READY;
05/18/2019 18:05:355;SOME STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;OK;
05/18/2019 18:22:460;SOME STRING;Nameofthemachine_Machine1_B_FAILURE_LEVEL;XXX;OK;
05/18/2019 19:48:127;SOME STRING;Nameofthemachine_Machine1_A_FAILURE_LEVEL;XXX;NOT_OK;
05/18/2019 21:05:237;SOME STRING;Nameofthemachine_Machine1_B_FAILURE_LEVEL;XXX;NOT_OK;
05/18/2019;SOMESTRING;MACHINE1_STATE;NOT_READY;