none
QoE and link error to ip-address RRS feed

  • Question

  • Hi,

    On the Skype reporting database there is database: LcsCDR.dbo.SessionDetails. On that database there are two interesting columns: ResponseCode and DiagnosticId. But the challenge is to found certain codes based on IP addresses (subnets). Basically we can see one error code more often on one subnet. But those we could found only one by one. I would like to list all sessions on that subnet/ip-address where had that codes.

    The IP addresses are listed on QoEMetrics database, but then ResponceCode neither DiagnosticId are not available on QoEMetrics. So I should somehow found the way to link DB on LcsCDR database and database in QoEMetrics.

    By the Query:

    select TABLE_CATALOG, TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS

    I could list all columns from one database, including views. But even so, I could not found any column used from both databases.

    So the question is: what column does the trick to marry these databases together? :)


    Petri

    Wednesday, December 18, 2019 12:39 PM

Answers

  • Hi Petri,

    In my research, you can refer to the following SQL statement to join the two databases:

    SELECT [lsd1].[SessionIdTime], [lsd1].[SessionIdSeq], [ldd1].[ExternalId],
           [qdd1].[DialogID], [qsd1].[ConferenceDateTime], [qsd1].[SessionSeq]
    FROM ((([LcsCDR].[dbo].[SessionDetails] AS [lsd1]
    LEFT JOIN [LcsCDR].[dbo].[Dialogs] AS [ldd1]
        ON [lsd1].[SessionIdTime]      = [ldd1].[SessionIdTime]
       AND [lsd1].[SessionIdSeq]       = [ldd1].[SessionIdSeq])
    LEFT JOIN [QoEMetrics].[dbo].[Dialog] AS [qdd1]
        ON [ldd1].[ExternalId]         = [qdd1].[DialogID])
    LEFT JOIN [QoEMetrics].[dbo].[Session] AS [qsd1]
        ON [qdd1].[ConferenceDateTime] = [qsd1].[ConferenceDateTime]
       AND [qdd1].[SessionSeq]         = [qsd1].[SessionSeq])
    

    The connection is through the Dialogs tables in each database (ExternalID in LcsCDR.Dialogs joins to DialogID in QoEMetrics.Dialog). You can not directly join via SessionTime/ConferenceDataTime as the call session (LcdCDR) and the quality session (QoEMetrics) do not always start at the same time.


    Best Regards,
    Sharon Zhao


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    • Marked as answer by Petri X Friday, December 20, 2019 9:29 AM
    Thursday, December 19, 2019 3:14 AM
    Moderator

