How do I compare two lookups formed from reports with IP and HOSTNAME columns, to see new devices on the later report and lost devices on the earlier report?

[lewis_bennett]

I need to compare lookup tables (e.g. 20170623.csv and 20170630.csv) which are uploaded weekly reports showing a list of IP, HOSTNAME and LASTTIMESEEN.

I'd like to see the difference in both reports, i.e. in the earlier report, which IP and HOSTNAME combinations (devices) are present but are not present on the later report, meaning we have lost/not seen that device in the week. In the later report which devices are present, meaning we have gained/seen a device in this week that was not seen in the week before.

I'd like two outputs from this, a list of devices gained and a list of devices lost.

This is my current search which adds 23/06 and 30/06 to each device entry.
I've manually checked what i should receive from this and i should get:
24 devices lost (file column as 23/06 and not 30/06), 22 devices gained (file column as 30/06 and not 23/06)
the rest should have file column as 23/06, 30/06 showing that device is seen in both lookups.

It needs to be searchable by time as I only care about the devices in the week period, some devices have a _time earlier than the period I want.

| inputlookup 20170623.csv
| eval file = "23/06"
| fillnull value="Unknown" IP HOSTNAME

| inputlookup append=t 20170630.csv
| eval file = coalesce(file, "30/06")
| fillnull value="Unknown" IP HOSTNAME

| eval _time=strptime(_time,"%b %d %Y %H:%M:%S")
| addinfo
| where _time>=info_min_time AND (_time<=info_max_time OR info_max_time="+Infinity")

| stats values(file) as files by IP HOSTNAME

| search files="30/06" NOT files="23/06"

| dedup IP HOSTNAME
| table IP HOSTNAME files

[raja8220]

How to compare the lookup file and stored logs to get the non reporting device list.

In the lookup i have my inventory. In that some device only sending logs to splunk how to get the list of device that are not getting logs ??

[somesoni2 · Accepted solution]

Try like this

| inputlookup 20170623.csv | eval From="LastWeek"
| append [| inputlookup append=t 20170630.csv | eval From="ThisWeek"]
| fillnull value="Unknown" IP HOSTNAME
| stats values(From) as From by IP HOSTNAME | where mvcount(From)=1
| eval Result=if(From="ThisWeek","Device Gained","Device Lost") | fields - From

[lewis_bennett]

Thanks!

This has helped a lot, I now need to try and tweak this so I can use the time range picker alongside the search, to only get results (devices) in those weeks, if you have any hints please feel free to let me know