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

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

HattrickNZ — Sun, 16 Aug 2015 23:25:44 GMT

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.

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

woodcock — Mon, 17 Aug 2015 16:24:37 GMT

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

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

HattrickNZ — Tue, 29 Sep 2020 07:01:36 GMT

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

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

woodcock — Mon, 17 Aug 2015 20:56:59 GMT

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

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

HattrickNZ — Mon, 17 Aug 2015 21:16:45 GMT

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.'

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

woodcock — Mon, 17 Aug 2015 21:34:26 GMT

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

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

HattrickNZ — Mon, 17 Aug 2015 22:04:24 GMT

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

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

woodcock — Mon, 17 Aug 2015 22:28:05 GMT

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

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

HattrickNZ — Mon, 17 Aug 2015 22:43:52 GMT

yep thats the job, tks.