topic filter results inside a chart... in Splunk Search

filter results inside a chart...

JWBailey — Mon, 28 Sep 2020 14:57:25 GMT

I am using:

… | chart sum(field1) over field2 by field3

to give me a nice chart of sums dependent on different fields. I would like to filter this chart to only show some of the results. How can I perform filtering based on the values inside the chart? I only want data to show up if a value in a cell is greater than zero. Or if only if one cell is greater than another cell.

I also want to eliminate entire rows based on the values within the chart. If a row contains a zero, I want to remove the entire row.

I can get pieces of it, but I feel I am missing something, I hope it is not this complicated...

This post is close, but I don’t think it is what I need, I am not dealing with a count, I am only dealing with sum. And I don’t have a static criteria I can eliminate values with, it is all based on comparisons to other values in the chart.

http://answers.splunk.com/answers/12577/filter-a-chart

I seem to keep working in circles using a combination of eventstats, dedup, transaction, multi value commands… solving one issue, but hitting another proplem.

Any help would be appreciated.

EDIT: Add examples...

My logs could look like this:

## Date - Time - UserName - BytesSent - When  
10/1/2013 - 12:00:00 - Mike - 2 - Afternoon  
10/1/2013 - 12:03:00 - Mike - 1 - Afternoon  
...  
...

But lots and lots of them, with tons of UserNames. The When field is always one of three values.

I then run a search:

... | chart sum(BytesSent) over UserName by When

To take all these logs, sum the BytesSent based on the UserName and When fields. Giving me a chart like this:

        Morning   Afternoon Evening
    Mike    1         7          3
    Steve   4         3          8
    Sue     6         5          6
    Bob     2         1          1
    Sara    3         0          2

I want to filter these results. For example, I only want to show users who sent more in the evening than the afternoon. So looking for this:

        Morning   Afternoon Evening
    Steve   4         3          8
    Sue     6         5          6
    Sara    3         0          2

Some other ideas....

I have tried using eventstats to sum the fields and add the total to each log:

... | eventstats sum(BytesSend) as Total by UserName, When

At this point I dont need all the logs anymore because the information I need is included in each one. So I can dedup:

... | dedup UserName When

Now what....? Use transaction to create multi value fields....?

.. | transaction UserName

The problem is the order of the multi value fields is not consistent. mvindex(Total,0) is not the same in each transaction. It can be any of the three values, Morning, Afternoon, or Evening. If it was consistent, we would be done. I could ultimately use table to display the results and throw a | where at the end to do the filtering.

... | table UserName Morning_Total Afternoon_Total Evening_Total | where Evening_Total>Afternoon_Total

I guess there is some way I could use eval and if statements to get the values into the right places, but I seem to run into problems with this also.

My point is.... there has to be a simpler way.....

Re: filter results inside a chart...

Ayn — Fri, 11 Oct 2013 22:01:35 GMT

I think you should add actual examples - makes it much easier to help out. Events, searches you're using, etc.

Re: filter results inside a chart...

dwaddle — Sun, 13 Oct 2013 19:35:59 GMT

I could be missing the point entirely, but for a results table like this:

    Morning   Afternoon Evening
Mike    1         7          3
Steve   4         3          8
Sue     6         5          6
Bob     2         1          1
Sara    3         0          2

To filter this result to only those who sent more in the Evening than Morning wouldn't it be just

| where (Evening > Morning)

Surely this can't be it?

Re: filter results inside a chart...

JWBailey — Sun, 13 Oct 2013 22:36:15 GMT

"Morning" "Afternoon" and "Evening" are the values in the When field. Evening > Morning is not valid.

Re: filter results inside a chart...

dwaddle — Mon, 14 Oct 2013 01:18:32 GMT

In your example -- which I copypasted from the question above -- they are not. "Morning", "Afternoon", and "Evening" are field names. "3" is the value of "Evening" for "Mike". Are your examples representative of your data or not?

Re: filter results inside a chart...

dwaddle — Mon, 14 Oct 2013 01:21:24 GMT

The primary point being that you can do a where to filter after running chart. By telling chart to split-by When, that makes each value of When a new field in the results table -- and therefore something you can directly compare against.

Re: filter results inside a chart...

JWBailey — Mon, 28 Sep 2020 14:57:38 GMT

Yes, a simple | where does it, see, I knew it had to be easier...

My problem was: When you use chart and the by clause to control the X axis, it lists the columns in alphabetical order, since my fields represent time frames, I actually used "1_Morning" "2_Afternoon" and "3_Evening" as the field names to get them in the order I wanted. Apparently having the number in the fieldname does something it doesnt like. As soon as I remove them, the | where command works.

I will just pipe to a table to display in the order I want instead of changing the field names.

Thank you.