Report on latest status per host

[pdjhh]

Hi,

I have put together a database input that queries a sql table that logs events against hosts. The events I"m interested in are when a host gets put into maintenance mode and gets taken out again. The end goal is to have a report or table that tells us which of our hosts are in maintenance mode, even better, what percentage of our total count of available hosts are free ie: NOT in maintenance mode. The maintenance mode I'm talking about is where you can take VDIs out of pools in VMWare btw.

The db results produce events that list the hostname, the person who made the change and the status change itself.

ModuleAndEventText="DOMAIN\user updated desktop hostname to enter maintenance mode"

and

ModuleAndEventText="DOMAIN\user updated desktop hostname to exit maintenance mode"

I've extracted the fields and am thinking I need to do something along the lines of check the latest event per host for its status (enter or exit) then list the ones with last status 'enter'? There could be a much better way of doing it I don't know.

This is where I am so far, the fields with 'maintmode' in the front are the extracted fields:

index=indexname sourcetype=XYZ:DB maintmode_host=wildcard | stats first(maintmode_state) as status by maintmode_host

This just gives me a list of the last status messages per host so not too helpful. Even if I could have last status and the timestamp would be something.

Thanks.

[diogofgm · Accepted solution]

base search

index=indexname sourcetype=XYZ:DB maintmode_host=wildcard 

add this after the base search to get a table with time, host and state

... | table _time maintmode_host maintmode_state | dedup maintmode_host 

add this after after the table command to get number of hosts by state

... | stats count by maintmode_state

[pdjhh]

Thanks for answering. Will the dedup take the last entry for the host? Each host could have many events for enter and exit over time you see. It's the last one we're interested in as that's the state it should be in now. I then need to know how many hosts are in the 'enter' state at this point in time and when they got put into that state ideally.

[diogofgm]

the dedup will keep the first occurrence of each value for the given field. Since you'll have you table ordered by _time (same order as event in the search results) it will give you the last entry for each host.

the complete search will give you how many are enter and exit:
index=indexname sourcetype=XYZ:DB maintmode_host=wildcard | table _time maintmode_host maintmode_state | dedup maintmode_host | stats count by maintmode_state

to get the list of the ones enter:
index=indexname sourcetype=XYZ:DB maintmode_host=wildcard | table _time maintmode_host maintmode_state | dedup maintmode_host | search maintmode_state="enter"

in the last search just replace "enter" in the maintmode_state with whatever you want to look for.

There are great way to improve your searches. If you have time take a look at eventtypes.