Alert Query not working as expected

[mahesh27]

index=app-logs sourcetype=app-data source=*app.logs*  host=appdatajs01 OR host=appdatajs02 OR host=appdatajs03 OR host=appdatajs04

|stats count by host
|where count < 100
|bin span=1m _time

We have an alert with the above query,  Alert is getting triggered when the count of hosts is less than 100. but not getting triggered when the count of any  host is zero.
How to make the alert to trigger even when the count=0

[gcusello]

Hi @mahesh27 

As @bowesmana said, this is a classic proving the negative issue and you can find thousands of answers in Community.

In this case you have two solutions:

if you have a list of hosts to monitor to put in a lookup (called e.g. perimeter.csv with at least one column called host), you could run something like this

| tstats 
   count 
   WHERE
      index=app-logs 
      sourcetype=app-data 
      source=*app.logs* 
      host IN (appdatajs01, appdatajs02, appdatajs03, appdatajs04)
   BY host
| append [ 
   | inputlookup perimeter.csv
   | eval count=0
   | fields host count ]
| stats sum(count) AS total by host
| where total<100

If you don't have a lookup or you don't want to manage it, you could run something like this:

| tstats 
   count 
   latest(_time) AS _time
   WHERE
      index=app-logs 
      sourcetype=app-data 
      source=*app.logs* 
      host IN (appdatajs01, appdatajs02, appdatajs03, appdatajs04)
      earliest=-30d@d
      latest=now
   BY host
| where _time<now()-3600

In this way, you have the hosts that sent logs in the last 30 days but not in the last hour (you eventually can modify the time periods).

in addition the command | bin span=1m _time has no sense because you don't use time in your stats.

Ciao.

Giuseppe

[bowesmana]

This is the classic proving the negative issue. Splunk will count events based on your search criteria. It's can't create new categories of result for things that are not there. You need to explicitly add the hosts into the final result table if they are not present in the first count.

You can do something like this, which will add in zero values for your 4 hosts and then max the count for each host

index=app-logs sourcetype=app-data source=*app.logs*  host=appdatajs01 OR host=appdatajs02 OR host=appdatajs03 OR host=appdatajs04
|stats count by host
| appendpipe [
 | where a=1
 | makeresults 
 | fields - _time
 | eval host=split("appdatajs01,appdatajs02,appdatajs03,appdatajs04",",")
 | mvexpand host
 | eval count=0
]
| stats max(count) as count by host
| where count<100

Note that your statement "|bin span=1m _time" does nothing because you have no time field after the stats command

Normally with this proving the negative technique, you would add all the hosts you are interested in into a lookup file and instead of appendpipe, use

| inputlookup append=t host_lookup.csv 
| fillnull count value=0
| stats...

[mahesh27]

@bowesmana , tried below query but its not working

index=app-logs sourcetype=app-data source=*app.logs*  host=appdatajs01 OR host=appdatajs02 OR host=appdatajs03 OR host=appdatajs04
| inputlookup append=t host_lookup.csv 
| fillnull count value=0
| stats count by host

below is the csv file used:
Hosts
appdatajs01
appdatajs02
appdatajs03
appdatajs04

[mahesh27]

index=app-logs sourcetype=app-data source=*app.logs*  host=appdatajs01 OR host=appdatajs02 OR host=appdatajs03 OR host=appdatajs04
| eval event_ct=1
| append [| makeresults 
    | eval host="appdatajs01, appdatajs02, appdatajs03, appdatajs04"
    | rex field=host mode=sed "s/\s+//g"
    | eval host=split(host,",")
    | mvexpand host
    | eval event_ct=0
    ]
| stats sum(event_ct) AS event_ct BY host
| where event_ct <100

 tried the above query its working as expected, but i need to see data in a span of1m
i tried add |bin span=1m
but its not working
Can anyone help on this request????

[bowesmana]

In your first attempt it should have been like this

index=app-logs sourcetype=app-data source=*app.logs*  host=appdatajs01 OR host=appdatajs02 OR host=appdatajs03 OR host=appdatajs04
| stats count by host
| inputlookup append=t host_lookup.csv 
| fillnull count value=0
| stats max(count) as count by host
| where count<100

 but if you want to search at 1 minute granularity, what if one minute is > 100 and 1 is < 100