Splunk Search

combine 2 queried and combine results

fmatera
Explorer

I have 2 good searches. One outputs:
Date Agent Answered Calls Average Talk Time Longest Talk Time Total Talk Time
By agent/client_name
The second has outbound minutes (OBMINS1) by client name.

What I would like to do is add a column OBMINS1 to the 1st table and add this result to Total Talk Time.

I tried using appendcols but can't seem the values to line up by client_name.

Here are the 2 queries that work:

base search
| addinfo | convert timeformat="%A, %m/%d/%Y" ctime(_time) AS Date
| dedup callid sortby disposion
| stats count(_raw) as Total, count(eval(disposion="ANSWERED")) as Answered, count(eval(disposion="NO ANSWER" OR disposion="BUSY" OR disposion="FAILED" OR disposion="NOT ALLOWED")) as "Abandoned Calls", count(eval(application="Voicemail")) as "Voicemail Calls" sum(Dur) AS Seconds, max(Dur) as Longest by Date, client_name
| eval ASR=round(Answered/Total*100,2)."%"
| eval Minutes=round(Seconds/60,0)
| eval OBMINS=round(OBSeconds/60,0)
| eval ALOC=round(Minutes/Answered,2)
| eval TotDur=tostring(Seconds, "duration")
| eval "Longest Talk Time"=tostring(Longest, "duration")
| rename client_name as Agent, TotDur as "Total Talk Time", ALOC as "Average Talk Time", Total as "Total Calls", Answered as "Answered Calls"
| table Date, Agent, "Answered Calls", "Average Talk Time", "Longest Talk Time", "Total Talk Time"
| addcoltotals "Answered Calls", labelfield=Date label=Totals

2nd search
base search
| stats count(_raw) as TotalOB, count(eval(disposion="ANSWERED")) as OBAnswered, count(eval(disposion="NO ANSWER" OR disposion="BUSY" OR disposion="FAILED" OR disposion="NOT ALLOWED")) as "OBAbandoned Calls", sum(Dur) AS OBSeconds by client_name | eval OBMINS1=tostring(OBSeconds, "duration")

Thanks in advance

Tags (1)
0 Karma

fmatera
Explorer

1st search

index=cdr flow=in client_client_id=1110 partyid=111000200 calltype=local OR calltype=out

2nd search

index=cdr client_client_id=1110 flow=out calltype=out

0 Karma

fmatera
Explorer

The searches share all the same fields, just different values. The 1st search returns events for inbound call records while the 2nd search returns outbound calls records. Trying to take those 2 values by client_name and total them up to get a total duration.

0 Karma

cmerriman
Super Champion

are the two queries using the same data in the base searches? same index? sourcetype? anything similar? Could be that we could avoid joins all together and do something with stats/eventstats type work. would help to know the base search.

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