All replies

  • Hi Petri,

    In my research, you can refer to the following SQL statement to join the two databases:

    SELECT [lsd1].[SessionIdTime], [lsd1].[SessionIdSeq], [ldd1].[ExternalId],
           [qdd1].[DialogID], [qsd1].[ConferenceDateTime], [qsd1].[SessionSeq]
    FROM ((([LcsCDR].[dbo].[SessionDetails] AS [lsd1]
    LEFT JOIN [LcsCDR].[dbo].[Dialogs] AS [ldd1]
        ON [lsd1].[SessionIdTime]      = [ldd1].[SessionIdTime]
       AND [lsd1].[SessionIdSeq]       = [ldd1].[SessionIdSeq])
    LEFT JOIN [QoEMetrics].[dbo].[Dialog] AS [qdd1]
        ON [ldd1].[ExternalId]         = [qdd1].[DialogID])
    LEFT JOIN [QoEMetrics].[dbo].[Session] AS [qsd1]
        ON [qdd1].[ConferenceDateTime] = [qsd1].[ConferenceDateTime]
       AND [qdd1].[SessionSeq]         = [qsd1].[SessionSeq])
    

    The connection is through the Dialogs tables in each database (ExternalID in LcsCDR.Dialogs joins to DialogID in QoEMetrics.Dialog). You can not directly join via SessionTime/ConferenceDataTime as the call session (LcdCDR) and the quality session (QoEMetrics) do not always start at the same time.


    Best Regards,
    Sharon Zhao


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    • Marked as answer by Petri X Friday, December 20, 2019 9:29 AM
    Thursday, December 19, 2019 3:14 AM
    Moderator
  • Wow !

    Big thanks to you Sharon ! You lead me to correct path \o/

    I need to modify that a bit to get the IP addresses visible, and now it looks like this:

    SELECT [lsd1].[SessionIdTime],  [ldd1].[ExternalId],[lsd1].[SessionIdSeq],
           [qsd1].[ConferenceDateTime], [qsd1].[SessionSeq], [lsd1].[DiagnosticId], [lsd1].[ResponseCode],[QoEML1].[CalleeIPAddr], 
    	   [QoEML1].[CallerIPAddr], [QoEIPs].[IpAddress] AS 'Caller IP'
    
    FROM ((((([LcsCDR].[dbo].[SessionDetails] AS [lsd1]
    
    LEFT JOIN [LcsCDR].[dbo].[Dialogs] AS [ldd1]
        ON [lsd1].[SessionIdTime]      = [ldd1].[SessionIdTime]
       AND [lsd1].[SessionIdSeq]       = [ldd1].[SessionIdSeq])
    
    LEFT JOIN [QoEMetrics].[dbo].[Dialog] AS [qdd1]
        ON [ldd1].[ExternalId]         = [qdd1].[DialogID])
    
    LEFT JOIN [QoEMetrics].[dbo].[Session] AS [qsd1]
        ON [qdd1].[ConferenceDateTime] = [qsd1].[ConferenceDateTime]
       AND [qdd1].[SessionSeq]         = [qsd1].[SessionSeq])
    
    LEFT JOIN [QoEMetrics].[dbo].[MediaLine] AS [QoEML1]
        ON [qsd1].[ConferenceDateTime] = [QoEML1].[ConferenceDateTime]
       AND [qsd1].[SessionSeq]         = [QoEML1].[SessionSeq])
    
    LEFT JOIN [QoEMetrics].[dbo].[IpAddress] AS [QoEIPs]
        ON [QoEML1].[CallerIPAddr] = [QoEIPs].[IpAddressKey])
    /*   ON [QoEML1].[CalleeIPAddr] = [QoEIPs].IpAddressKey) */
    
    WHERE [qsd1].[ConferenceDateTime] > '2019-10-01' AND [lsd1].[DiagnosticId] = 32

    With the limited SQL skills I was not able to found the way to get callee's and caller's IP addresses on the same query, but excel is my friend :)

    It could also be so, that I did this a bit too hard way (too much excel required), as of course there was the "CallerSubnet" and "CalleeSubnet" columns available as well. I just did not found the source for those references as dbo.Subnet is empty at least on my QoE DB.

    You were great help again !


    Petri

    Friday, December 20, 2019 9:29 AM
  • Here I will provide a brief summary of this post. This will make answer searching in the forum easier.

     

    <Request/Expectation>:

    How to link database on LcsCDR database and database in QoEMetrics?

     

    <Suggestions>:

    SELECT [lsd1].[SessionIdTime],  [ldd1].[ExternalId],[lsd1].[SessionIdSeq],
    
           [qsd1].[ConferenceDateTime], [qsd1].[SessionSeq], [lsd1].[DiagnosticId], [lsd1].[ResponseCode],[QoEML1].[CalleeIPAddr],
    
                       [QoEML1].[CallerIPAddr], [QoEIPs].[IpAddress] AS 'Caller IP'
    
    FROM ((((([LcsCDR].[dbo].[SessionDetails] AS [lsd1]
    
    LEFT JOIN [LcsCDR].[dbo].[Dialogs] AS [ldd1]
    
        ON [lsd1].[SessionIdTime]      = [ldd1].[SessionIdTime]
    
       AND [lsd1].[SessionIdSeq]       = [ldd1].[SessionIdSeq])
    
    LEFT JOIN [QoEMetrics].[dbo].[Dialog] AS [qdd1]
    
        ON [ldd1].[ExternalId]         = [qdd1].[DialogID])
    
    LEFT JOIN [QoEMetrics].[dbo].[Session] AS [qsd1]
    
        ON [qdd1].[ConferenceDateTime] = [qsd1].[ConferenceDateTime]
    
       AND [qdd1].[SessionSeq]         = [qsd1].[SessionSeq])
    
    LEFT JOIN [QoEMetrics].[dbo].[MediaLine] AS [QoEML1]
    
        ON [qsd1].[ConferenceDateTime] = [QoEML1].[ConferenceDateTime]
    
       AND [qsd1].[SessionSeq]         = [QoEML1].[SessionSeq])
    
    LEFT JOIN [QoEMetrics].[dbo].[IpAddress] AS [QoEIPs]
    
        ON [QoEML1].[CallerIPAddr] = [QoEIPs].[IpAddressKey])
    
    /*   ON [QoEML1].[CalleeIPAddr] = [QoEIPs].IpAddressKey) */
    
    WHERE [qsd1].[ConferenceDateTime] > '2019-10-01' AND [lsd1].[DiagnosticId] = 32

    The connection is through the Dialogs tables in each database (ExternalID in LcsCDR.Dialogs joins to DialogID in QoEMetrics.Dialog). You can not directly join via SessionTime/ConferenceDataTime as the call session (LcdCDR) and the quality session (QoEMetrics) do not always start at the same time.


    Best Regards,
    Sharon Zhao


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Wednesday, December 25, 2019 8:23 AM
    Moderator