Splunk Search

Can you use transpose to eval fields for column totals?

dcroteau
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)
1 Solution

somesoni2
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

Jason
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

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

somesoni2
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

dcroteau
Splunk Employee
Splunk Employee

Nice somesoni2 !!! Winner Winner Turkey Dinner.

skawasaki_splun
Splunk Employee
Splunk Employee
0 Karma

dcroteau
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...