Splunk Search
Highlighted

Can you use transpose to eval fields for column totals?

Splunk Employee
Splunk Employee

Please help!

Using transpose in my search so that each row becomes a column. Then I'd like to count the number of characters in column r1 + the number or characters in r2 using len. This will give my customer an approximate size of a cookie (if you must know).

This is a simplified example, some columns may have more than 2 rows.

Is it possible to do this with transpose?

index="hslog" |table Ck_\* |transpose |rename row* AS r* |eval bytes=len(r1+r2)

alt text

Tags (3)
Highlighted

Re: Can you use transpose to eval fields for column totals?

Splunk Employee
Splunk Employee
0 Karma
Highlighted

Re: Can you use transpose to eval fields for column totals?

Splunk Employee
Splunk Employee

Nice effort, no luck with transpose:

index="hslog" |table Ck_* |transpose |rename row* AS r* | eval bytes=len(r1)+len(r2) |fields bytes
0 Karma
Highlighted

Re: Can you use transpose to eval fields for column totals?

SplunkTrust
SplunkTrust

Try something like this (should be able to handle any number of rows).

index="hslog" |table Ck_* |transpose |rename "row *" AS r* 
|eval bytes=0 | foreach r* [eval bytes=bytes+coalesce(len('<<FIELD>>'),0)]

View solution in original post

Highlighted

Re: Can you use transpose to eval fields for column totals?

SplunkTrust
SplunkTrust

Also note that after transpose, the field name will be "row N" with a space, so there is a change in rename command as well from your search in the question.

0 Karma
Highlighted

Re: Can you use transpose to eval fields for column totals?

Splunk Employee
Splunk Employee

Nice somesoni2 !!! Winner Winner Turkey Dinner.

Highlighted

Re: Can you use transpose to eval fields for column totals?

Motivator

Why are you using transpose? (It's implemented in python and not the fastest search command.)

If the question is "what is the average length of each cookie throughout my events, by cookie name?" then foreach is enough, assuming they all start with ck_*:

| foreach ck_* [eval len_<<FIELD>>=len(<<FIELD>>)] 
| stats avg(len_ck_*) 

Full search example:

| stats count 
| fields - count 
| eval ck_1="abc", ck_2="bcdef", ck_3="g" 
| eval event="e1|e2" 
| makemv delim="|" event 
| mvexpand event 
| eval ck_3=if(event=="e2", "ghi", ck_3) 
| foreach ck_* [eval len_<<FIELD>>=len(<<FIELD>>)] 
| stats avg(len_ck_*) 


ck_1    ck_2    ck_3        event
abc     bcdef   g       e1
abc     bcdef   ghi     e2 


avg(len_ck_1)   avg(len_ck_2)   avg(len_ck_3)   
3.000000        5.000000        2.000000 
0 Karma