Export Teams Call Quality Dashboard to SQL server

%3CLINGO-SUB%20id%3D%22lingo-sub-3415250%22%20slang%3D%22en-US%22%3EExport%20Teams%20Call%20Quality%20Dashboard%20to%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3415250%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EDoes%20anybody%20knows%20how%20to%20export%20the%20CQD%20data%20to%20SQL%20server%3F%20I%20would%20like%20to%20take%20some%20statistics%20and%20keep%20them%20longer%20time%20than%20what%20Office%20365%20is%20offering%20by%20default.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3415250%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdministrator%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMicrosoft%20Teams%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3418673%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Teams%20Call%20Quality%20Dashboard%20to%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3418673%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F90197%22%20target%3D%22_blank%22%3E%40Petri%20X%3C%2FA%3E-Could%20you%20please%20elaborate%20your%20requirement%20and%20share%20the%20repro%20steps%2C%20so%20that%20we%20can%20try%20it%20from%20our%20end.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3420892%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Teams%20Call%20Quality%20Dashboard%20to%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3420892%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1202096%22%20target%3D%22_blank%22%3E%40Sayali-MSFT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20Teams%20keeps%20the%20current%20CDRs%20only%2030%20days%20available.%20If%20you%20want%20to%20create%20any%20kind%20of%20statistics%20which%20are%20older%20than%20that%2C%20e.g.%20see%20PSTN%20calls%2C%20Queue%20reports%2C%20from%20last%20months%2C%20you%20should%20have%20access%20to%20that%20data%20longer%20then%2030%20days%20(e.g.%20for%20365%20days).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20idea%20behind%20this%20was%2C%20to%20export%20available%20data%20from%20O365%20and%20import%20that%20into%20e.g.%20SQL%20database.%20And%20then%20having%20e.g.%20Power%20BI%20templates%20for%20users.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20also%20solves%20even%20the%20bigger%20issue%2C%20to%20be%20able%20to%20read%20those%20CDRs%20from%20O365%20you%20need%20to%20have%20quite%20much%20access%20to%20it%20which%20we%20cannot%20give%20for%20regular%20users.%20Having%20separated%20SQL%20DB%20we%20could%20segmenting%20what%20end%20users%20are%20able%20to%20see%20from%20their%20own%20calls.%20And%20that%20they%20can%20do%20using%20their%20own%20credentials.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20Power%20BI%20we%20are%20able%20to%20use%20%22Microsoft%20Call%20Quality%22%20as%20a%20source%2C%20so%20I%20though%20using%20similar%20method.%20I%20could%20perhaps%20setup%20a%20process%20for%20doing%20the%20same%20in%20automatically%2C%20and%20importing%20the%20data%20into%20SQL.%20But%20before%20that%20I%20thought%20to%20ask%20from%20here%2C%20if%20anybody%20else%20has%20already%20been%20working%20with%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3426411%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Teams%20Call%20Quality%20Dashboard%20to%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3426411%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F90197%22%20target%3D%22_blank%22%3E%40Petri%20X%3C%2FA%3E%26nbsp%3B-Please%20have%20a%20look%20into%20this%20doc-%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fmicrosoftteams%2Fcqd-data-and-reports%23create-custom-detailed-reports%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EData%20and%20reports%20in%20Call%20Quality%20Dashboard%20(CQD)%20-%20Microsoft%20Teams%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3438011%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Teams%20Call%20Quality%20Dashboard%20to%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3438011%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F90197%22%20target%3D%22_blank%22%3E%40Petri%20X%3C%2FA%3E%20-Did%20you%20get%20chance%20to%20check%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3438342%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Teams%20Call%20Quality%20Dashboard%20to%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3438342%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1202096%22%20target%3D%22_blank%22%3E%40Sayali-MSFT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20I%20did%2C%20but%20I%20was%20not%20sure%20how%20that%20should%20help%3F%20CQD%20user%20interface%20still%20requires%20extra%20credentials%20for%20viewers%2C%20and%20CQD%20does%20not%20have%20past%20year%20details.%3C%2FP%3E%3CP%3EIf%20I%20success%20to%20do%20using%20e.g.%20PS%20script%20which%20collects%20the%20same%20data%20what%20Power%20BI%20does%2C%20then%20I%20could%20import%20that%20into%20SQL.%20Then%20I%20would%20be%20a%20bit%20closer%20to%20goal%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3440957%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Teams%20Call%20Quality%20Dashboard%20to%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3440957%22%20slang%3D%22en-US%22%3Elike%20your%20idea%2C%20hope%20you%20can%20find%20about%20a%20way%3CBR%20%2F%3EI%20leave%20the%20word%20here%20to%20let%20you%20know%20more%20people%20want%20this%3CBR%20%2F%3Eeven%20I%20don't%20know%20how%20to%20build%20this%20local%20database%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3444295%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Teams%20Call%20Quality%20Dashboard%20to%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3444295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F90197%22%20target%3D%22_blank%22%3E%40Petri%20X%3C%2FA%3E%20-Here%20is%20document%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fmicrosoftteams%2Fcqd-power-bi-query-templates%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EUse%20Power%20BI%20to%20analyze%20CQD%20data%20for%20Microsoft%20Teams%20-%20Microsoft%20Teams%20%7C%20Microsoft%20Docs%3C%2FA%3E%3CBR%20%2F%3Ehope%20it%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3444373%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Teams%20Call%20Quality%20Dashboard%20to%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3444373%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20to%20say%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1202096%22%20target%3D%22_blank%22%3E%40Sayali-MSFT%3C%2FA%3E%2C%26nbsp%3Bbut%20that%20is%20still%20not%20help%20to%20reach%20the%20goal%20where%20I%20could%20do%20the%20same%20by%20scripting%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20believe%20the%20answer%20is%2C%20nobody%20has%20done%20this%20earlier%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20far%20as%20I%20have%20seen%2C%20I%20should%20authenticate%20myself%2C%20and%20then%20send%20the%20query%20in%20JSON%20format.%20But%20obviously%20this%20requires%20more%20investigation%20%3A)%3C%2Fimg%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3445340%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Teams%20Call%20Quality%20Dashboard%20to%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3445340%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20actually%20one%20finding%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fpsg-prod-centralus.cloudapp.net%2Fpackages%2FCQDPowerShell%2F2.0.1%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ECQD%20PowerShell%202.0.1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%2C%20that%20is%20pretty%20old%20already%2C%20so%20not%20sure%20if%20this%20is%20active%20scenario%20anymore%2C%20or%20is%20the%20CQD%20actually%20not%20developed%20anymore.%20But%20this%20is%20till%20pretty%20interesting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Super Contributor

