Splunk Search

Is there an alternative to append?

Vikasreddys
Engager

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

 

PickleRick
Ultra Champion

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"

 

vikasreddy
Explorer

Thank you! it worked, What about if we want to calculate the percentage by cell like High/A,Low/A etc? @PickleRick 

0 Karma

PickleRick
Ultra Champion

Probably easiest way would be to use addtotals to sum all values and then just calculate ratio of given value vs the calculated total.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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