Need help in framing SPL query

[RanjiRaje]

| loadjob savedsearch="userid:search:hostslists"

| lookup lookupname Hostname as host OUTPUTNEW Hostname,IP

| eval Host=upper(host)

   | append

        [| loadjob savedsearch="userid:search:hostslists"

         | lookup lookupname IP as host OUTPUTNEW IP,Hostname

         | eval Host=upper(host)]

   | append

        [| loadjob savedsearch="userid:search:hostslists"

         | lookup lookupname AltName as host OUTPUTNEW AltName,IP,Hostname

         | where AltName != Hostname

         | eval Host=upper(host)]

| eval starttime=relative_time(now(),"-10d@d"),endtime=relative_time(now(),"-1d@d")

| convert ctime(latest),ctime(starttime),ctime(endtime)

| where latest<=endtime AND latest>=starttime

| rename latest as "Last event date", Host as "Host referred in Splunk"

| eval Hostname=if('Host referred in Splunk'!='IP','Host referred in Splunk',Hostname)

| stats count by Hostname,IP,"Host referred in Splunk","Last event date"

| fields - count

| dedup IP,Hostname

In my query I am using the saved search "hostslists" (it contains list of hosts reporting to splunk along with latest event datetime)

Lookup "lookupname" (contains fields: Hostname, AltName,IP)

Aim: Have to get the list of devices present in lookup which is not reporting for more than 10 days

Logic: some devices report with "Hostname", some devices reprot with "AltName", few devices report with "IP"

       So, I am checking all the 3 fields and capturing "Last event date"

Now, I am facing challenge, 

Hostname               IP              "Last event date"

Host1                  ipaddr1               25th July                 (by referring IP)

Host1                  ipaddr1               10th June                 (by referring Hostname)

I have 2 different "Last event date" for same "Hostname" & "IP". 

In my report, it is not showing the latest date, but Here I have to consider latest date, I am stuck how to use such logic. Can anyone please help ? Thanks for your response

[livehybrid]

Hi @RanjiRaje 

The appends definitely aren't needed here, as this runs a search for that data each time in order to do the lookup - instead you could look to do something like this:

Replace the three append branches with a single lookup that matches on any of the three possible keys, then keep the latest event per host/IP.

| loadjob savedsearch="userid:search:hostslists"
| eval host=upper(host)
| lookup lookupname Hostname as host OUTPUTNEW Hostname as H1, IP as IP1
| lookup lookupname IP       as host OUTPUTNEW IP       as IP2, Hostname as H2
| lookup lookupname AltName  as host OUTPUTNEW AltName  as A3, IP as IP3, Hostname as H3
| eval Hostname=coalesce(H1,H2,H3), IP=coalesce(IP1,IP2,IP3)
| eval starttime=relative_time(now(),"-10d@d")
| where latest>=starttime
| stats max(latest) as latest by host, Hostname, IP
| eval "Last event date"=strftime(latest,"%d %b %Y")
| table host Hostname IP "Last event date"
| rename host AS 'Host referred in Splunk'

Let me know how you get on or if any bits need tweaking or explaining 🙂

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[RanjiRaje]

Hi @livehybrid ,

Thanks a lot for your valuable SPL query. I tried using this but I am facing a challenge.

Some of the devices listed in the lookup file are not reported in Splunk, and therefore do not appear in the savedsearch results. Ideally, these devices should still be listed in the final output, but that’s not happening.

Could you please suggest a workaround to ensure that those devices are also reflected in the result?

Thanks .

[ITWhisperer]

Try adding this after the where command

| inputlookup lookupname append=t

This assumes that the fields host, Hostname, IP are fields are in your lookup, otherwise you will have to set these up before the stats command

[RanjiRaje]

Hello sir, thanks for the suggestion. But it didn't work as expected. It just appending all the devices from lookup. I need to append only the devices for which there is no entry in savedsearch

[ITWhisperer]

Without knowledge of your events and your actual search, it is difficult to say what is not working. However, it is possible that after the stats by host etc., you then need to add a where command

| where isnull(latest)

Or if that doesn't work, try adding this after the inputlookup

| eval latest=coalesce(latest,0)

 and this after the stats

| where latest=0