cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
578
Views
10
Helpful
6
Replies

SQL query to find latest login dates of Agents

rmanakadav
Beginner
Beginner

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

1 Accepted Solution

Accepted Solutions

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

Please rate helpful posts and if applicable mark "Accept as a Solution".
Thanks, Thomas G. J.

View solution in original post

6 Replies 6

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

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

Maren

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

Please rate all helpful posts "The more you help the more you learn"

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

Please rate helpful posts and if applicable mark "Accept as a Solution".
Thanks, Thomas G. J.

Hi Rohit,

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

 

Thanks again

 

Regards

 

Carlo

Please rate all helpful posts "The more you help the more you learn"
Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: