SQL query to find latest login dates of Agents

[rmanakadav]

Hi All ,Can anyone help to get below details

SQL Query to find agent name ,extension used and latest login date for all agents in ucce. looking for a single entry for each agent.

thanks,

Rohit

[bill.king1]

The standard Cisco CUIC reports like agent attendance or agent login/logout will give you this. If you want to modify them to just get the last entry you can look or the SQL, or just export these as is and then sort/filter in Excel.

Here's information about them.
https://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/icm_enterprise/icm_enterprise_12_0_1/User/Guide/ucce_b_unified-cce-reporting-user_1201/ucce_b_cisco-unified-contact-center-enterprise_1201_chapter_01100.html#UCCE_RF_AB9B...

[Maren Mahoney]

@Carlo Poggiarelli - You helped me with some awesome SQL queries. Might you have advice for this poster?

Maren

[Carlo Poggiarelli]

Hi @Maren Mahoney 

Sure I can.. I was all the day at customer’s premise

let me prepare it for the user and i’ll get back soon.

Thanks for considering me 

Cheers 

Carlo

[Thomas G. Johannesen · Accepted solution]

Hey

I think something like this :

SELECT p.PersonID as pId, p.FirstName, p.LastName, MAX(e.EventDateTimeUTC) as LoginDatetime, MAX(e.AgentDialedNumber ) as Extension
FROM awdb.dbo.t_Person p, [awdb].[dbo].[t_Agent] a, [hds].[dbo].[t_Agent_Event_Detail] e 
WHERE a.SkillTargetID = e.SkillTargetID ANd e.Event = 1 and a.PersonID = p.PersonID
GROUP BY p.PersonID , p.FirstName, p.LastName
ORDER BY LoginDatetime DESc

Remember to change to DB names in the where statetement

[Carlo Poggiarelli]

Hi Rohit,

Sorry I totally missed to answer you but @Thomas G. Johannesen  did it correctly.

Thanks again

Regards

Carlo

[rmanakadav]

Thanks All @Carlo Poggiarelli @Thomas G. Johannesen @Maren Mahoney @bill.king1