finding systems from a CSV that are not reporting into Splunk

[jchapell]

I have a search that I am working on and running into problems.

Currently, I have a CSV generated that contains all of our hosts and their statuses on our network — in use, expired, disposed, etc. I am trying to search on "in use" status and find the first/last time they reported into Splunk.

Well, I thought it was working great with the search I had, but there are systems showing up as not reporting in a number of days, yet host=foo yields results that are current. Below is my search:

| metadata type=hosts 
| lookup assets.csv "short_names" as host Output "asset_state" as Reason 
| where Reason="In Use"
| dedup host firstTime recentTime lastTime
| eval diff= (now()-recentTime)/86400
| convert ctime(firstTime) timeformat="%Y-%m-%d %H:%M:%S"
| convert ctime(lastTime) timeformat="%Y-%m-%d %H:%M:%S"
| convert ctime(recentTime) timeformat="%Y-%m-%d %H:%M:%S"
| where diff > 3.0
| eval now= now()
| convert ctime(now) timeformat="%Y-%m-%d %H:%M:%S"
| sort - diff
| table host Reason diff recentTime firstTime

[jchapell]

I've tried both of these and get different results, but still pulling up systems that can be verified as having written events to splunk. I know I can't be the only one trying to leverage our assets.csv to find hosts not writing events.

[woodcock]

Try this:

| tstats min(_time) AS firstTime max(_time) AS lastTime max(_indextime) AS recentTime count AS totalCount WHERE index=* BY host 
| lookup assets.csv "short_names" as host Output "asset_state" as Reason 
| where Reason == "In Use"
| eval diff = (now() - recentTime) / 86400 
| where diff > 3.0
| eval now = now() 
| sort 0 - diff 
| table host Reason diff recentTime firstTime
| foreach now *Time [ fieldformat <<FIELD>> = strftime(<<FIELD>>, "%Y-%m-%d %H:%M:%S") ]

[richgalloway]

Try tstats instead of metadata.

| tstats earliest(_time) as firstTime, latest(_time) as lastTime where index=* by host
| lookup assets.csv "short_names" as host Output "asset_state" as Reason
| where Reason="In Use"
| eval diff= (now()-lastTime)/86400
| where diff > 3.0
| eval now= now()
| convert ctime(now) timeformat="%Y-%m-%d %H:%M:%S"
| sort - diff
| table host Reason diff lastTime firstTime