Important Lync report from SQL server – Wireless and Wired

–current daily troubleshooting wireless subnet report (“Wireless Subnets With The Most Poor Streams”) 6/7/2012

–Note: “poor streams” in this report are streams having PacketLossRate > .01

with v2 as

(

select

NetworkKey,

NetworkType,

Network,

NetworkName,

OtherEndpointName,

OtherEndpointIPAddress,

OtherEndpointType,

(case

when CallLength is null then N’NA’

when CallLength <= 30 then N’Dur <= 30 s’

when CallLength <= 60 then N’Dur 31 – 60 s’

when CallLength <= 90 then N’Dur 61 – 90 s’

when CallLength <= 120 then N’Dur 91 – 120 s’

else N’Dur 121+ s’

end) as CallLength,

(case

when CallLength is null then 10

when CallLength <= 30 then 1

when CallLength <= 60 then 2

when CallLength <= 90 then 3

when CallLength <= 120 then 4

else 5

end) as CallLengthOrdinal,

ConferenceDateTime,

SessionSeq,

StreamID,

count(1) over (partition by NetworkKey) as NetAllStreams,

count(IsBadStream) over(partition by NetworkKey) as NetBadStreams,

cast(100.0 * cast(count(IsBadStream) over(partition by NetworkKey) as float) / cast(count(1) over(partition by NetworkKey) as float) as decimal(4, 1)) as NetBadStreamsRatio,

IsBadStream

from

(

select

n1.NetworkKey,

n1.NetworkType,

n1.Network,

n1.NetworkName,

replace(s.CalleeEndpoint, N’.network.microsoft.com’, N”) as OtherEndpointName,

m.CalleeIPAddr as OtherEndpointIPAddress,

uat1.UserAgentType as OtherEndpointType,

datediff(second, s.StartTime, s.EndTime) as CallLength,

a.ConferenceDateTime,

a.SessionSeq,

a.StreamID,

(case when (PacketLossRate > .01 OR PacketLossRateMax > .05) then 1 else null end) as IsBadStream

from

LyncReporting.dbo.[QoE.Session] s with (nolock)

inner join

LyncReporting.dbo.[QoE.MediaLine] as m with (nolock)

on m.ConferenceDateTime = s.ConferenceDateTime

and m.SessionSeq = s.SessionSeq

and m.CallerNetworkConnectionTypeKey in

(select NetworkConnectionTypeKey

from LyncDimensions.dbo.DimNetworkConnectionType with (nolock)

where NetworkConnectionAlternateKey = 1)

inner join

LyncReporting.dbo.[QoE.AudioStream] as a with (nolock)

on a.MediaLineLabel = m.MediaLineLabel

and a.ConferenceDateTime = m.ConferenceDateTime

and a.SessionSeq = m.SessionSeq

inner join

LyncDimensions.dbo.DimNetwork n1 with (nolock)

on m.CallerSubnetKey = n1.NetworkKey

and n1.Network <> N’Unknown’

and n1.NetworkType in (‘VoIP’, ‘RAS/VPN’, ‘Corpnet’)

inner join

LyncDimensions.dbo.DimUserAgent ua1 with (nolock)

on s.CalleeUserAgentKey = ua1.UserAgentKey

inner join

LyncDimensions.dbo.DimUserAgentType uat1 with (nolock)

on ua1.UserAgentTypeKey = uat1.UserAgentTypeKey

and uat1.UserAgentTypeAlternateKey in (1, 2)

where

s.ConferenceDateTime >= (@beginTime) and s.ConferenceDateTime < (@endTime)

and m.CallerInside = 1

and m.CalleeInside = 1

union

select

n1.NetworkKey,

n1.NetworkType,

n1.Network,

n1.NetworkName,

replace(s.CallerEndpoint, N’.network.microsoft.com’, N”) as OtherEndpointName,

m.CallerIPAddr as OtherEndpointIPAddress,

uat1.UserAgentType as OtherEndpointType,

datediff(second, s.StartTime, s.EndTime) as CallLength,

a.ConferenceDateTime,

a.SessionSeq,

a.StreamID,

(case when (PacketLossRate > .01 OR PacketLossRateMax > .05) then 1 else null end) as IsBadStream

from

LyncReporting.dbo.[QoE.Session] s with (nolock)

inner join

LyncReporting.dbo.[QoE.MediaLine] as m with (nolock)

on m.ConferenceDateTime = s.ConferenceDateTime

and m.SessionSeq = s.SessionSeq

and m.CallerNetworkConnectionTypeKey in

(select NetworkConnectionTypeKey

from LyncDimensions.dbo.DimNetworkConnectionType with (nolock)

where NetworkConnectionAlternateKey = 1)

inner join

LyncReporting.dbo.[QoE.AudioStream] as a with (nolock)

on a.MediaLineLabel = m.MediaLineLabel

and a.ConferenceDateTime = m.ConferenceDateTime

and a.SessionSeq = m.SessionSeq

inner join

LyncDimensions.dbo.DimNetwork n1 with (nolock)

on m.CalleeSubnetKey = n1.NetworkKey

and n1.Network <> N’Unknown’

and n1.NetworkType in (‘VoIP’, ‘RAS/VPN’, ‘Corpnet’)

inner join

LyncDimensions.dbo.DimUserAgent ua1 with (nolock)

on s.CallerUserAgentKey = ua1.UserAgentKey

inner join

LyncDimensions.dbo.DimUserAgentType uat1 with (nolock)

on ua1.UserAgentTypeKey = uat1.UserAgentTypeKey

and uat1.UserAgentTypeAlternateKey in (1, 2)

where

s.ConferenceDateTime >= (@beginTime) and s.ConferenceDateTime < (@endTime)

and m.CallerInside = 1

and m.CalleeInside = 1

) as v1

),

