topic Re: How to get the non reporting device list?? in Splunk Search

How to get the non reporting device list??

raja8220 — Fri, 29 Mar 2019 12:16:33 GMT

How to get the non reporting device list??

Any way to compare the lookup and the stored data so i can get the non reporting device list??

Re: How to get the non reporting device list??

kamlesh_vaghela — Fri, 29 Mar 2019 12:22:09 GMT

@raja8220

Please share your sample search.

Re: How to get the non reporting device list??

raja8220 — Fri, 29 Mar 2019 12:36:00 GMT

For example I have a lookup file which contain my inventory with device name it contain 10 device and am getting logs only from 8 devices by which SPL command I can compare and get the list of device where am not getting any logs ????

Re: How to get the non reporting device list??

spavin — Fri, 29 Mar 2019 22:31:57 GMT

Hi @raja8220,

Without seeing your search, or any details about what you're searching for, I can only give a high-level view of how to go about this.

You can structure your search like this:
1. Main search: index=blah | stats count by host (or use | metadata for speed)
2. Append your lookup of known devices, and create a field called "totalCount" that equals zero
3. Use stats to get the first value for each host.
4. Filter out all hosts with a totalCount greater than zero

This method will get all hosts that are forwarding data, and then add a list of all known hosts. There will be duplicates - so we need to tidy it up a bit. By using stats we weed out all the duplicate events, and are left with a unique list of all the host names, and a count of how many messages were received.

Now you can filter out all hosts that have a count >0. The remaining hosts haven't communicated in your selected time period.

Here's an example:

| metadata type=hosts index=my_index 
| fields host, totalCount
| append [| inputlookup assets.csv | eval totalCount=0 | fields totalCount, host]
| stats first(totalCount) as totalCount by host
| where totalCount=0

Re: How to get the non reporting device list??

woodcock — Mon, 01 Apr 2019 04:44:45 GMT

This is the Sentinel Search problem discussed (with solution) here:

https://conf.splunk.com/session/2015/conf2015-LookupTalk.pdf

Re: How to get the non reporting device list??

raja8220 — Mon, 01 Apr 2019 11:06:20 GMT

My lookup name is sw.csv in this one field is host contain IP address of my inventory and that field am matching with my splunk default host field.

In my splunk search
index=cisco | lookup sw.csv host | table host ##getting the active device list

|inputlookup sw.csv | table host ##getting the inventory list

How to merge these two and get only the not active device list ????

Re: How to get the non reporting device list??

ashutoshab — Mon, 01 Apr 2019 21:41:42 GMT

Answered previously here

You canget this done using tstats command
You can get this done using 'tstats' command. You may run this search Every Hour, Every Day depending upon your requirement.
The below query is for any host that has not sent data for over an hour, you can change the value of 'diff>=3600' to suit your need and run a scheduled search. The 'tstats' command actually does require very less time to complete, it is blazing fast so no need to worry about performance impact.

|tstats latest(_time) as lat_time by host | eval NOW=now() | eval diff=NOW-lat_time | where diff>=3600 | table host

Re: How to get the non reporting device list??

spavin — Mon, 01 Apr 2019 22:01:47 GMT

Start with this, and tweak it to suit:

index=cisco 
| lookup sw.csv host 
| stats count by host
|append[|inputlookup sw.csv | eval count=0 | fields host, count]
| stats first(count) as count by host
| where count=0

Re: How to get the non reporting device list??

raja8220 — Wed, 03 Apr 2019 11:43:03 GMT

Am not getting the list of the device which is not sending the logs ??