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 :-
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
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
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
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.
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.
No issues. I corrected that. Please find my updated responce. :).
Ahhh, I'm sorry for all the back and forth. I forgot to apply the conversion on the second inputlookup
. Will fix now.
By not converting my_hostname
to host
in the subsearch, the inputlookup
was appending nothing to the parent search. Sorry! Should be fixed now.
Hi @elliotproebstel still no change. Only 2 values are displaying and no "Systems never reported to Splunk"
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?
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.
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.
great worked now. Thank you
Glad I could help!
Great. Thanks a lot for your time. 🙂
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.