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

[sepkarimpour]

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.

[sepkarimpour · Accepted solution]

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.

[lguinn2]

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.

[sepkarimpour]

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.

[somesoni2]

The function name is just isnum.

[DalJeanis]

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]

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

[DalJeanis]

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