Can you use transpose to eval fields for column totals?

[dcroteau]

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)

[Jason]

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 

[somesoni2 · Accepted solution]

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)]

[somesoni2]

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.

[dcroteau]

Nice somesoni2 !!! Winner Winner Turkey Dinner.

[skawasaki_splun]

Isn't it just...

| eval bytes=len(r1)+len(r2)

?

https://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/CommonEvalFunctions

[dcroteau]

Nice effort, no luck with transpose:

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