Hello, Splunkers!
Need help in finding the alternative to the append command.
I have a data with 8 fields [say A,B,C,D,E,F,G,H] in one index, Out of 8 fields in which 6 fields have the same field values
say [A=High, A=low, A=medium],[B=High, B=Low, B=medium].etc ,remaining 2 fields have the value of [true and false]. I need to count the field values with respect to the field.
I achieved this using append, but it is taking too much time due to large data sets. Looking for an alternative solution.
below is the sample query.
index=Test
| eval 1="A"
chart count over 1 by "A"
append[index=Test
| eval 1="B"
chart count over 1 by "B"]
append[index=Test
| eval 1="C"
chart count over 1 by "C"]
.......
The output should be like this
Field | High | Low | Medium | TRUE | FALSE |
A | 10 | 3 | 2 | ||
B | 8 | 4 | 3 | ||
C | 8 | 7 | 0 | ||
D | 8 | 0 | 7 | ||
E | 8 | 3 | 4 | ||
F | 9 | 2 | 3 | ||
G | 5 | 10 | |||
H | 7 | 8 |
Run-anywhere example with nummerical values. For production use adjust the digit-based evals to your true/false and high/mid/low values.
| makeresults count=1000
| eval A=random()%3
| eval B=random()%3
| eval D=random()%3
| eval C=random()%2+3
| eval E=random()%3
| eval F=random()%3
| eval G=random()%3
| eval H=random()%2+3
| foreach *
[ eval <<FIELD>>_1=if(<<FIELD>>=1,1,0)
| eval <<FIELD>>_2=if(<<FIELD>>=2,1,0)
| eval <<FIELD>>_0=if(<<FIELD>>=0,1,0)
| eval <<FIELD>>_3=if(<<FIELD>>=3,1,0)
| eval <<FIELD>>_4=if(<<FIELD>>=4,1,0)]
| stats sum(*_*) as *_*
| transpose
| rex field=column "(?<field>.*)_(?<value>.*)"
| fields - column
| xyseries field value "row 1"
Thank you! it worked, What about if we want to calculate the percentage by cell like High/A,Low/A etc? @PickleRick
Probably easiest way would be to use addtotals to sum all values and then just calculate ratio of given value vs the calculated total.
Try something like this
| eval {A}_A="A"
| eval {B}_B="B"
| eval {C}_C="C"
| eval {D}_D="D"
| eval {E}_E="E"
| eval {F}_F="F"
| eval {G}_G="G"
| eval {H}_H="H"
| stats list(*_*) as *_*
| foreach *_*
[| eval <<FIELD>>=mvcount(<<FIELD>>)]
| eval field=split("ABCDEFGH","")
| mvexpand field
| foreach *_*
[| eval <<FIELD>>=if(field="<<MATCHSEG2>>",<<FIELD>>,null())]
| foreach *_*
[| eval <<MATCHSEG1>>=if(isnotnull(<<FIELD>>),<<FIELD>>,<<MATCHSEG1>>)]
| fields - *_*
| table field high low medium true false