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
... View more