Splunk Search

appendcols - How can I match the same row in a query?

maridelfi
Explorer

Hi All
I have a query that join two searches
I need to complete the information from the second query in the same row that the first
alt text

How can I do?

This is the query

index=aa source=aa_bb sourcetype=test C
| dedup QUEUE_CITY QUEUE_NUMBER
|stats sum(PNR_COUNT) as "Total of PNRs on Desk" sum(AGENT_COUNT) as "Total of Agents on Desk" by DESK
| replace "0" WITH " " IN "Total of PNRs on Desk" "Total of Agents on Desk"
| rename DESK as Desk
| appendcols [search sourcetype=pnr_by_queue | dedup QUEUE_CITY QUEUE_NUMBER|stats sum(PNRS3DAY) sum(PNRS4TO6DAYS) sum(PNRS7TO10DAYS) sum(PNRS11DAYS) by DESK | rename sum(PNRS3DAY) as "PNRs Departing in 3 Days", sum(PNRS4TO6DAYS) as "PNRs Departing between 4 to 6 Days" sum(PNRS7TO10DAYS) as "PNRs Departing between 7 to 10 days" sum(PNRS11DAYS) as "PNRs Departing in 11 Days or more" | table DESK "PNRs Departing in 3 Days" "PNRs Departing between 4 to 6 Days" "PNRs Departing between 7 to 10 days" "PNRs Departing in 11 Days or more" ]

0 Karma
1 Solution

somesoni2
Revered Legend

Try like this (appendcols just joins two result set side by side, it doesn't do any match. Join command does that but it's resource intensive, so try this join alternative command)

index=aa source=aa_bb sourcetype=test C
| dedup QUEUE_CITY QUEUE_NUMBER
|stats sum(PNR_COUNT) as "Total of PNRs on Desk" sum(AGENT_COUNT) as "Total of Agents on Desk" by DESK 
| replace "0" WITH " " IN "Total of PNRs on Desk" "Total of Agents on Desk" 

| append [search sourcetype=pnr_by_queue | dedup QUEUE_CITY QUEUE_NUMBER|stats sum(PNRS3DAY) sum(PNRS4TO6DAYS) sum(PNRS7TO10DAYS) sum(PNRS11DAYS) by DESK | rename sum(PNRS3DAY) as "PNRs Departing in 3 Days", sum(PNRS4TO6DAYS) as "PNRs Departing between 4 to 6 Days" sum(PNRS7TO10DAYS) as "PNRs Departing between 7 to 10 days" sum(PNRS11DAYS) as "PNRs Departing in 11 Days or more" | table DESK "PNRs Departing in 3 Days" "PNRs Departing between 4 to 6 Days" "PNRs Departing between 7 to 10 days" "PNRs Departing in 11 Days or more"   ] 
| stats values(*) as * by DESK
| rename DESK as Desk

View solution in original post

0 Karma

maridelfi
Explorer

Perfect!, This works as I wanted 🙂 Thanks for your time

0 Karma

somesoni2
Revered Legend

Try like this (appendcols just joins two result set side by side, it doesn't do any match. Join command does that but it's resource intensive, so try this join alternative command)

index=aa source=aa_bb sourcetype=test C
| dedup QUEUE_CITY QUEUE_NUMBER
|stats sum(PNR_COUNT) as "Total of PNRs on Desk" sum(AGENT_COUNT) as "Total of Agents on Desk" by DESK 
| replace "0" WITH " " IN "Total of PNRs on Desk" "Total of Agents on Desk" 

| append [search sourcetype=pnr_by_queue | dedup QUEUE_CITY QUEUE_NUMBER|stats sum(PNRS3DAY) sum(PNRS4TO6DAYS) sum(PNRS7TO10DAYS) sum(PNRS11DAYS) by DESK | rename sum(PNRS3DAY) as "PNRs Departing in 3 Days", sum(PNRS4TO6DAYS) as "PNRs Departing between 4 to 6 Days" sum(PNRS7TO10DAYS) as "PNRs Departing between 7 to 10 days" sum(PNRS11DAYS) as "PNRs Departing in 11 Days or more" | table DESK "PNRs Departing in 3 Days" "PNRs Departing between 4 to 6 Days" "PNRs Departing between 7 to 10 days" "PNRs Departing in 11 Days or more"   ] 
| stats values(*) as * by DESK
| rename DESK as Desk
0 Karma
Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...