Getting Data In

finding systems from a CSV that are not reporting into Splunk

jchapell
Explorer

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
0 Karma

jchapell
Explorer

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.

0 Karma

woodcock
Esteemed Legend

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

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...