Skype und Lync

Skype CDR Daten mit Splunk auswerten (Teil 3)

Siehe Teil 1 und Teil 2.

Statistiken für Telefonanrufe

Für die Auswertung der eingehenden und ausgehenden PSTN Anrufe gibts es keine passende Stored Procedure. Hier zeigt sich erneut der nicht konsistente Entwicklungsstand der Monitoring Server Reports. Es ist aber nicht schwer, eine passende Stored Procedure PSTNCallSummaryReport zu schreiben. Die Anrufe sind in der Tabelle dbo.VoipDetails aufgelistet und wir können diese entsprechend zählen und stundenweise aggregieren. Die Richtung des Anrufs ist durch die Telefonnummer gegeben, da die interne Seite immer als Audio Call mit einer SIP Adresse erscheint und die externe Seite mit einer Telefonnummer. Mit dieser Unterscheidung lassen sich eingehende und ausgehende Anrufe getrennt erfassen. Die Details eines Anrufs sind aber dann wieder, wie alle Verbindungen überhaupt, in der Tabelle dbo.SessionDetails zu finden. Das restliche Drumherum der neuen Stored Procedure kann von einem anderen Summary Report übernommen und angepasst werden.

 

Stored Procedure PSTNCallSummaryReport

USE [LcsCDR]

GO

/****** Object:  StoredProcedure [dbo].[PSTNCallSummaryReport]    Script Date: 22.09.2016 15:31:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[PSTNCallSummaryReport]

   @_StartTime     datetime = null,

   @_EndTime       datetime = null,

   @_Interval              int = 5,

   @_WindowSize            int = 5

WITH RECOMPILE — Recompile sprocs that may query large amount of data

as

begin

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    declare @Status int

    declare @Msg    int

    set @Msg = 0

       — create table for time fields

    create table #TimeAxis ( StartTime datetime, EndTime datetime )

    if (@_EndTime is null) select @_EndTime = getutcdate()

    if (@_StartTime is null) select @_StartTime = dateadd(hour, –1*1, @_EndTime)

    if (dbo.pValidReportingDateTime(@_StartTime, @_EndTime, @_Interval, @_WindowSize) = 0) begin

       set @Msg = 50500

       goto errorhandler

    end

    exec @Status = dbo.RtcPopulateTimeAxis @_StartTime, @_EndTime, @_Interval

    if (@Status <> 0) begin

        set @Msg = 50500

        goto errorhandler

    end

 

Nach diesem Geplänkel folgt hier der eigentliche Code:

 

       — decide, if the call is inbound or outbound traffic

       SELECT CASE WHEN LEFT(Users1.UserUri,1) = ‚+‘ THEN ‚inbound‘ ELSE ‚outbound‘ END AS Typ,

       VoipDetails.SessionIdTime [Date],

               CONVERT(varchar(10),SessionDetails.SessionEndTime VoipDetails.SessionIdTime,108) [Time],

               Phones.PhoneUri [From Number],

               Users1.UserUri [FromSip],

               Phones1.PhoneUri [Number Dialed],

                    SessionDetails.SessionEndTime,

                    VoipDetails.SessionIdTime

       — write into temporary table

       INTO #pstntable

       FROM VoipDetails LEFT OUTER JOIN SessionDetails

       ON VoipDetails.SessionIdTime = SessionDetails.SessionIdTime AND VoipDetails.SessionIdSeq = SessionDetails.SessionIdSeq

       LEFT OUTER JOIN  Phones

       ON VoipDetails.FromNumberId = Phones.PhoneId

       LEFT OUTER JOIN  Phones Phones1

       ON VoipDetails.ConnectedNumberId = Phones1.PhoneId

       LEFT OUTER JOIN  Users Users1

       ON SessionDetails.User1Id = Users1.UserId

       group by Phones.PhoneUri, Users1.UserUri, VoipDetails.SessionIdTime, SessionDetails.SessionEndTime, Phones1.PhoneUri

       select

       T.StartTime as Sample,

       sum(case when Typ = ‚outbound‘ then 1 else 0 end) as outbound,

       sum(case when Typ = ‚inbound‘ then 1 else 0 end) as inbound

       from #TimeAxis T

       left join #pstntable p on T.Starttime <= p.Date and T.EndTime >= p.Date

       group by T.StartTime

 

…und noch die standard Fehlerbehandlung und das Aufräumen:

errorhandler:

    if (@Msg <> 0) begin

        exec dbo.DbRaiseError @@procid, @Msg

    end

       –delete temporary tables

    drop table #TimeAxis

       drop table #pstntable

    return @Msg

end

 

Aufruf der Stored Procedure mit Powershell

Der Aufruf kann jetzt ganz analog wie bei den built-in Summary Reports unter dem gewählten Namen PSTNCallSummaryReport erfolgen. Das Ergebnis ist eine aggregierte CSV-Datei mit nach Richtung getrennt summierten Anrufen. Im Splunk kann die Datei wie im Teil 2 beschrieben indiziert werden.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.