Reporting

report count from hosts in csv file INCLUDING hosts with count=0

stevenatmit
Explorer

I'm looking for a report that can import a csv file of hosts and display the event count for the past 7 days. This csv file may have systems that have never reported to splunk and thus may have a count of zero. I can get a report that list hosts with count > 0, but it won't list the ones where count = 0. Any help would be appreciated.

Tags (2)
0 Karma

stevenatmit
Explorer

I finally found something that works, but it is a slow way of doing it.

index=* [|inputcsv allhosts.csv] | stats count by host | stats count AS totalReportingHosts| appendcols [| inputlookup allhosts.csv | stats count AS totalAssets]

0 Karma

stevenatmit
Explorer

sure. thanks for the offer of help. my search looks like this

index=* [|inputcsv allhosts.csv] | stats count by host

and my csv file looks like this

host
goodhost1
goodhost2
unknownhost1

It reports counts from the "good" hosts, but won't report a 0 from the "unknown" host

0 Karma

somesoni2
Revered Legend

Assuming you have the csv file added as lookup table, try something like this

|inputcsv allhosts.csv | eval count=0 | join type=left host [search index=* [|inputcsv allhosts.csv] | stats count by host]
0 Karma

somesoni2
Revered Legend

My bad. try now.

0 Karma

stevenatmit
Explorer

that fails with 'unknown search command 'index'

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If you're willing to share your existing query, we may be able to figure out why hosts with count=0 aren't showing up.

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

The Payment Operations Wake-Up Call: Why Financial Institutions Can't Afford ...

The same scenario plays out across financial institutions daily. A payment system fails at 11:30 AM on a busy ...

Make Your Case: A Ready-to-Send Letter for Getting Approval to Attend .conf25

Hello Splunkers, Want to attend .conf25 in Boston this year but not sure how to convince your manager? We've ...

Community Spotlight: A Splunk Expert's Journey

In the world of data analytics, some journeys leave a lasting impact not only on the individual but on the ...