topic Re: Need help with query in Dashboards & Visualizations

Need help with query

Mrig342 — Thu, 28 Jan 2021 11:14:27 GMT

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.

Re: Need help with query

rnowitzki — Thu, 28 Jan 2021 12:17:51 GMT

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

Re: Need help with query

manjunathmeti — Thu, 28 Jan 2021 13:00:23 GMT

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,

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

Re: Need help with query

Mrig342 — Thu, 28 Jan 2021 13:40:32 GMT

Hi rnowitzki,

It worked as desired.

Thank you very much for your support..!!