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
SplunkTrust
SplunkTrust

The function name is just isnum.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...