Splunk Search

How edit my search so that appendcols command appends results correctly?

cchange
Path Finder

Hi,

I'm trying to append the results from two tables. I used appendcols with override option. But results showing different in final output.

I used following query

Search query 1 | appendcols override=true [Search query2]

First Search Results

Team FM    
A         35    
B         81    
C          30    

Second sub Search Results

Team  SM
D       40
E       620
A   180
B        59
C           53

Currently results are showing

Team FM    SM
D   35  40
E   81  620
A   30  180
B   -      59
C   -    53

Results should show in below format. I'm not sure if this is a bug with appendcols option. Any inputs or help is appreciated.

Team FM    SM
D    -  40
E   -   620
A   35   180
B   81  59
C   30   53

thanks

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

The appendcols appends the result of two searches, row/result by result (row 1 of search 1 with row 1 of search 2 and so on). The override field defines if same field name is present in both the results, which value to keep (keep from search 1 or overwrite from search 2). I see that results from your both the searches doesn't have same row count and they are not sorted either, making appendcols not the ideal command for your need. What you need is to merge both results and keep values from second search, then you should use combination of append and stats like this.

To keep results from search 1

Search query 1 | append [Search query2] | stats list(FM) as FM list(SM) as SM by Team | eval FM=mvindex(FM,0)  | eval SM=mvindex(SM,0)

To keep results from search 2

Search query 1 | append [Search query2] | stats list(FM) as FM list(SM) as SM by Team | eval FM=mvindex(FM,-1)  | eval SM=mvindex(SM,-1)

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

The appendcols appends the result of two searches, row/result by result (row 1 of search 1 with row 1 of search 2 and so on). The override field defines if same field name is present in both the results, which value to keep (keep from search 1 or overwrite from search 2). I see that results from your both the searches doesn't have same row count and they are not sorted either, making appendcols not the ideal command for your need. What you need is to merge both results and keep values from second search, then you should use combination of append and stats like this.

To keep results from search 1

Search query 1 | append [Search query2] | stats list(FM) as FM list(SM) as SM by Team | eval FM=mvindex(FM,0)  | eval SM=mvindex(SM,0)

To keep results from search 2

Search query 1 | append [Search query2] | stats list(FM) as FM list(SM) as SM by Team | eval FM=mvindex(FM,-1)  | eval SM=mvindex(SM,-1)
0 Karma

cchange
Path Finder

Thanks. It worked.

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