Splunk Search

filter results inside a chart...

JWBailey
Communicator

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

Tags (2)

dwaddle
SplunkTrust
SplunkTrust

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?

JWBailey
Communicator

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.

dwaddle
SplunkTrust
SplunkTrust

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.

0 Karma

dwaddle
SplunkTrust
SplunkTrust

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?

0 Karma

JWBailey
Communicator

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

0 Karma

Ayn
Legend

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

0 Karma
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 ...