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.
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
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
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
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
hi sir, made few changes in the above query based on my requirement and the result is as expected. thanks for your time
Hi @RanjiRaje ,
good for you, see next time!
Ciao and happy splunking
Giuseppe
P.S.: Karma Points are appreciated 😉