How to achieve eval case, stats count when no event is returned?

[raghul725]

Hello All,

I have created the following search in splunk

index=* namespace=* 
|rex "Executing http:\/\/(?<rval>\w+.*)"
|eval rvl_status=case(rval=="se","E_Successful",rval=="vt","F_Successful")
|stats count by rvl_status

The E_Successful and F_Successful count will always be 1 for a date

This works perfectly.

But what I am seeking is to add another case condition, when no events are returned and call it No_Success and increase its count either by 1 or 2 or depending the count value of E_Successful or F_Successful i.e. No_Success count will be 1 if either E_Successful or F_Successful count is 0 or No_Success count will be 2 if E_Successful & F_Successful counts are 0.

I have tried various things, but I am unable to get the desired results. Could someone assist me please?

[danspav · Accepted solution]

Hi @raghul725,

This SPL got a bit more complicated than I first thought - but here goes:

| makeresults 
| eval rval="sea;sea;ses;vts" 
| makemv rval delim=";" 
| mvexpand rval 
``` The above is just creating the data. Set the values to se or vt to simulate live data```

| eval rvl_status=case(rval=="se","E_Successful",rval=="vt","F_Successful") 
| stats count by rvl_status 
``` This is where your query ended - the next stuff is new ```

``` This code makes sure that there is always a result returned so we can create "E_Successful" or "F_Successful" values if they are missing ```
| append [| makeresults ] 

``` This section calculates the No_Success value ```
| eval {rvl_status}=count 
| eval E_Successful=if(isnull(E_Successful),0,E_Successful),F_Successful=if(isnull(F_Successful),0,F_Successful)
| stats sum(E_Successful) as E_Successful, sum(F_Successful) as F_Successful
| eval No_success =case(E_Successful >0 AND F_Successful > 0,0, E_Successful >0 OR F_Successful >0,1, true(),2)
| transpose 2 column_name="rvl_status"
| rename "row 1" as count

The search does the following:

• Generate the data (you don't need this in your search)
• Make sure that there's always a value for E_Successful and F_Successful - even if the value is zero
• Calculate the No_Success count:
  E AND F = 0
  E OR F = 1
  Neither E NOR F =2

The output is always 3 rows for E_Successful, F_Successful, and No_Success:

If you want to only have rows with data, you can add : | search count >0

Hopefully that points you in the right direction.

Cheers,
Daniel

[raghul725]

Brilliant Daniel,

Sorry to sound silly

What is the purpose of 

eval {rvl_status}=count

If I remove this, No_success count is always 2 regardless of whether the ones are found or not

[danspav]

Hi @raghul725,

That's not a silly question at all.

This SPL creates field names based on field values:
| eval {rvl_status} = count

The curly brackets create a field whose name will be the value of the rvl_status field, and it is assigned count as it's value.

In this case, rvl_status can be:  "E_Successful" or  "F_Successful"

So those curly brackets create fields called "E_Successful" or "F_Successfu" with the value of count.

We use that to work out how many of each kind are there so we can work out the no_successful value.

If you're still unsure about it, perhaps this answers question covers it better:
Solved: About usage of {} in eval - Splunk Community

-Daniel

[raghul725]

Thanks Daniel