Splunk Search

How to Display Only Count of 0

vwilson3
Path Finder

Greetings,

I've been trying to tweak an inherited report to only show the results where the count of events is blank (or zero). Here is a sanitized version of the search string for the report. It takes an input file with our host names and indexes we should see events from for the different apps and OS' and displays the counts and indexes received events into for the last xx hours. That part works well. Now, I need to show only the results where the event count is blank or zero. Any suggestions? Thanks in advance.

Tags (1)
0 Karma

vwilson3
Path Finder

Thank you, Woodcock, for your answer. I tried your suggestion and it works. It shows only those systems that have no events. I noticed that the output does not show the Target data (which comes from the input file), listing the indexes for each host in which we expect to see events.

0 Karma

woodcock
Esteemed Legend

The reason that it is auto-finalizing is that it is a very inefficient search. Try this optimized version:

|tstats count WHERE (index!="_internal" AND index!="_audit") BY host index 
| eval host=upper(if(match(host, "^[\d\.]+$"), host, replace(host, "\..*$", "")))
| search [| inputlookup myinput.csv | fields host] 
| stats list(index) AS index values(count) AS count BY host 
| fields host, index, count 
| append [| inputlookup lmyinput.csv | fields host] 
| dedup host 
| lookup myinput.csv host 
| fields host, sys_purpose, opsys, index, count, note app* os*
| eval Target=mvdedup(mvappend(app1, app2, app3, app4, app5, app6, app7, app8, app9, app10, app11, app12, app13, app14, os1, os2)) 
| table host, sys_purpose, opsys, Target, index, count, note 
| rename COMMENT AS "Values for 'host' from the file but not in the data have no value (null()) for 'count'"
| where isnull(count) 
| rename host AS "Host Name:", sys_purpose AS "System Purpose:", opsys AS "Operating System:", Target AS "Expected Index:", count AS "Event Count:", index AS "Received Events in Index:", note AS"Note:"
0 Karma

vwilson3
Path Finder

I finally figured it out... made a few changes:

index!=_internal index!=_audit | stats count by host,index | rex field=host "^(?\w+)\.?" | eval host=upper(host) | search [|inputlookup myinput.csv | fields host] | stats list(index) as index values(count) as count by host | fields host, index, count | append [|inputlookup lmyinput.csv | fields host] | dedup host | lookup myinput.csv host | fields host, sys_purpose, opsys, index, count, note | lookup myinput.csv host | eval Target=mvdedup(mvappend(app1, app2, app3, app4, app5, app6, app7, app8, app9, app10, app11, app12, app13, app14, os1, os2)) | table host, sys_purpose, opsys, Target, index, count, note | where isnull(count) | rename host AS "Host Name:", sys_purpose AS "System Purpose:", opsys AS "Operating System:", Target AS "Expected Index:", count as "Event Count:", index as "Received Events in Index:", note as "Note:"
0 Karma

vwilson3
Path Finder

This works for a short period of time, say the last 10 minutes, but not for the last 24 hours. The search runs for a while then auto-cancels. Alas, back to the drawing board. Anyone have any suggestions?

0 Karma

vwilson3
Path Finder

Sorry, hit the post button before adding the search...

| tstats count where index!=_internal AND index!=_audit by host,index | rex field=host "^(?\w+)\.?" | eval host=upper(host) | search [|inputlookup myinput.csv | fields host] | stats list(index) as index values(count) as count by host | fields host, index, count | append [|inputlookup myinput.csv | fields host] | dedup host | lookup myinput.csv host | fields host, sys_purpose, opsys, index, count, note | lookup myinput.csv host | fields - fqdn | eval Target=mvsort(mvdedup(mvappend(app1, app2, app3, app3, app5, app6, app7, app8, app9, app10, app11, app12, app13, app14, app15, os1, os2))) | fields host, sys_purpose, opsys, Target, index, count, note | rename host AS "Host Name:", sys_purpose AS "System Purpose:", opsys AS "Operating System:", Target AS "Expected Index:", count as "Event Count:", index as "Received Events in Index:", note as "Note:"
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...