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.
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
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
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?
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
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.'
I forgot the if
s; 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
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()))
Then you need double-quotes in my solution, too. Does it work now?
yep thats the job, tks.