Splunk Search
Highlighted

AddColTotals & Percentage Column To Listed values

Path Finder

Hey guys! So i have this search:

index="servers" | stats count by Delivery, VDC, Manageable | eval temp=Delivery."#".VDC | xyseries temp Manageable count| addtotals fieldname=Count| rex field=temp "(?<Delivery>[^#]+)#(?<VDC>.+)" | fields - temp | stats sum(Count) as "Total Per Delivery" list(*) as * by Delivery| fields Delivery "Total Per Delivery" Count VDC Yes No

Manageable contains "Yes" and "No" values that are listed horizontally. It gives me the following output:

Delivery      | Total Per Delivery | Count |   VDC    | Yes | No                                                                ---------------|--------------------|-------|----------|-----|------                                                                                                                                                   company1       |                 200|    150|    Group1|  100| 50                                                                                  `              |                    |     50|    Group2|   40| 10                                                                       ---------------|--------------------|-------|----------|-----|------                                                               company2       |                 100|    100|    Group3|   75|  25

I am trying to get something like the following:

Delivery      | Total Per Delivery | Count |   VDC    | Yes | No | %                                                                ---------------|--------------------|-------|----------|-----|----|---                                                                                                                                                   company1       |                 200|    150|    Group1|  100| 50 | 66%                                                                                                                             `              |                    |     50|    Group2|   40| 10 | 88%                                                                     ---------------|--------------------|-------|----------|-----|----|---                                                               company2       |                 100|    100|    Group3|   75| 25 | 75%                                                                              ---------------|--------------------|-------|----------|-----|----|---                                                               Total          |                 300|    250|          |  215| 85 | 86%

Where the percentage column is the value in Yes/Count. I have tried addcoltotals but because some columns are multi-valued columns it doesnt work. Then I tried mvexpand but it splits the columns in a strange way that i dont know how to handle.

0 Karma
Highlighted

Re: AddColTotals & Percentage Column To Listed values

Splunk Employee
Splunk Employee

Very interesting question, and not a simple one to solve. To divide multivalve events, you will need to:
- use mvzip to combine the values per line
- use mvexpand to separate them into separate events
- use eval to perform division
- use mvcombine to put it all back together

This answer has 90% of that laid out for you - https://answers.splunk.com/answers/25653/mvexpand-multiple-multi-value-fields.html

You just have to put it back together on your own ;). But that should be fairly straight forward following the doc for it.

View solution in original post

Highlighted

Re: AddColTotals & Percentage Column To Listed values

Path Finder

You are a Splunk God! Thank you so much!

0 Karma