Reporting

Broad categorical grouping in a report

Builder

I have a report / search that I want to segment based on the value of a certain field. Is there a quick / easy way to do this? Here's an example

fieldX   name   value
yes      joe    10
yes      pete   20
no       john   20

transform to:

"X Users"
  joe   10
  pete  20
"Non-X" 
  john  20
Tags (2)

Ultra Champion

An easy way to do that is concatenate the stuff you want to report on before the 'group by'. Since you don't provide any sample events, the example below uses web server logs, where yes/no of fieldX is http status 200 or 500, name is clientip, and value is count

sourcetype=access_combined status=200 OR status=500 
| stats count by clientip status 
| eval cip = count . " - " . clientip 
| stats list(cip) as "count - ip" by status 

Perhaps you can modify this to suit your needs.


UPDATE:

A slightly different way is to make use of the delta function to see when a a field value is the same as in the previous event. After setting the repeated value of your field to null, you can remove the delta-field with the fields command;

 sourcetype=access_combined status=200 OR status=500 
    | stats count by clientip status 
    | delta status as ds
    | eval status = if(ds==0, null(), status)
    | fields - ds

If your 'fieldX' is non-numerical you'd need to make it so, e.g. with replace just before the delta;

sourcetype=my_sourcetype 
| stats count by fieldX name 
| replace "yes" with "1" in fieldX
| replace "no" with "0" in fieldX
| delta fieldX as dX
| eval fieldX=if(dX==0, null(), fieldX)
| fields - dX 

/K

Ultra Champion

depending on your query, you might have to sort fieldX as well, prior to the delta.

0 Karma

Builder

The concatenation idea is a nice approach, but I'm already using this approach, so the row splits make the report readable.... (my query uses stats list(field3) as Type list(field4) as Dollar by User in the example below):

"X Users"
   Joe      $1223      typeA    $23
                       typeC    $12
    --------------------------------
   Pete     $1034      typeA    $29
                       typeB    $49

So using a concatenation again will probably end up a little bit unreadable.

Thanks for the delta idea, I'll give it a go now.

0 Karma