Splunk Search

How to returns 0 or null if no results with tstats over time?

New Member

First of all, I apologize if I missed the answer somewhere and for my bad english.

I try to supervise my hosts, indexes and sourcetypes over time with percentage. And I also try to make it dynamic so without keep update a csv file.

Here is, the result expected:

_time   host    index   sourcetype  lasttime    count   perc_count  total_events
01/01/2020  fwd01   firewall    pf:filterlog    1577919599  10701461    2   469641743
01/01/2020  inflin01    linux   auditd  0   0   0   0
02/01/2020  fwd01   firewall    pf:filterlog    1578005999  65224250    14  469641743
02/01/2020  inflin01    linux   auditd  0   0   0   0
03/01/2020  fwd01   firewall    pf:filterlog    1578092399  66539689    14  469641743
03/01/2020  inflin01    linux   auditd  0   0   0   0
04/01/2020  fwd01   firewall    pf:filterlog    1578178799  38504400    8   469641743
04/01/2020  inflin01    linux   auditd  0   0   0   0
05/01/2020  fwd01   firewall    pf:filterlog    1578265199  40818288    9   469641743
05/01/2020  inflin01    linux   auditd  0   0   0   0
06/01/2020  fwd01   firewall    pf:filterlog    1578351599  89271070    19  469641743
06/01/2020  inflin01    linux   auditd  0   0   0   0
07/01/2020  fwd01   firewall    pf:filterlog    1578437999  88244234    19  469641743
07/01/2020  inflin01    linux   auditd  0   0   0   0
08/01/2020  fwd01   firewall    pf:filterlog    1578500836  70338351    5   469641743
08/01/2020  inflin01    linux   auditd  0   0   0   0

My search is something like that (on the last 7 days):

| tstats latest(_time) as lasttime count where index=* by _time host index sourcetype span=1d
| eventstats sum(count) as total_events by host index sourcetype 
| eval perc_count=round(count*100/total_events,0) 

But tstats returns me only the rows with 'pf:filterlog' as sourcetype because it's the only one which have data.
The idea is to have conditions on perc_count <=2% for example or count=0 in order to raise an alert.

I have already a search which works perfectly with 'lasttime' where each host or index or sourcetype (or all 3 at the same time) with custom threshold. So here, I want to identify the log decreases or nothing.

How to tell to tstatsto returns 0 value if it have 0 result over time? Or any tricks with another commands to get the final results?
I also already tried the append command but with the host field is complicated to merge rows with identical host/index/sourcetype.

0 Karma
1 Solution

Path Finder

I think your best bet would be too create a lookup of all the host, index, sourcetypes combinations you would expect to see. Perhaps by running a search like the following over the past 30 days:

 | tstats count by host, index, sourcetype | table host, index, sourcetype | outputlookup lookupname.csv

Then you can start your search by outputting the results of that lookup and then using a left join with a subsearch that uses your original logic to add the count, perc_count, and total_count fields that you want. By using a left join, you will either have a value in the desired fields or they'll be null. Then you can use the fillnull command to populate them with zeros or use a where isnull(count) to detect these combos that didn't report events. The logic would look like this:

| inputlookup lookupname.csv
| join type=left host, index, sourcetype
[| tstats latest(_time) as lasttime count where index=* by _time host index sourcetype span=1d
 | eventstats sum(count) as total_events by host index sourcetype 
 | eval perc_count=round(count*100/total_events,0)]
| where isnull(count)

View solution in original post

0 Karma

Path Finder

I think your best bet would be too create a lookup of all the host, index, sourcetypes combinations you would expect to see. Perhaps by running a search like the following over the past 30 days:

 | tstats count by host, index, sourcetype | table host, index, sourcetype | outputlookup lookupname.csv

Then you can start your search by outputting the results of that lookup and then using a left join with a subsearch that uses your original logic to add the count, perc_count, and total_count fields that you want. By using a left join, you will either have a value in the desired fields or they'll be null. Then you can use the fillnull command to populate them with zeros or use a where isnull(count) to detect these combos that didn't report events. The logic would look like this:

| inputlookup lookupname.csv
| join type=left host, index, sourcetype
[| tstats latest(_time) as lasttime count where index=* by _time host index sourcetype span=1d
 | eventstats sum(count) as total_events by host index sourcetype 
 | eval perc_count=round(count*100/total_events,0)]
| where isnull(count)

View solution in original post

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!