Hi @Cipher44,
Thanks for reaching out to the Microsoft fabric community forum.
It looks like you want to replicate the "Omnichannel Real-time Insights" report that's available in the Customer Service Hub using Power BI. Replicating the "Omnichannel Real-time Insights" report isn’t straightforward since Microsoft doesn’t expose the exact logic or make the report downloadable. The core challenge is that the built-in report provided in the Customer Service Hub cannot be downloaded or directly inspected, which makes it difficult for the you to understand how it works internally.
But you are on the right track by exploring msdyn_ocsession and msdyn_ocliveworkitem, as those are core to the experience.
Here’s what I can share based on what I’ve seen:
* Session Timeouts & Rejected Sessions:
There isn’t a single flag or field that directly says “this session timed out,” but you can derive it. Check the msdyn_endreason field in msdyn_ocsession. Specific status codes often represent timeout or rejection scenarios. For example 260650001 might indicate “Timed Out” or 260650002 might represent “Rejected”
Make sure to cross-reference with msdyn_ocliveworkitem as some terminations are logged there too.
* Transfers typically show up when the same live work item is associated with multiple sessions (or multiple agents). You can look for the same msdyn_ocliveworkitemid linked to multiple msdyn_ocsession records, and infer a transfer when there’s a change in agent (msdyn_agentid) across sessions.
* Average Call Time / Session Duration:
Use the createdon and msdyn_endtime fields from the msdyn_ocsession table. A simple DATEDIFF in Power BI (in seconds or minutes) will give you the call duration. Average it by agent to get their average call time.
* Agent Status and Hours Available:
These aren’t in the same tables. For current status, check msdyn_presence and msdyn_agentstatushistory. Presence gives near real-time info, while status history is better for reporting hours in a state like “Available.” You may need to aggregate time durations where the status was set to “Available” (based on timestamp diffs).
* Tables you might be missing like "msdyn_agentstatus" for custom statuses like “Break”, “Busy”, etc or "msdyn_ocagentstatuslog" which logs agent status changes. "systemuser" for agent names and mapping to their sessions and "msdyn_ocliveworkitemparticipant" which useful to track agent hand-offs or multiple participants
I’d recommend capturing a few sessions in test and tracing them end-to-end across these tables, it really helps to see how the data flows.
I would also take a moment to thank @pankajnamekar25, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support Team
If this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
