Splunk Search

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

le_barbucheron
Path Finder

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.

0 Karma
1 Solution

le_barbucheron
Path Finder

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 !!

View solution in original post

0 Karma

le_barbucheron
Path Finder

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 !!

0 Karma

DavidHourani
Super Champion

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
Path Finder

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

Thank you so much again 🙂

Cheers

0 Karma

DavidHourani
Super Champion

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
Path Finder

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"

0 Karma

DavidHourani
Super Champion

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
Path Finder

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 😕

0 Karma

DavidHourani
Super Champion

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
Champion

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
Path Finder

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

0 Karma

le_barbucheron
Path Finder

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

0 Karma

Sukisen1981
Champion

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
Champion

hi @le_barbucheron please try the above and check

le_barbucheron
Path Finder

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

0 Karma

Sukisen1981
Champion

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
Path Finder

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

0 Karma

Sukisen1981
Champion

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
Path Finder

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;
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...