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

[anu1729]

 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"  

[gcusello]

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

[anu1729]

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

[ITWhisperer]

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.

[anu1729]

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

[ITWhisperer]

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

[anu1729]

yes

[ITWhisperer]

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

[anu1729]

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

[ITWhisperer]

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.

[ITWhisperer]

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