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

[nrodrigues]

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.

[acfecondo75 · Accepted solution]

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)