Querry with results only from specific subnets?

• alexander.holmeset

  SSC Rookie

  Points: 42

  More actions

  December 3, 2017 at 3:23 pm

  #330398

  How can i do a querry that only shows results with a speciffic caller or callee subnet?
  This is from Skype for Business CQM, where we are querrying the QOEMetrics database

  USE QoEMetrics;

  DECLARE @beginTime AS DateTime = ###STARTTIME;
  DECLARE @endTime AS DateTime = ###ENDTIME;
  DECLARE @dateFormat AS Int = 105;          

  DECLARE @wiredClientUATypes TABLE (UAType INT);

  INSERT INTO @wiredClientUATypes VALUES (4),(8),(16),(64),(128),(16403),(16405),(16407),(16411),(16412);

  WITH FullLyncJoinView AS
  (
      SELECT
          s.ConferenceDateTime as ConferenceDateTime
          ,s.StartTime as StartTime
          ,CallerUA.UAType AS CallerUAType
          ,CalleeUA.UAType AS CalleeUAType    
          ,m.CallerSubnet AS CallerSubnet
          ,m.CalleeSubnet AS CalleeSubnet
          ,###POORSTREAMCONDITION AS IsPoorStream
      
      FROM [Session] s WITH (NOLOCK)
          INNER JOIN [MediaLine] AS m WITH (NOLOCK) ON
              m.ConferenceDateTime = s.ConferenceDateTime
              AND m.SessionSeq = s.SessionSeq            
          INNER JOIN [AudioStream] AS a WITH (NOLOCK) ON
              a.MediaLineLabel = m.MediaLineLabel 
              and a.ConferenceDateTime = m.ConferenceDateTime
              and a.SessionSeq = m.SessionSeq            
          INNER JOIN UserAgent AS CallerUA WITH (NOLOCK) ON
              CallerUA.UserAgentKey = s.CallerUserAgent
          INNER JOIN UserAgent AS CalleeUA WITH (NOLOCK) ON
              CalleeUA.UserAgentKey = s.CalleeUserAgent
          INNER JOIN [NetworkConnectionDetail] AS CallerNcd WITH (NOLOCK) ON
              CallerNcd.NetworkConnectionDetailKey = m.CallerNetworkConnectionType
         INNER JOIN [NetworkConnectionDetail] AS CalleeNcd WITH (NOLOCK) ON
              CalleeNcd.NetworkConnectionDetailKey = m.CalleeNetworkConnectionType
      WHERE
          s.StartTime >= (@beginTime) and s.StartTime < (@endTime)
          and CallerNcd.NetworkConnectionDetail in ('wired','Ethernet')
          and CalleeNcd.NetworkConnectionDetail in ('wired','Ethernet')
         and m.CallerInside = 1    
          and m.CalleeInside = 1
          and     
  )
  ,AllVOIPStreams as
  (
      SELECT   
          CONVERT(date,StartTime,@dateFormat) AS ReportDate
          ,ConferenceDateTime        
          ,IsPoorStream  
      FROM      
          FullLyncJoinView
      WHERE
           CallerUAType in (SELECT UAType FROM @wiredClientUATypes)
       and CalleeUAType in (1,2,32769)
    
      UNION ALL  
              
      SELECT   
          CONVERT(date,StartTime,@dateFormat) AS ReportDate
          ,ConferenceDateTime        
          ,IsPoorStream  
      FROM      
          FullLyncJoinView
      WHERE
      CallerUAType in (1,2,32769) 
        and CalleeUAType in (SELECT UAType FROM @wiredClientUATypes)

  )
  ,PoorStreamsSummary AS
  (
      SELECT          
          ReportDate 
          ,count(*) as AllStreams
          ,count(IsPoorStream) as PoorStreams
          ,cast(100.0 * cast(count(IsPoorStream) as float) / cast(count(*) as float) as decimal(4, 1)) as PoorStreamsRatio
      FROM
          AllVOIPStreams
      GROUP BY
          ReportDate

  ) 

  SELECT
    ReportDate,
      AllStreams,
      PoorStreams,
      PoorStreamsRatio,
      'Trend_3_Wired' as QueryType
  FROM
    PoorStreamsSummary
  ORDER BY
    ReportDate

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737961

  More actions

  December 4, 2017 at 9:25 am

  #1970550

  This isn't really a good question as listed. Is this the query you want or do you want to further filter results by caller?

  Ultimately you should produce a small data set of relevant columns in a sample data (DDL and DML) and then we can help you with a query. Please explain the results you need and what isn't working in your attempts.