topic Re: combine 2 queried and combine results in Splunk Search

combine 2 queried and combine results

fmatera — Tue, 29 Sep 2020 23:54:35 GMT

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

Re: combine 2 queried and combine results

cmerriman — Fri, 29 Mar 2019 17:04:44 GMT

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.

Re: combine 2 queried and combine results

fmatera — Fri, 29 Mar 2019 18:01:43 GMT

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.

Re: combine 2 queried and combine results

fmatera — Tue, 29 Sep 2020 23:50:29 GMT

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