SQL Query for real time resource stats

[michael mack]

Does anyone know what the query would be to duplicate the real time resource stats page in version 8 or above of UCCX?  I need to build a custom web page that displays the same information.  I've got all the connectivity to the database just fine and can query it and display information.  Just can't figure out what the SQL would be that would extract that information.

[jeroen1964]

 Michael,

you can try :

SELECT x.resourceName, t.eventType, x.datetime FROM (SELECT t1.resourceID, t1.resourceName, MAX(t2.eventDateTime) AS datetime FROM Resource AS t1 INNER JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID GROUP BY t1.resourceID, t1.resourceName ) AS x INNER JOIN AgentStateDetail AS t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime ORDER BY x.resourceName

You will need to translate the eventType into the "readable" status like Talking or Not Ready with an IF or Case statement.

Regards,

Jeroen

[michael mack]

Unfortunately this query only produces the current agent state.  What I was hoping for was to display the actual agent statistics for the day, like the real time reports do.  The number of calls handled, average talk time, average hold time, that stuff.  The real time reports tool gets that information.  I suspect it might be one of the stored procedures, but I don't know.