Splunk Search

Syslog event count with lookup table assistance

Path Finder


I have a lookup table full of syslog hosts that are sending data to Splunk. My goal is to identify which syslog hosts have stopped sending data, so I can alert the appropriate folks. My lookup table has fields "host hostname src"; my syslog hosts are reporting host as an IP address. My search thus far (broken):

| inputlookup sysloglist | appendcols [ index=myindex sourcetype=syslog | stats count by host ] | fields host hostname count | fillnull value=0 count | sort + count

When this search runs, the intent is to serve the table, apply a search to get the event count, arrange the fields, fill null values where there are no event counts, then sort the 0's to the top (plus fancy color formatting). The issue I have encountered is that the event counts are wrong. For example, I have a syslog host called "print01" which by the above search says it has 0 events (last 60 minutes). When I run this search:

index=myindex sourcetype=syslog host=print01 | stats count

I have over 100 events, which means that the logic in my first search is incorrect. Yes, I have verified the search time frame is the same. I have tried running the search in reverse (running the count, then appendcols the table), but that does not work. I'm not sure if its a table sort issue in my csv file or if I am just missing something ridiculously obvious.

Any help is appreciated, thanks!

0 Karma

Revered Legend

Try something like this (since you're checking just the event count, will use tstats command which is way faster than regular search)

| tstats count WHERE index=myindex sourcetype=syslog by host
| append [ | inputlookup sysloglist | table host | eval count=0]
| stats max(count) as count by host 
| sort count
0 Karma

Path Finder

That makes it a lot faster than before. However, I forgot to mention that I additionally need the hostname field displayed. As soon as I add that field into the search (anywhere), it breaks consistently with what I have done above.

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...