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 tstats
to 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.
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)
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)