Querry with results only from specific subnets?

Question

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 733197 More actions · Answer 1

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.