Splunk Search

Need help in framing SPL query

RanjiRaje
Explorer
| 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
Labels (2)
0 Karma

livehybrid
Ultra Champion

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 :slightly_smiling_face:

:glowing_star: 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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...