Splunk Search
Highlighted

How to align values in a column with values from another column?

Motivator

I basically have 2 searches that I am combining using appendcols. 1 search is for each element. It looks something like:

index=core ... ne=ne1 | stats sum(kpi1) as "kpi1" by ks_countryname | sort - "kpi1" |
appendcols [search index=core ... ne=ne2 | stats sum(kpi1) as "kpi1_ne2" by ks_countryname | rename ks_countryname as  ks_countryname_ne2| sort - "kpi1_ne2"]

the output looks something like below:

ks_countryname  kpi1  kpi1_ne2  ks_countryname_ne2
ZZ_undefined    1615    2631       ZZ_undefined
Australia      1500 1635           China
United States   676 1600         Australia
China           423  410    United States
Vanuatu       295    305           Samoa
Switzerland   220    247        Switzerland
Germany       165    213          Germany
France         157   181        France
Samoa           118   62          Vanuatu

How do I get column 3 and 4 to line up with column 1 and 2 such that:
row 2 of column 1 and 2 Australia 1500 would line up with row 3 of column 3 and 4 1600 Australia

Ideally I would still like to sort max to min by kpi1, but I just want the countries aligned also.

0 Karma
Highlighted

Re: How to align values in a column with values from another column?

Esteemed Legend

Do it like this:

index=core ... ne=ne1 | stats sum(kpi1) as "kpi1" by ks_countryname | append [search index=core ... ne=ne2 | stats sum(kpi1) as "kpi1_ne2" by ks_countryname ] | stats values(*) AS * by ks_countryname 

View solution in original post

0 Karma
Highlighted

Re: How to align values in a column with values from another column?

Motivator

don't think this wrks. they don't seem to be lining up correctly. Also what if kscountryname and kscountryname_ne2 have different list of names?

0 Karma
Highlighted

Re: How to align values in a column with values from another column?

Esteemed Legend

Actually, we don't need to use append at all and this should be even better and clearer (to give you more confidence that it is doing what you need it to do):

index=core (... ne=ne1) OR (... ne=ne2) | stats sum(eval(ne=ne1,kpi1,null())) AS "kpi1" sum(eval(ne=ne2,kpi1,null())) AS "kpi1_ne2" BY ks_countryname
0 Karma
Highlighted

Re: How to align values in a column with values from another column?

Motivator

sum(eval(ne=ne1,kpi1,null())) I am getting an error with this Error in 'stats' command: The eval expression for dynamic field 'eval(ne=ne1,kpi1,null())' is invalid. Error='The operator at ',kpi1,null()' is invalid.'

0 Karma
Highlighted

Re: How to align values in a column with values from another column?

Esteemed Legend

I forgot the ifs; try this version:

index=core (... ne=ne1) OR (... ne=ne2) | stats sum(eval(if((ne==ne1),kpi1,null())) AS "kpi1" sum(eval(if(ne==ne2),kpi1,null())) AS "kpi1_ne2" BY ks_countryname
Highlighted

Re: How to align values in a column with values from another column?

Motivator

tks I should have copped that, I think thats the one 🙂 Note i had to use double quotes around ne1 sum(eval(if((ne=="ne1"),kpi1,null()))

0 Karma
Highlighted

Re: How to align values in a column with values from another column?

Esteemed Legend

Then you need double-quotes in my solution, too. Does it work now?

0 Karma
Highlighted

Re: How to align values in a column with values from another column?

Motivator

yep thats the job, tks.

0 Karma