Help with search lookup for logs that stopped sending in the past 24 hours?

[RanjiRaje]

Hi All, I started working in splunk just few months ago and new to splunk. Can anyone help me with some idea please..

I have a lookup file (contains around 8500 rows - columns: Host, status, category)

1. to find non reporting hosts list present in my lookup file (it taking time to execute the below script)

| inputlookup lookupfile
| where category="categoryname" AND status="Active"
| fields Host
| search NOT
[tstats count where index="indexname" by Host
| fields - count]
| stats count

2. Among the non reporting hosts, have to find the list of hosts that stopped sending logs for past 24 hours. I am executing the below script with time range 24hours. I am getting incorrect result. 

| inputlookup lookupfile
| where category="categoryname" AND status="Active"
| fields Host
| search NOT
[tstats count where index="indexname" by Host
| fields - count]
| search
[tstats count where (index="indexname" earliest=-6mon@mon latest=now) by Host
| fields - count]
| stats count

Please help me correcting my script.

[gcusello]

Hi @RanjiRaje,

youshould reverse your logic because subsearches have the limit of 50,000 results, so please try something like this:

| tstats count where index="indexname" by Host
| eval Host=lower(Host)
| append [ | inputlookup lookupfile where category="categoryname" AND status="Active" | eval host=lower(host), count=0 | fields Host ]
| stats sum(count) AS total BY Host
| where total=0

Ciao.

Giuseppe

[RanjiRaje]

Hi Sir, thanks for the reply. it working good and taking less time. thanks again.
it would be better if I have suggestion for the 2nd query

[gcusello · Accepted solution]

Hi @RanjiRaje,

in this case you have to add an additions check based on time:

| tstats count where index="indexname"  earliest=-6mon@mon latest=now by Host
| eval Host=lower(Host)
| append [ | inputlookup lookupfile where category="categoryname" AND status="Active" | eval host=lower(host), count=0 | fields Host ]
| stats latest(_time) AS latest sum(count) AS total BY Host
| where total=0 OR now()-latest>86400

Ciao.

Giuseppe

[RanjiRaje]

hi sir, made few changes in the above query based on my requirement and the result is as expected. thanks for your time

[gcusello]

Hi @RanjiRaje ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