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.