Splunk Search

Is there an alternative to the Appendcol command?

VI371887
Path Finder

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?

0 Karma
1 Solution

harishalipaka
Motivator

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
Harish

View solution in original post

knielsen
Contributor

I'd actually avoid append here.

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

should be faster...

VI371887
Path Finder

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

0 Karma

harishalipaka
Motivator

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
Harish

VI371887
Path Finder

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

0 Karma

harishalipaka
Motivator

It will adjust the values based on Function field.

Thanks
Harish
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...