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.
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
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.
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
Hi rnowitzki,
It worked as desired.
Thank you very much for your support..!!