Search help -- my search is inaccurately showing if hosts have been online in the past 24 hours.

[pavanae]

I have a query as follows

| inputlookup ABCD  | search Forward="Yes" |  table Region,IPHost, ip_address  | rename Region AS my_region, IPHost AS my_hostname, ip_address AS my_ip            
                                                                                                                                                                                                                        |  join type=left my_hostname [|metadata type=hosts index=* | rename host AS my_hostname] 
 |eval lastTime=if(lastTime>0,lastTime,0)
 |eval timeDiff=now()-lastTime
 | eval last_seen_in_24_hours=if(timeDiff>86400,"NO","YES")
 | eval lastReported=strftime(lastTime,"%F %T")
 | table my_region,my_hostname,last_seen_in_24_hours,lastReported

Which displays the results as follows

For some reason though the hosts were reporting to splunk (I verified that by picking the hosts from the dashboard and searching with host="abcd" for last 24 hours) all I can see from the dashboard is NO for last_seen_in_24_hours field.

Could anyone let me know where did i made the mistake or help me modifying the query to display the 4 accurate results like YES for all the hosts which were reporting

[DalJeanis · Accepted solution]

Try this -

| inputlookup ABCD  
| search Forward="Yes" 
| table Region,IPHost, ip_address  
| rename Region AS my_region, IPHost AS my_hostname, ip_address AS my_ip            
| join type=left my_hostname 
    [| metadata type=hosts index=* 
     | rename host AS my_hostname] 
| eval lastTime=coalesce(lastTime,0)
| eval timeDiff=now()-lastTime
| eval last_seen_in_24_hours=if(timeDiff>86400,"NO","YES")
| eval lastReported=if(lastTime=0,"never",strftime(lastTime,"%F %T"))
| table my_region,my_hostname,last_seen_in_24_hours,lastReported

[pavanae]

Actually the query works good for most of the hosts but there are few hosts which still shows never seen and NO in the dashboards though events can be seen when try to search with just the host or using the query (| metadata type=hosts index=* | search host="A") I have more than 600 results. Does the parsing has any limitation? @DalJeanis

[DalJeanis]

@pavanae - the only potential issue I can see is case sensitivity. If the lookup table doesn't have the exact same case for the host name as the metadata does, then they wont join. Try this -

| inputlookup ABCD  
 | search Forward="Yes" 
 | table Region, IPHost, ip_address  
 | rename Region AS my_region, ip_address AS my_ip
 | eval my_hostname=lower(IPHost)            
 | join type=left my_hostname 
     [| metadata type=hosts index=* 
      | eval my_hostname=lower(host) 
      | table my_hostname lastTime] 
 | eval lastTime=coalesce(lastTime,0)
 | eval timeDiff=now()-lastTime
 | eval last_seen_in_24_hours=if(timeDiff>86400,"NO","YES")
 | eval lastReported=if(lastTime=0,"never",strftime(lastTime,"%F %T"))
 | table my_region,my_hostname,last_seen_in_24_hours,lastReported

[pavanae]

Thanks for the response @DalJeanis. Its looks like the issue is not case sensitive both the lookup table hosts and metadata hosts were in lower case but the only issue I have identified is below

For example host="abc" is seen as "NO" in the dashboard though it's been reporting. I tried to check if the metadata query works to find that host

| metadata type=hosts index=* | search host="abc"

result :- No result fount
warning :- Metadata results may be incomplete: 100000 entries have been received from all peers (see parameter maxcount under the [metadata] stanza in limits.conf), and this search will not return metadata information for any more entries.

Is there any way that i can filter the search. I think the above warning causing the issue.

[pavanae]

Great. Thanks for the answer Daljeanis. Could you also modify the above query to calcullate the percentages of Yes's and No's from the above results in a Pie chart.

[DalJeanis]

sure, just add

| stats count by last_seen_in_24_hours

[cpetterborg]

The first time you reference lastTime is in the eval to set lastTime. Is it a field that comes through from somewhere in your events? If it isn't set by the time you get to the eval, it will evaluate to 0, and now()-0 is greater than 86400.