Need help with query

[Mrig342]

Hi,

I am getting  the below table using the query "index=main host="abcde" | rex field=_raw "(?ms)Node\s+Name\s:\s(?<Node_Name>\w+\S+)" | rex field=_raw "(?ms)Node\sState\s:\s(?<Node_State>[\w\s]+\w)\s+Number | eval Result=if(Node_State=="Running",  "Ok",  "NotOk") | table Node_Name,Node_State,Result"

Node_Name    Node_State                       Result

abc                      Stopped                               NotOk

cde                      Running                                Ok

abc                      Running                                Ok

xyz                       Stopped                               NotOk

the                       Running                                Ok

abc                      Partially running                NotOk

abc                      Stopped                                NotOk

xyz                       Running                                Ok

the                       Running                                Ok

abc                      Running                                Ok

Here I want to get the count of "Result=Ok" and count of "Node_State" to calculate the percentage of "Result=Ok".

I tried these queries:

"..... | search Result=ok | stats count(Result) as Total, count(Node_State) as Total1 | eval Enterprise1=round(Total/Total1*100) | fields - Total,Total1"

and

"..... | stats count(Result) as Total, count(Node_State) as Total1 | eval Enterprise1=round(Total/Total1*100) | fields - Total,Total1"

But I am getting 100% which can't be true as count of "Result=OK" is less than count of the Node_State. 

Please help me modify the query in the right way to get the desired result.

Thank You.

[manjunathmeti]

hi, @Mrig342,

You can use eval expression in count function as shown below.

| makeresults | eval _raw="Node_Name,Node_State,Result
abc,Stopped,NotOk
cde,Running,Ok
abc,Running,Ok
xyz,Stopped,NotOk
the,Running,Ok
abc,Partially running,NotOk
abc,Stopped,NotOk
xyz,Running,Ok
the,Running,Ok
abc,Running,Ok" | multikv forceheader=1 | table Node_Name,Node_State,Result 
| stats count(Node_State) as node_state_count count(eval(Result=="Ok")) as ok_count | eval Enterprise1=round(ok_count/node_state_count*100)

OR just use top command which will give the percentage of a particular value against the total event count,

| makeresults | eval _raw="Node_Name,Node_State,Result
abc,Stopped,NotOk
cde,Running,Ok
abc,Running,Ok
xyz,Stopped,NotOk
the,Running,Ok
abc,Partially running,NotOk
abc,Stopped,NotOk
xyz,Running,Ok
the,Running,Ok
abc,Running,Ok" | multikv forceheader=1 | table Node_Name,Node_State,Result
| top Result | search Result="Ok"

If this reply helps you, an upvote/like would be appreciated.

[rnowitzki · Accepted solution]

Hi @Mrig342 ,

Add this below your SPL:

| eval result_id=if(Result="Ok",1,0)
| stats count as total, sum(result_id) as goodones
| eval good_percentage=(goodones*100)/total

I think that's what you're looking for.

BR

Ralph

[Mrig342]

Hi rnowitzki,

It worked as desired.

Thank you very much for your support..!!