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

[maridelfi]

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

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" ]

[maridelfi]

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

[somesoni2 · Accepted solution]

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