Splunk Search

Adding a WHERE clause to chart so only NON-matching results are shown on visualization

sepkarimpour
Path Finder

I'm currently generating a chart with ... | chart count by host source | ... so it counts the number of lines output by source on each host. It's creating three columns as it's three different sources and ideally, all three counts should be equal on each host. Is there a way you can add a WHERE clause or something so it only shows the hosts where the counts do NOT match? This is for a dashboard where we monitor log entries and want to see when they don't match, so an alert can be sent. I've attached an image to display what I'm looking for where I'd want only the yellow highlighted graphs to be shown when since they don't match.alt text

0 Karma
1 Solution

sepkarimpour
Path Finder

DalJeanis' answer worked for me:

You can't delete num_source, that's your count you want to display - the number of records for that host from the source from the host. I've renamed it to sourceCount to make it clearer, and added your required chart command at the bottom.

yoursearchhere
| stats count as sourceCount by host source
| eventstats values(sourceCount) as theCounts by host
| where mvcount(theCounts)>1
| table host source sourceCount
| chart sum(sourceCount) as count by host source

The chart command could use sum, avg, first, last, or whatever aggregate command you feel is clearest, since there's only one record for each.

View solution in original post

0 Karma

sepkarimpour
Path Finder

DalJeanis' answer worked for me:

You can't delete num_source, that's your count you want to display - the number of records for that host from the source from the host. I've renamed it to sourceCount to make it clearer, and added your required chart command at the bottom.

yoursearchhere
| stats count as sourceCount by host source
| eventstats values(sourceCount) as theCounts by host
| where mvcount(theCounts)>1
| table host source sourceCount
| chart sum(sourceCount) as count by host source

The chart command could use sum, avg, first, last, or whatever aggregate command you feel is clearest, since there's only one record for each.

0 Karma

lguinn2
Legend

Try this

yoursearchhere
| chart count as num_source by host source 
| eval sourceValues = ""
| foreach * [eval sourceValues = if(isnumeric('<<FIELD>>'),'<<FIELD>>' . ";" . sourceValues ,  sourceValues ]                                    
| makemv delim=";" sourceValues
| where mvcount(mvdedup(sourceValues)) > 1

The challenge is this: there is no way to be sure of the names of the fields (columns) in the report, or how many fields there will be.
What if in the future your search returned a different number of sources? I wanted to write a search that would be generally useful.

The foreach command examines each column. It ignores nonnumeric columns (like host) and grabs the value for any numeric column (the various counts). It makes a string of the values separated by semicolons. The makemv command turns that string into a multi-valued field. The where command dedups the values in the multi-valued field - if they are all the same, then the mvcount should be one.

0 Karma

sepkarimpour
Path Finder

Iguinn, I think you missed a ')' in your fourth line but even when I correct that, I'm unable to use 'isnumeric' as it's unsupported here.

DalJeanis, once I remove source from the last line, it works in the way that I want:
... | stats count as num_source by host source | eventstats values(num_source) as num_counts by host | where mvcount(num_counts)>1 | table host num_source | ...

But is there a way I can get the bars for each host to be together in a similar style as my initial graph? Currently, it'll display each bar as separate, so the host will be shown three times and doesn't show the name of each source so it's not as clear as before.

0 Karma

somesoni2
Revered Legend

The function name is just isnum.

0 Karma

DalJeanis
Legend

You can't delete num_source, that's your count you want to display - the number of records for that host from the source from the host. I've renamed it to sourceCount to make it clearer, and added your required chart command at the bottom.

yoursearchhere
| stats count as sourceCount by host source 
| eventstats values(sourceCount) as theCounts by host
| where mvcount(theCounts)>1
| table host source sourceCount
| chart sum(sourceCount) as count by host source

The chart command could use sum, avg, first, last, or whatever aggregate command you feel is clearest, since there's only one record for each.

sepkarimpour
Path Finder

Yes, when I add the last line, it works as expected. Thanks a lot for the help.

0 Karma

DalJeanis
Legend

Here's the same idea, using eventstats instead of foreach...

 yoursearchhere
 | stats count as num_source by host source 
 | eventstats values(num_source) as num_counts by host
 | where mvcount(num_counts)>1
 | table host source num_source
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

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

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...