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" ]
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
Perfect!, This works as I wanted 🙂 Thanks for your time
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