Dynamic table with missing event

[c_prateesh]

Hi

I am building a table with some metrics on the http access to different services reported in the apache WAF logs.
I use the field extraction to build the table dynamically and display the number of hits to each service.

index=apache-access | eval destination=split(...) | table count by destination

If there is no traffic on a specific destination, it does not appear in the table. However i would like to show count 0 to initiate a warning that there is no traffic. I do not want to do a targeted search on each service as it could generate 100s of searches.

How can i achieve the above with a static column to declare the expected services and then have dynamic count in the second column linked to those services

Prateesh

[elliotproebstel · Accepted solution]

Presumably, you have a pre-defined list of services that you'd expect to see in the table (or else you wouldn't notice they were missing). I'd recommend collecting those in a single-column csv file - perhaps apache_services.csv - with a field header of destination (to match your query structure above) and create a lookup from this file in Splunk. Then you can do this:

index=apache-access 
| eval destination=split(...) 
| stats count by destination
| append 
 [| inputlookup apache_services.csv
  | eval count=0 ]
| stats max(count) AS count BY destination

This will allow you to first count the number of hits per destination in the log file, then append the list of possible services from the lookup file, with a presumed count of 0. The final stats call will pick up the higher count value if it was found in the logs.

[c_prateesh]

Thanks a lot. It works well.

[p_gurav]

Can you provide sample data ? When you write | stats count by destination , are you getting missing events?

[c_prateesh]

Lets assume i have 3 services serviceA, serviceB, serviceC

In my logs i have (Note: i have no logs for serviceC during the timeframe i selected.
uri=/serviceA/xyz/..
uri=/serviceA/def/..
uri=/serviceB/abc/..

so when i search and parse i get the following result
serviceA : 2
serviceB : 1

What i want is
serviceA : 2
serviceB : 1
serviceC : 0