v3 as

(

select top 10 NetworkKey

from (select distinct NetworkKey, NetBadStreams from v2) as v

order by NetBadStreams desc

)

select

max(Network) as Network,

max(NetworkType) as NetworkType,

max(NetworkName) as NetworkName,

OtherEndpointName,

max(OtherEndpointIPAddress) as OtherEndpointIPAddress,

max(OtherEndpointType) as OtherEndpointType,

max(NetAllStreams) as NetAllStreams,

max(NetBadStreams) as NetBadStreams,

max(NetBadStreamsRatio) as NetBadStreamsRatio,

max(CallLength) as CallLength,

CallLengthOrdinal,

count(1) as TotalStreams,

count(IsBadStream) as BadStreams

from v2 inner join v3 on v2.NetworkKey = v3.NetworkKey

group by

v2.NetworkKey,

OtherEndpointName,

CallLengthOrdinal

——————————————————————————————————————-

To produce this report: ( Poor call broken down by modality)

Ouput  Screen shot :   

Use the following SQL query:

 

 

 

 

 

use qoemetrics

DECLARE              @START_DATE  DATETIME

DECLARE              @END_DATE      DATETIME

SET         @START_DATE                  = ‘1/21/2013 11:21:06 PM’

SET         @END_DATE                      = ‘1/28/2013 11:21:06 PM’;

WITH P2P_Audio_FullJoin AS

(

SELECT

CallerInside, CalleeInside, CallerNetworkConnectionType, CalleeNetworkConnectionType,

CASE

WHEN (CallerInside = 1 AND CalleeInside = 1) THEN ‘BothInside’

WHEN (CallerInside = 0 AND CalleeInside = 0) THEN ‘BothOutside’

ELSE ‘Inside-Outside’

END AS Location,

CASE

WHEN (CallerNetworkConnectionType = 0 AND CalleeNetworkConnectionType = 0) THEN ‘BothWired’

WHEN (CallerNetworkConnectionType = 1 AND CalleeNetworkConnectionType = 1) THEN ‘BothWireless’

ELSE ‘Wired-Wireless’

END AS ConnectionType,

CASE WHEN (Session.ClassifiedPoorCall = 1) THEN 1 ELSE NULL END AS IsPoorCall,

Session.ClassifiedPoorCall

FROM   Session

INNER JOIN UserAgent AS CallerUA WITH(NOLOCK)

ON                         Session.CallerUserAgent = CallerUA.UserAgentKey

INNER JOIN UserAgent AS CalleeUA WITH(NOLOCK)

ON                         Session.CalleeUserAgent = CalleeUA.UserAgentKey

INNER JOIN UserAgentDef AS CallerUADef WITH(NOLOCK)

ON                         CallerUADef.UAType = CallerUA.UAType

INNER JOIN UserAgentDef AS CalleeUADef WITH(NOLOCK)

ON                         CalleeUADef.UAType = CalleeUA.UAType

INNER JOIN MediaLine AS AudioLine WITH(NOLOCK)

ON                         AudioLine.ConferenceDateTime = Session.ConferenceDateTime

AND       AudioLine.SessionSeq = Session.SessionSeq

AND       AudioLine.MediaLineLabel = 0 /*0=audio,1=video*/

/*

INNER JOIN AudioStream WITH(NOLOCK)

ON AudioStream.ConferenceDateTime = Session.ConferenceDateTime

AND AudioStream.SessionSeq = Session.SessionSeq

AND AudioStream.MediaLineLabel = AudioLine.MediaLineLabel

AND Session.ClassifiedPoorCall is not null

*/

WHERE Session.StartTime >= @START_DATE AND Session.EndTime < @END_DATE

AND (

( /*CallerUA.UAType = 1 OR*/   –MS

CallerUA.UAType = 4 OR                             –OC

CallerUA.UAType = 8 OR                             –OCPhone

CallerUA.UAType = 16 OR                          –LMC

CallerUA.UAType = 64 OR                          –Mac Messenger

CallerUA.UAType = 128 OR                        –Attendant

CallerUA.UAType = 512 OR                        –CAA

CallerUA.UAType = 1024 OR                      –RGS

CallerUA.UAType = 16386 OR   –Como

CallerUA.UAType =16387                           –CWA

/*CallerUA.UAType = 16393*/ –EXUM

)

AND

( /*CalleeUA.UAType = 1 OR*/  –MS

CalleeUA.UAType = 4 OR                            –OC

CalleeUA.UAType = 8 OR                            –OCPhone

CalleeUA.UAType = 16 OR                         –LMC

CalleeUA.UAType = 64 OR                         –Mac Messenger

CalleeUA.UAType = 128 OR                       –Attendant

CalleeUA.UAType = 512 OR                       –CAA

CalleeUA.UAType = 1024 OR                     –RGS

CalleeUA.UAType = 16386 OR  –Como

CalleeUA.UAType =16387                          –CWA

/*CalleeUA.UAType = 16393*/                –EXUM

)

)

),

Totals as

(

SELECT COUNT(*) AS NumSessions FROM P2P_Audio_FullJoin

)

SELECT

DISTINCT Location, ConnectionType, COUNT(*) AS NumSessions, COUNT(IsPoorCall) AS PoorSessions

FROM P2P_Audio_FullJoin

GROUP BY Location, ConnectionType

ORDER BY Location, ConnectionType

Advertisements

Leave a comment

Filed under Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s