Splunk Search

how to find the number of hosts that never reported to splunk from a lookup with the existing query?

pavanae
Builder

I have a query as follows to display the list of hosts which are seen in last 24 hours and hosts which are not seen in last 24 hours from a list of lookup table hosts. which is working fine. But I also want to see the list of hosts which are neither seen in last 24 hours nor not seen in 24 hours. I mean the hosts which are never in splunk

The following is my query

| metadata type=hosts | search [| inputlookup hosts_test.csv | search environment="PROD" | rename my_hostname as host | eval host=lower(host) | eval recentTime=0
| table host]
| eval lastTime=coalesce(lastTime,0)
| eval timeDiff=now()-lastTime
| eval last_seen_in_24_hours=case(timeDiff==NULL, "never in Splunk", timeDiff>86400,"Systems not reported to Splunk from last 24 hours", 1==1,"Systems reported to Splunk in last 24 hours")
| eval lastReported=if(lastTime=0,"never",strftime(lastTime,"%F %T"))

Following is the result :-

alt text

Now I also want to see the remaining hosts which are never in splunk as "never in splunk". I'm trying to display the number for never seen hosts in the report along with last seen in 24 hours and not seen in 24 hours like below

last_seen_in_24_hours
systems not reported to splunk in last 24 hours 43
systems reported to splunk in last 24 hours 768
systems never reported to splunk 76

0 Karma
1 Solution

elliotproebstel
Champion

I think this should do it:

| metadata type=hosts | search [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | fields host ] | append [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0 | fields host recentTime lastTime ] | dedup host | eval category=case(recentTime>=relative_time(now(), "-24h"), "Systems reported to Splunk in last 24 hours", (recentTime<relative_time(now(), "-24h") AND recentTime>0), "Systems reported to Splunk more than 24 hours ago", recentTime=0, "Systems never reported to Splunk") | stats dc(host) as "Total Hosts" BY category

Note that your approach with applying "lastTime=0" in the inputlookup portion of your original search was having no effect - by feeding the data from the inputlookup directly into a search, Splunk was discarding all the hosts that weren't found. I added a second inputlookup inside an append command, and then I applied dedup to remove the hosts that had been found.

If there is any chance that the hostnames in your csv file have different capitalization than the values in the log entries, this conversion might be necessary:

| metadata type=hosts | search [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | fields host ] | eval host=lower(host) | append [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0, host=lower(host) | fields host recentTime lastTime ] | dedup host | eval category=case(recentTime>=relative_time(now(), "-24h"), "Systems reported to Splunk in last 24 hours", (recentTime<relative_time(now(), "-24h") AND recentTime>0), "Systems reported to Splunk more than 24 hours ago", recentTime=0, "Systems never reported to Splunk") | stats dc(host) as "Total Hosts" BY category

View solution in original post

0 Karma

elliotproebstel
Champion

I think this should do it:

| metadata type=hosts | search [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | fields host ] | append [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0 | fields host recentTime lastTime ] | dedup host | eval category=case(recentTime>=relative_time(now(), "-24h"), "Systems reported to Splunk in last 24 hours", (recentTime<relative_time(now(), "-24h") AND recentTime>0), "Systems reported to Splunk more than 24 hours ago", recentTime=0, "Systems never reported to Splunk") | stats dc(host) as "Total Hosts" BY category

Note that your approach with applying "lastTime=0" in the inputlookup portion of your original search was having no effect - by feeding the data from the inputlookup directly into a search, Splunk was discarding all the hosts that weren't found. I added a second inputlookup inside an append command, and then I applied dedup to remove the hosts that had been found.

If there is any chance that the hostnames in your csv file have different capitalization than the values in the log entries, this conversion might be necessary:

| metadata type=hosts | search [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | fields host ] | eval host=lower(host) | append [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0, host=lower(host) | fields host recentTime lastTime ] | dedup host | eval category=case(recentTime>=relative_time(now(), "-24h"), "Systems reported to Splunk in last 24 hours", (recentTime<relative_time(now(), "-24h") AND recentTime>0), "Systems reported to Splunk more than 24 hours ago", recentTime=0, "Systems never reported to Splunk") | stats dc(host) as "Total Hosts" BY category

0 Karma

pavanae
Builder

Thanks for the quick response @elliotproebstel. I still see the result displays only 2 field values and no results for "Systems never reported to splunk" looks like Splunk was still discarding all the hosts that weren't found.

0 Karma

elliotproebstel
Champion

Oops! Yes, there is a cut and paste error in my code. I'm going to fix it above - but the issue is the extra pipe after append. Sorry.

0 Karma

pavanae
Builder

No issues. I corrected that. Please find my updated responce. :).

0 Karma

elliotproebstel
Champion

Ahhh, I'm sorry for all the back and forth. I forgot to apply the conversion on the second inputlookup. Will fix now.

0 Karma

elliotproebstel
Champion

By not converting my_hostname to host in the subsearch, the inputlookup was appending nothing to the parent search. Sorry! Should be fixed now.

0 Karma

pavanae
Builder

Hi @elliotproebstel still no change. Only 2 values are displaying and no "Systems never reported to Splunk"

0 Karma

elliotproebstel
Champion

Hmm...Try this code and see if there are any hosts with recentTime=0 and lastTime=0:

| metadata type=hosts | search [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | fields host ] | append [| inputlookup mssp_dashboard_hosts_test.csv | search hpam_environment="PROD" | eval host=lower(my_hostname) | eval recentTime=0, lastTime=0 | fields host recentTime lastTime ] | dedup host

If not, are you certain there are any hosts in mssp_dashboard_hosts_test.csv with hpam_environment="PROD" that have never reported to Splunk?

0 Karma

pavanae
Builder

Hi @elliotproebstel It worked and displayed the results as follows

category Total Hosts
Systems never reported to Splunk 402
Systems reported to Splunk in last 24 hours 966
Systems reported to Splunk more than 24 hours ago 21

But the total number of hosts in the lookup is 1066. which means the Systems never reported to Splunk shoul be 1066-(966+21)=79. But here the 79 count has been displayed as 402 looks like something wrong with the calculation.

0 Karma

elliotproebstel
Champion

Hmm...I could imagine this being caused by a case mis-match (lower-case vs. upper-case) across entries in the lookup table and values in the log entries. Let's try a revision with hosts converted all to lower-case. I'll update the original post with a conversion case.

0 Karma

pavanae
Builder

great worked now. Thank you

0 Karma

elliotproebstel
Champion

Glad I could help!

0 Karma

pavanae
Builder

Great. Thanks a lot for your time. 🙂

0 Karma

elliotproebstel
Champion

I meant to mention: it might be helpful to consult the docs for metadata to determine if you really want to use lastTime or if recentTime is more appropriate for your use case:
http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Metadata

My search uses recentTime, because that references the last indextime of an event from the particular host, which seemed most useful for determining when a host actually last contacted Splunk. But if lastTime really is best for your use case, replace recentTime with lastTime in the last portion of my code above.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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