Need help!!!
I am intending to make a table with the country wise sum(percent90). If i do the below, it will just sum percent90 per FUNCTION
index=int source="*" FUNCTION=* | stats sum(PERCENT90) by FUNCTION
So I tried using append which...
index=int source="HK" FUNCTION=* | stats sum(PERCENT90) as HK by FUNCTION |
append [search index=int source="SG" FUNCTION=* | stats sum(PERCENT90) as SG by FUNCTION]
...would give the below output
FUNCTION | HK | SG
AGE |107.773 |
CLT |49.206 |
COM | 7.497 |
RIO |56.803 |
AGE | | 120.644
CLT | | 37.6
COM | | 61.778
CONSULT | | 10.115
What I am looking for is as shown below, no repetition of Function name below.
FUNCTION | HK | SG
AGE |107.773 | 120.644
CLT |49.206 | 37.6
COM | 7.497 | 61.778
RIO |56.803 | 0
CONSULT | 0 | 10.115
So i tried the appendcol command, but it messes up the data, like in the above example, if Function Consult and RIO don't have a value for any country, it would show "0 " instead Appendcol with below query does..
index=int source="HK" FUNCTION=* | stats sum(PERCENT90) as HK by FUNCTION |
appendcols [search index=int source="SG" FUNCTION=* | stats sum(PERCENT90) as SG by FUNCTION]
FUNCTION | HK | SG
AGE |107.773 | 120.644
CLT |49.206 | 37.6
COM | 7.497 | 61.778
RIO |56.803 | 10.115
CONSULT | |
It fills the details from Consult in RIO for SG which is wrong.
What can be done here?
hi @VI371887
try like this with your second approach
index=int source="HK" FUNCTION=* | stats sum(PERCENT90) as HK by FUNCTION |
append [search index=int source="SG" FUNCTION=* | stats sum(PERCENT90) as SG by FUNCTION] |stats values(*) as * by FUNCTION
I'd actually avoid append here.
index=int source="HK" OR source="SG" FUNCTION=* | chart sum(PERCENT90) over FUNCTION by source
should be faster...
Thanks!! this makes it more minimal and easy to migrate.
hi @VI371887
try like this with your second approach
index=int source="HK" FUNCTION=* | stats sum(PERCENT90) as HK by FUNCTION |
append [search index=int source="SG" FUNCTION=* | stats sum(PERCENT90) as SG by FUNCTION] |stats values(*) as * by FUNCTION
Thanks!! It worked but can you help me understand what it is doing ?
It will adjust the values based on Function field.