Hi All,
I need help with host availability ?
I need a table to list the servers that are not sending data in select time range.. is it possible in splunk ?
this is what i have tried..
index=xyz_w source=/System.log earliest=-1d@d latest=now
| stats values(host) as today
| appendcols
[ search index=XYZ source=/System.log earliest=@w-24h latest=@d
| stats values(host) as yesterday]
| eval different=today-yesterday
| table host
@VI371887, as mentioned by @renjith.nair, if you need to work only with metadata field like host source sourcetype you are better off running commands specifically for such use case like tstats or metadata.
Since your objective is to compare the hosts that reported yesterday but not today. You can run a search like the following:
| tstats count as "Yesterday" where index="int_gcg_apac_eclipse_mw" source="*/System*.log" earliest=-1d@d latest=-0d@d-1s by host
| appendcols [| tstats count as "Today" where index="int_gcg_apac_eclipse_mw" source="*/System*.log" earliest=-0d@d latest=now by host]
| fillnull value=0
| search Today=0
I ended up using..
| inputlookup hostlist.csv | eval ActiveServer=case(isnull(host),"NO") |
join type=left host [search index="YourIndex" source="YourSource" |
stats count by host ] | fillnull value=0 | search count=0 | table host Role
Where hostlist.csv is lookup with host and its role..
@VI371887, as mentioned by @renjith.nair, if you need to work only with metadata field like host source sourcetype you are better off running commands specifically for such use case like tstats or metadata.
Since your objective is to compare the hosts that reported yesterday but not today. You can run a search like the following:
| tstats count as "Yesterday" where index="int_gcg_apac_eclipse_mw" source="*/System*.log" earliest=-1d@d latest=-0d@d-1s by host
| appendcols [| tstats count as "Today" where index="int_gcg_apac_eclipse_mw" source="*/System*.log" earliest=-0d@d latest=now by host]
| fillnull value=0
| search Today=0
Thanks!!
That Worked.
So what would i have to do if i want to list down the role this servers belongs to.. as tag under which i have gave them role isn't working, I tried
| tstats count as "Yesterday" where index="xyz_w" source="/System.log" earliest=-1d@d latest=-0d@d-1s by host
| appendcols [| tstats count as "Today" where index="xyz_w"" source="/System.log" earliest=-0d@d latest=now by host]
| fillnull value=0
| search Today=0 | table tag host | rename tag as role host as Hosts
also tried appending tag=*
| tstats count as "Yesterday" where index="xyz_w" source="/System.log" earliest=-1d@d latest=-0d@d-1s by host
| appendcols [| tstats count as "Today" where index="xyz_w"" source="/System.log" earliest=-0d@d latest=now by host]
| fillnull value=0
| search Today=0 | append [search tag=*]| table tag host | rename tag as role host as Hosts
You can create a lookup of all existing hosts with required details like tags and then enrich the host details and also populate missing hosts. You can find examples on Splunk Answers for finding missing host through lookup file.
I tried looking for them, but that would require access to the _internal index, do we have any other way possible ?
Hi @VI371887 ,
Easiest method is to see when the last event was sent and compare it with current timestamp. This is on the assumption that the host sends data to splunk continuously when it's available. In that case you could use
|tstats latest(_time) as last_time by host|eval down=now()-last_time|where down > "your threshold seconds/minutes to check availability"