How to write a search and set up an alert using the metadata command to find hosts that are not reporting in?

[hartfoml]

I can do this search | metadata type=hosts | convert timeformat="%m/%d/%Y - %H:%M:%S" ctime(*Time)

This will give me the date fields of first, last and recent Time.

I want to Pipe to an if statement to see if the recent or last time is between yesterday and 14 days ago and report on only those servers.

If a server has not been reporting in 14 days it is most likely off the network for good.

How do I correct the syntax for | eval not_reporting=case(lastTime>yesterday, alert, lastTime<30days_ago, alert)

Any help with syntax would be great.

[somesoni2 · Accepted solution]

Try this

|metadata type=hosts | table host lastTime | where lastTime<relative_time(now(),"-1d@d") AND lastTime>relative_time(now(),"-30d@d") | convert timeformat="%Y%m%d" ctime(lastTime) AS LastTime

[revanthammineni]

Hi! I’m using metadata to report the missing logs in splunk. I’m giving a eval condition as 

eval reporting = case(isnull(lastTime), “no”,1=1, “yes”)..

so where ever there is “no”,  I’m counting those hosts and reporting. I want to know if there is any dynamic way of passing time to the query since the above mentioned logic isn’t working for me.

Thanks In advance.

[brod_geico]

This is not working for me
|metadata type=hosts | table host lastTime | where lastTimerelative_time(now(),"-30d@d") | convert timeformat="%Y%m%d" ctime(lastTime) AS LastTime

[woodcock]

You are missing < and > characters and merging 2 existing field names into garbage ones.

[hartfoml]

This works great, Thanks much for the help

[hartfoml]

I can do this type of search but how do I make the search dates dynamic

| metadata type=hosts | convert timeformat="%Y%m%d" ctime(lastTime) AS LastTime | table host LastTime | where LastTime<20141030 AND LastTime>20140930