Splunk Search

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

nrodrigues
Engager

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

acfecondo75
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

acfecondo75
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)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...