Reporting

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
Engager

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

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

raja8220
New Member

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 ??

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
Engager

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...