topic Re: Is there an alternative to the Appendcol command? in Splunk Search

Is there an alternative to the Appendcol command?

VI371887 — Mon, 26 Nov 2018 07:56:09 GMT

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?

Re: Is there an alternative to the Appendcol command?

harishalipaka — Mon, 26 Nov 2018 08:06:03 GMT

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

Re: Is there an alternative to the Appendcol command?

VI371887 — Mon, 26 Nov 2018 08:56:49 GMT

Thanks!! It worked but can you help me understand what it is doing ?

Re: Is there an alternative to the Appendcol command?

knielsen — Mon, 26 Nov 2018 09:46:06 GMT

I'd actually avoid append here.

index=int  source="HK" OR source="SG" FUNCTION=* | chart sum(PERCENT90) over FUNCTION by source

should be faster...

Re: Is there an alternative to the Appendcol command?

harishalipaka — Mon, 26 Nov 2018 09:59:05 GMT

It will adjust the values based on Function field.

Re: Is there an alternative to the Appendcol command?

VI371887 — Thu, 29 Nov 2018 06:40:18 GMT

Thanks!! this makes it more minimal and easy to migrate.