Hi All,
About this post, so I was tasked with trying to create a powerbi dashboard that can look at our direct routed telephone logs from Teams and produce a dashboard that can show us things like number of calls / minutes, international and premium rate calling, and most importantly, cost centre information.
When looking at the odata reports, they were enumerated and lacked the kind of coherent information I needed in order to fulfil the task! I found this graph API call that got what I needed:
so got to work seeing if it could use this. I could, but it was slow and so I would need to take this data and put it somewhere I could access it better and manipulate it. I chose to use a sharepoint list. We make somewhere in the region of 50,000 calls a month, so 600,000 a year, we only need to keep for 3 years (+10%) makes roughly 2 million records, we wont be accessing this through sharepoint, only on PowerBI so I think I can live with that! If it was higher than this, i would opt to use Azure table storage or something with a bit more meat, but they are more tricky to setup and use, so will stick with Sharepoint for now.
I created a list called TeamsCallingRecords and added these columns:
This will need to run an apply to each action with more than 5000 items, so I used a service account with a flow license (P2) in order to run the flow for me.
So I create a flow with a Recurrence, in my case we are happy with 24 hour updates, but we could go more frequently if we choose.
I create some variables, I like to use an object variable for http auth instead of 4 separate strings:
You will need to setup a graph endpoint in Azure, I wont cover that here, but there are instructions here:
https://docs.microsoft.com/en-us/graph/auth-register-app-v2
Now get some dates and times, I am getting yesterday to today:
I managed to put together an excel spreadsheet with country codes on it so I can do an international call lookup as the reports dont have one, I am just loading the contents of that file here:
Then I do my http call to the graph:
The Api call looks like this:
https://graph.microsoft.com/beta/communications/callRecords/getDirectRoutingCalls(fromDateTime=@{variables('fromDate')},toDateTime=@{variables('todate')})
With a few runs I was able to get the sample so I parse the JSON, I know we can do it without, but I find its just a bit easier sometimes
I have an apply to each to go through each record and I just refer back to the parse JSON method
The first step I do is just check that the record doesnt already exist in the list as there may be some overlap:
My condition looks at the result of the ID lookup has anything in it, I found the easiest approach was to just look at the array length:
I am a big fan of containers so added a scope to put everything in once we get a yes, the contents of the scope look like this:
We have a service account that operates in the system so I have to do a lookup to see if the account needs to be added manually to the record:
You may not need to do this
Some of the callee numbers are coming in blank, im not sure why, but I am checking for this and removing the + from the start of every number, I dont need it for my report.
Now I start looking up the international codes:
The list of codes is in order of code length so if I dial 442380, when it goes through the list it will filter and get back:
44, United Kingdom
44238, UK Southampton
(Sort of),
I then just pick the last one in the list as it will be the most relevant:
For some reason, the failure date time and the invite datetime actually come back with the date as 0001-01-01, in this case I dont want to record any dates as this will mess with the report later:
Then finally I can save to my sharepoint list, just filling in all the columns I want. I have replicated all the columns from the graph output as we may need some of it later:
And finally I added a blank scope at the bottom as I was getting a no dependent action error, I thin think that Flow doesnt like to end inside a condition or something!
