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