topic Re: finding systems from a CSV that are not reporting into Splunk in Getting Data In

finding systems from a CSV that are not reporting into Splunk

jchapell — Wed, 20 Feb 2019 18:11:37 GMT

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

Re: finding systems from a CSV that are not reporting into Splunk

richgalloway — Tue, 29 Sep 2020 23:23:50 GMT

Try tstats instead of metadata.

Re: finding systems from a CSV that are not reporting into Splunk

woodcock — Wed, 20 Feb 2019 22:55:43 GMT

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") ]

Re: finding systems from a CSV that are not reporting into Splunk

jchapell — Mon, 04 Mar 2019 13:52:43 GMT

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.