topic Re: Eval field to get total count and plot the chart in Splunk Search

How to search Eval field to get total count and plot the chart?

anu1729 — Mon, 04 Apr 2022 14:56:01 GMT

We want to get the number of successful login, multiple successful login, multi-fail logins and also number the of hqid which has not logged in i.e (total number of hqid - sum(successful login + multiple successful login + multi fail).

We have written below query, and we are able to get the number of successful login, multi-success login and as well multi-fail but I am not sure how to get the number for not logged-in case. Could anyone please help me here

base_search query | eval hqid = substr(requestURI,23,10) | table hqid httpStatus | eval status-success=if(httpStatus="200",1,0) | eval status-fail= if(httpStatus != "200",1,0) | stats sum(status-success) as status-success, sum(status-fail) as status-fail by hqid | eval status = case(('status-fail'=0 AND 'status-success'>0), "successful-logins", ('status-fail'>0 AND 'status-success'>0), "multi-success", ('status-fail'>0 AND 'status-success'=0), "multi-fail", ('status-fail'>0), "fail",1=1, "Other"

Re: Eval field to get total count and plot the chart

ITWhisperer — Mon, 04 Apr 2022 10:00:41 GMT

If httpStatus isn't present in the event, it doesn't get counted. You could try counting all the events which don't have httpStatus and include that in your total?

| eval status-success=if(httpStatus="200",1,0) | eval status-fail= if(httpStatus != "200",1,0) | eval status-missing= if(isnull(httpStatus),1,0) | stats sum(status-success) as status-success, sum(status-fail) as status-fail sum(status-missing) as status-missing by hqid

Re: Eval field to get total count and plot the chart

gcusello — Mon, 04 Apr 2022 10:03:58 GMT

Hi @anu1729,

you could add a streamstats command before the stats command to have the total events, something like this:

base_search query | eval hqid = substr(requestURI,23,10) | fields hqid httpStatus | streamstats count AS total | eval status-success=if(httpStatus="200",1,0), status-fail=if(httpStatus != "200",1,0) | stats sum(status_success) as status_success, sum(status_fail) as status_fail values(total) AS total by hqid | eval status = case((status_fail=0 AND status_success>0), successful_logins, (status_fail>0 AND status_success>0), multi_success, (status_fail>0 AND status_success=0), multi_fail, (status_fail>0), fail,1=1, Other) | eval not_logged_in=total-successful_logins-multi_success-multi_fail-Other

Don't use "-" in the field names, use always "_", so you don't need to use quotes.

Ciao.

Giuseppe

Re: Eval field to get total count and plot the chart

anu1729 — Mon, 04 Apr 2022 10:26:48 GMT

streamstats is giving the total count at that time, but we need to get the not-logged -in value as we have fixed number of hqid and we want to check how many of them have not logged and how many of them are able to successfully log in , or multi-fail is happening, or multi-success

Re: Eval field to get total count and plot the chart

ITWhisperer — Mon, 04 Apr 2022 10:54:46 GMT

So you are trying to count events that haven't happened? Essentially, you need to create some events which splunk can count or simply tell splunk what the total should be.

Re: Eval field to get total count and plot the chart

anu1729 — Mon, 04 Apr 2022 12:06:49 GMT

Yes we are trying to get the count for those event which has not happened. we have used the below query to get the count of not-logged-in but we are not able to club with the eval statement for status.

| eval hqid = substr(requestURI,23,10) | table hqid httpStatus | eval status_success=if(httpStatus="200",1,0) | eval status_fail= if(httpStatus != "200",1,0) | stats sum(status_success) as status_success, sum(status_fail) as status_fail by hqid | eval status = case((status_fail>0 AND 'status_success'>0), "multiple successful logins", ('status_fail'>0), "multi fail", ('status_success'>0), "successfull login",1=1, "Other") | eval logged_in = status_success+status_fail | eval not_logged_in = 28-logged_in

we want the output to be in stacked form , like on a particular date how many of them were successful, multi-fail, multi-success and not logged in

Re: Eval field to get total count and plot the chart

ITWhisperer — Mon, 04 Apr 2022 13:03:49 GMT

Do you know all the hqids that you have that could potentially try to login?

Re: Eval field to get total count and plot the chart

anu1729 — Mon, 04 Apr 2022 14:38:06 GMT

yes

Re: Eval field to get total count and plot the chart

ITWhisperer — Mon, 04 Apr 2022 15:03:57 GMT

In that case, you should include them in your search so you can count them.

Re: Eval field to get total count and plot the chart

anu1729 — Mon, 04 Apr 2022 15:23:53 GMT

how to include that and how we will get data in the stacked format.

Re: Eval field to get total count and plot the chart

ITWhisperer — Mon, 04 Apr 2022 15:34:07 GMT

It depends where you data is and how much of it there is. You can use append but you are limited to the number of events you can add the the pipeline in a single append, although you can use multiple appends.