Hi,

Does anybody knows how to export the CQD data to SQL server? I would like to take some statistics and keep them longer time than what Office 365 is offering by default.

9 Replies
@Petri X-Could you please elaborate your requirement and share the repro steps, so that we can try it from our end.

@Sayali-MSFT 

Basically, Teams keeps the current CDRs only 30 days available. If you want to create any kind of statistics which are older than that, e.g. see PSTN calls, Queue reports, from last months, you should have access to that data longer then 30 days (e.g. for 365 days).

 

So the idea behind this was, to export available data from O365 and import that into e.g. SQL database. And then having e.g. Power BI templates for users.

 

This also solves even the bigger issue, to be able to read those CDRs from O365 you need to have quite much access to it which we cannot give for regular users. Having separated SQL DB we could segmenting what end users are able to see from their own calls. And that they can do using their own credentials.

 

On the Power BI we are able to use "Microsoft Call Quality" as a source, so I though using similar method. I could perhaps setup a process for doing the same in automatically, and importing the data into SQL. But before that I thought to ask from here, if anybody else has already been working with this.

@Petri X -Did you get chance to check?

@Sayali-MSFT 

Yes I did, but I was not sure how that should help? CQD user interface still requires extra credentials for viewers, and CQD does not have past year details.

If I success to do using e.g. PS script which collects the same data what Power BI does, then I could import that into SQL. Then I would be a bit closer to goal :)

 

like your idea, hope you can find about a way
I leave the word here to let you know more people want this
even I don't know how to build this local database

Sorry to say @Sayali-MSFT, but that is still not help to reach the goal where I could do the same by scripting this.

 

But I believe the answer is, nobody has done this earlier :)

 

As far as I have seen, I should authenticate myself, and then send the query in JSON format. But obviously this requires more investigation :) 

This is actually one finding:

CQD PowerShell 2.0.1 

The problem is, that is pretty old already, so not sure if this is active scenario anymore, or is the CQD actually not developed anymore. But this is till pretty interesting.