Analyzing PSTN usage reports in Teams Admin Center is a good start to troubleshoot issues with Direct Routing calls in Teams. You can either view the report directly in the browser or export it into a CSV file.

To access this report, head over to https://admin.teams.microsoft.com/ and click Usage reports under Analytics & Reports. Next, choose PSTN and SMS (preview) usage and select a date range. Finally, click on Run report.

It will default to Calling plans/SMS so you need to switch the tab to Direct Routing.

Viewing the logs in the browser is good enough if you just need to take a quick look at some calls. For example, you can quickly identify issues if you sort by the column Failure time (UTC). But keep in mind that cancelled calls are also shown as errors, as these are terminated with the SIP error code 487 (busy here). A duration of zero is also an indicator for failed calls.

Besides obvious stuff like the display name, callee and caller numbers, the SIP address, or the call direction/type, you can also see which Direct Routing gateway and which Azure region for media or signaling was used.

If you need to take a deeper look at your call logs, it’s probably best to download the report as a CSV file. To do that, just click the Excel Icon in the upper right corner as shown in the first screenshot.

Once the report has been generated, it will trigger a download of a Zip file. Depending on your setup, the Zip file might also contain a CSV file for Calling Plans. I’m only interested in Direct Routing calls at the moment, so let’s open that file in Excel. It gives us all call records in the specified date range as comma separated values. But they are all in the same column, which is pretty useless until they’re formatted correctly.

For some reason, Excel always shows a warning, that there’s already data here, when I try to use the Text to Columns feature. Despite this warning, I’m still able to read the logs once the columns are separated.

Since I don’t want to take the risk of missing important information in the logs, I wrote this handy PowerShell one-liner to convert the CSV file into a more human readable CSV file. This also saves me the time to turn text into columns first.

Just fire up PowerShell or Windows Terminal by entering powershell or wt directly into the File Explorer’s address bar.

This will open PowerShell in the current directory. Now all you have to do is to paste this code.

This will read the original CSV, into a PowerShell object and export it to a new CSV file called “drExport.csv” where the columns are already separated.

I hope this little Gist also helps you formatting your logs a little quicker.