Fault detected on <xsl:value-of select="EventDetail/Asset/AssetName" /> (<xsl:value-of select="EventDetail/Asset/AssetKey" />)

INSERT INTO Setting (Name,Value,DefaultValue) VALUES ('PQBrowser.URL','',''); INSERT INTO Setting (Name,Value,DefaultValue) VALUES ('Notifications.URL','',''); INSERT INTO EmailType (CombineEventsSQL,EmailCategoryID, ShowSubscription, SMS, TriggerEmailSQL, Template, Name, MinDelay, MaxDelay) VALUES ('',(SELECT ID FROM EmailCategory WHERE Name = 'Event'),1,0,'','','Faults',0,0), ('',(SELECT ID FROM EmailCategory WHERE Name = 'Event'),1,0,'','','Non-Fault Events',0,0) GO UPDATE EmailType SET TriggerEmailSQL = 'SELECT CASE WHEN EventType.Name IN (''Fault'', ''RecloseIntoFault'') AND Event.StartTime > DATEADD(HOUR, -24, GETDATE()) THEN 1 ELSE 0 END FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID WHERE Event.ID = {0}' WHERE Name = 'Faults'; UPDATE EmailType SET CombineEventsSQL = 'DECLARE @timeTolerance FLOAT = (SELECT CAST(Value AS FLOAT) FROM Setting WHERE Name = ''DataAnalysis.TimeTolerance'') DECLARE @assetID INT DECLARE @startTime DATETIME2 DECLARE @endTime DATETIME2 SELECT @assetID = AssetID, @startTime = dbo.AdjustDateTime2(StartTime, -@timeTolerance), @endTime = dbo.AdjustDateTime2(EndTime, @timeTolerance) FROM Event WHERE ID = {0} SELECT ID FROM Event WHERE Event.AssetID = @assetID AND Event.EndTime >= @startTime AND Event.StartTime <= @endTime' WHERE Name = 'Faults'; UPDATE EmailType SET TriggerEmailSQL = 'SELECT CASE WHEN EventType.Name NOT IN (''Fault'', ''RecloseIntoFault'', ''Test'') AND Event.StartTime > DATEADD(HOUR, -24, GETDATE()) THEN 1 ELSE 0 END FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID WHERE Event.ID = {0}' WHERE Name = 'Non-Fault Events'; UPDATE EmailType SET CombineEventsSQL = 'DECLARE @timeTolerance FLOAT = (SELECT CAST(Value AS FLOAT) FROM Setting WHERE Name = ''DataAnalysis.TimeTolerance'') DECLARE @assetID INT DECLARE @startTime DATETIME2 DECLARE @endTime DATETIME2 SELECT @assetID = AssetID, @startTime = dbo.AdjustDateTime2(StartTime, -@timeTolerance), @endTime = dbo.AdjustDateTime2(EndTime, @timeTolerance) FROM Event WHERE ID = {0} SELECT ID FROM Event WHERE Event.AssetID = @assetID AND Event.EndTime >= @startTime AND Event.StartTime <= @endTime' WHERE Name = 'Non-Fault Events'; /* Add DataSources */ INSERT INTO TriggeredEmailDataSourceEmailType (EmailTypeID, TriggeredEmailDataSourceID) VALUES ((SELECT ID FROM EmailType WHERE Name = 'Faults'), (SELECT ID FROM TriggeredEmailDataSource WHERE Name = 'SQL')), ((SELECT ID FROM EmailType WHERE Name = 'Non-Fault Events'), (SELECT ID FROM TriggeredEmailDataSource WHERE Name = 'SQL')) GO /* Update Templates*/ UPDATE EmailType SET Template = ' Fault detected on <xsl:value-of select="EventDetail/Asset/AssetName" /> (<xsl:value-of select="EventDetail/Asset/AssetKey" />)

Fault -

Station:	()
Meter:	/ ()
File:
Fault Type:
Inception Time:
Fault Duration:	msec ( cycles)
Fault Current:	Amps (RMS)

Click to view in PQ Browser.

- Voltage

Voltage (volts) 2 1 640 350 cycle VAN VBN VCN

- Current

Current (Amps) 2 1 640 350 cycle IAN IBN ICN

To manage your automated fault notification subscription, or to unsubscribe, click on this link.

' WHERE Name = 'Faults'; INSERT INTO TriggeredEmailDataSourceSetting (TriggeredEmailDataSourceEmailTypeID,Name,Value) VALUES ((SELECT ID FROM TriggeredEmailDataSourceEmailType WHERE EmailTypeID = (SELECT ID FROM EmailType WHERE Name = 'Faults')),'DataProviderString','AssemblyName={System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=System.Data.SqlClient.SqlConnection; AdapterType=System.Data.SqlClient.SqlDataAdapter'), ((SELECT ID FROM TriggeredEmailDataSourceEmailType WHERE EmailTypeID = (SELECT ID FROM EmailType WHERE Name = 'Faults')),'SQLStatement','DECLARE @timeTolerance FLOAT = (SELECT CAST(Value AS FLOAT) FROM Setting WHERE Name = ''DataAnalysis.TimeTolerance'') DECLARE @assetID INT DECLARE @startTime DATETIME2 DECLARE @endTime DATETIME2 SELECT @assetID = AssetID, @startTime = dbo.AdjustDateTime2(StartTime, -@timeTolerance), @endTime = dbo.AdjustDateTime2(EndTime, @timeTolerance) FROM Event WHERE ID = {0} SELECT * INTO #systemEvent FROM Event WHERE Event.EndTime >= @startTime AND Event.StartTime <= @endTime SELECT * INTO #assetEvent FROM Event WHERE Event.AssetID = @assetID AND Event.EndTime >= @startTime AND Event.StartTime <= @endTime SELECT SQRT(R1 * R1 + X1 * X1) AS Z1, CASE R1 WHEN 0 THEN 0 ELSE ATN2(X1, R1) * 180 / PI() END AS A1, R1, X1, SQRT(R0 * R0 + X0 * X0) AS Z0, CASE R0 WHEN 0 THEN 0 ELSE ATN2(X0, R0) * 180 / PI() END AS A0, R0, X0, SQRT(POWER((2.0 * R1 + R0) / 3.0, 2) + POWER((2.0 * X1 + X0) / 3.0, 2)) AS ZS, CASE 2.0 * R1 + R0 WHEN 0 THEN 0 ELSE ATN2((2.0 * X1 + X0) / 3.0, (2.0 * R1 + R0) / 3.0) * 180 / PI() END AS [AS], (2.0 * R1 + R0) / 3.0 AS RS, (2.0 * X1 + X0) / 3.0 AS XS, AssetKey, Length INTO #lineImpedance FROM LineView WHERE ID = @assetID SELECT ROW_NUMBER() OVER(PARTITION BY Event.MeterID ORDER BY FaultSummary.Inception) AS FaultNumber, FaultSummary.ID AS FaultSummaryID, Meter.AssetKey AS MeterKey, Meter.Make AS MeterMake, Meter.Name AS MeterName, Location.LocationKey as StationKey, Location.Name AS StationName, Asset.AssetKey AS AssetKey, Asset.AssetName AS AssetName, FaultSummary.FaultType, FaultSummary.Inception, FaultSummary.DurationCycles, FaultSummary.DurationSeconds * 1000.0 AS DurationMilliseconds, FaultSummary.PrefaultCurrent, FaultSummary.PostfaultCurrent, FaultSummary.ReactanceRatio, FaultSummary.CurrentMagnitude AS FaultCurrent, FaultSummary.Algorithm, FaultSummary.Distance AS SingleEndedDistance, DoubleEndedFaultSummary.Distance AS DoubleEndedDistance, DoubleEndedFaultSummary.Angle AS DoubleEndedAngle, RIGHT(DataFile.FilePath, CHARINDEX(''\'', REVERSE(DataFile.FilePath)) - 1) AS FileName, FaultCauseMetrics.TreeFaultResistance, FaultCauseMetrics.LightningMilliseconds, FaultCauseMetrics.InceptionDistanceFromPeak, FaultCauseMetrics.PrefaultThirdHarmonic, FaultCauseMetrics.GroundCurrentRatio, FaultCauseMetrics.LowPrefaultCurrentRatio, FaultSummary.EventID, Event.StartTime AS EventStartTime, SimpleSummary.Distance AS Simple, ReactanceSummary.Distance AS Reactance, Event.EndTime AS EventEndTime, CONCAT((SELECT TOP 1 VALUE FROM Setting WHERE Name = ''PQBrowser.URL''),''/eventsearch?date='', FORMAT(FaultSummary.Inception,''MM dd yyyy''),''&time='',FORMAT(FaultSummary.Inception,''HH:mm:ss.000''), ''&windowSize=10&timeWindowUnits=2&eventid='', FaultSummary.EventID) AS PQBrowserLink, CASE FaultSummary.FaultType WHEN ''AN'' THEN ''A-phase to ground'' WHEN ''BN'' THEN ''B-phase to ground'' WHEN ''CN'' THEN ''C-phase to ground'' WHEN ''AB'' THEN ''A-phase to B-phase'' WHEN ''BC'' THEN ''B-phase to C-phase'' WHEN ''CA'' THEN ''C-phase to A-phase'' WHEN ''ABG'' THEN ''A-phase to B-phase to ground'' WHEN ''BCG'' THEN ''B-phase to C-phase to ground'' WHEN ''CAG'' THEN ''C-phase to A-phase to ground'' WHEN ''ABC'' THEN ''Three phase'' WHEN ''ABCG'' THEN ''Three phase to ground'' ELSE FaultSummary.FaultType END LaymanFaultType INTO #summaryData FROM #assetEvent Event JOIN FaultSummary ON FaultSummary.EventID = Event.ID AND FaultSummary.IsSelectedAlgorithm <> 0 AND FaultSummary.IsSuppressed = 0 LEFT OUTER JOIN FaultSummary SimpleSummary ON FaultSummary.EventID = SimpleSummary.EventID AND FaultSummary.Inception = SimpleSummary.Inception AND SimpleSummary.Algorithm = ''Simple'' LEFT OUTER JOIN FaultSummary ReactanceSummary ON FaultSummary.EventID = ReactanceSummary.EventID AND FaultSummary.Inception = ReactanceSummary.Inception AND ReactanceSummary.Algorithm = ''Reactance'' LEFT OUTER JOIN FaultCauseMetrics ON FaultSummary.EventID = FaultCauseMetrics.EventID AND FaultSummary.FaultNumber = FaultCauseMetrics.FaultNumber JOIN DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN Meter ON Event.MeterID = Meter.ID JOIN Location ON Meter.LocationID = Location.ID JOIN Asset ON Asset.ID = Event.AssetID LEFT OUTER JOIN DoubleEndedFaultDistance ON DoubleEndedFaultDistance.LocalFaultSummaryID = FaultSummary.ID LEFT OUTER JOIN DoubleEndedFaultSummary ON DoubleEndedFaultSummary.ID = DoubleEndedFaultDistance.ID WHERE DataFile.FilePath LIKE ''%.DAT'' OR DataFile.FilePath LIKE ''%.D00'' OR DataFile.FilePath LIKE ''%.PQD'' OR DataFile.FilePath LIKE ''%.RCD'' OR DataFile.FilePath LIKE ''%.RCL'' OR DataFile.FilePath LIKE ''%.SEL'' OR DataFile.FilePath LIKE ''%.EVE'' OR DataFile.FilePath LIKE ''%.CEV'' SELECT DISTINCT FaultNumber INTO #FaultList FROM #summaryData DECLARE @url VARCHAR(MAX) = (SELECT Value FROM DashSettings WHERE Name = ''System.URL'') SELECT ( SELECT FaultNumber AS [@num], ( SELECT DoubleEndedDistance, Inception, StationKey, StationName, FaultType, EventID, FaultSummaryID, LaymanFaultType, DurationCycles, DurationMilliseconds, SingleEndedDistance, MeterKey, MeterName, MeterMake, FaultCurrent, DoubleEndedAngle, LowPrefaultCurrentRatio, LightningMilliseconds, InceptionDistanceFromPeak, PrefaultThirdHarmonic, Algorithm, GroundCurrentRatio, RIGHT(FileName, 8) AS ShortFileName, PQBrowserLink, CASE WHEN ABS(#lineImpedance.ZS * Simple) >= ABS(#lineImpedance.XS * Reactance) THEN FORMAT((SQRT(POWER(#lineImpedance.ZS * Simple, 2) - POWER(#lineImpedance.XS * Reactance, 2)) - #lineImpedance.RS * Reactance) / #lineImpedance.Length, ''0.##########'') ELSE ''0.0'' END AS TreeResistance, CASE WHEN ABS(#lineImpedance.ZS * Simple) < ABS(#lineImpedance.XS * Reactance) THEN 1 ELSE 0 END AS TreeResistanceError FROM #summaryData LEFT JOIN #lineImpedance ON #summaryData.AssetKey = #lineImpedance.AssetKey WHERE #summaryData.FaultNumber = Fault.FaultNumber FOR XML PATH(''Summary''), TYPE ) AS Summaries FROM #FaultList Fault FOR XML PATH(''Fault''), TYPE ) AS Faults, FORMAT(#lineImpedance.Length, ''0.##########'') AS [Line/Length], FORMAT(#lineImpedance.Z1, ''0.##########'') AS [Line/Z1], FORMAT(#lineImpedance.R1, ''0.##########'') AS [Line/R1], FORMAT(#lineImpedance.X1, ''0.##########'') AS [Line/X1], FORMAT(#lineImpedance.A1, ''0.##########'') AS [Line/A1], FORMAT(#lineImpedance.Z0, ''0.##########'') AS [Line/Z0], FORMAT(#lineImpedance.R0, ''0.##########'') AS [Line/R0], FORMAT(#lineImpedance.X0, ''0.##########'') AS [Line/X0], FORMAT(#lineImpedance.A0, ''0.##########'') AS [Line/A0], FORMAT(#lineImpedance.XS, ''0.##########'') AS [Line/XS], FORMAT(#lineImpedance.ZS, ''0.##########'') AS [Line/ZS], FORMAT(#lineImpedance.[AS], ''0.##########'') AS [Line/AS], FORMAT(#lineImpedance.RS, ''0.##########'') AS [Line/RS], Asset.AssetKey AS [Asset/AssetKey], Asset.AssetName AS [Asset/AssetName], Asset.Description AS [Asset/Description], (SELECT TOP 1 VALUE FROM Setting WHERE Name = ''PQBrowser.URL'') AS [System/PQBrowser], (SELECT TOP 1 VALUE FROM Setting WHERE Name = ''Notifications.URL'') AS [System/Notifications], ( SELECT COALESCE(Point, '''') FROM EDNAPoint WHERE BreakerID = Asset.ID FOR XML PATH(''Point''), TYPE ) AS [EDNA] FROM Event JOIN Asset ON Event.AssetID = Asset.ID lEFT JOIN #lineImpedance ON #lineImpedance.AssetKey = Asset.AssetKey WHERE Event.ID = {0} FOR XML PATH(''EventDetail''), TYPE ') GO UPDATE EmailType SET Template = ' [<xsl:value-of select="EventDetail/Event/Type" />] <xsl:value-of select="EventDetail/Meter/Name" />

detected at

Station:
Meter:
File:
Voltage:	kV

Click to view in PQ Browser.

- Voltage

Voltage (volts) 640 350 VAN VBN VCN

- Current

Current (Amps) 640 350 IAN IBN ICN

Voltage Disturbances:

Start:
Type:
Phase:
Magnitude:
Duration:	ms ( cycles)
Severity:

To manage your automated event notification subscription, or to unsubscribe, click on this link.

' WHERE Name = 'Non-Fault Events'; INSERT INTO TriggeredEmailDataSourceSetting (TriggeredEmailDataSourceEmailTypeID,Name,Value) VALUES ((SELECT ID FROM TriggeredEmailDataSourceEmailType WHERE EmailTypeID = (SELECT ID FROM EmailType WHERE Name = 'Non-Fault Events') AND TriggeredEmailDataSourceID = (SELECT ID FROM TriggeredEmailDataSource WHERE Name = 'SQL')),'DataProviderString','AssemblyName={System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=System.Data.SqlClient.SqlConnection; AdapterType=System.Data.SqlClient.SqlDataAdapter'), ((SELECT ID FROM TriggeredEmailDataSourceEmailType WHERE EmailTypeID = (SELECT ID FROM EmailType WHERE Name = 'Non-Fault Events') AND TriggeredEmailDataSourceID = (SELECT ID FROM TriggeredEmailDataSource WHERE Name = 'SQL')),'SQLStatement','DECLARE @eventID INT = {0} SELECT Event.ID AS [Events/Event/@id], Event.ID AS [Event/ID], EventType.Name AS [Event/Type], Event.StartTime AS [Event/StartTime], Event.EndTime AS [Event/EndTime], Location.LocationKey AS [Station/Key], Location.Name AS [Station/Name], Meter.AssetKey AS [Meter/Key], Meter.Name AS [Meter/Name], (SELECT COUNT(MeasurementTypeID) FROM Channel WHERE AssetID = (SELECT AssetID FROM Event WHERE ID = {0}) AND Trend = 0 AND MeasurementTypeID = (SELECT ID FROM MeasurementType WHERE Name = ''Voltage'')) AS [Meter/VChannelCount], (SELECT COUNT(MeasurementTypeID) FROM Channel WHERE AssetID = (SELECT AssetID FROM Event WHERE ID = {0}) AND Trend = 0 AND MeasurementTypeID = (SELECT ID FROM MeasurementType WHERE Name = ''Current'')) AS [Meter/IChannelCount], Asset.VoltageKV AS [Asset/Voltage], CONCAT((SELECT TOP 1 VALUE FROM Setting WHERE Name = ''PQBrowser.URL''),''/eventsearch?date='', FORMAT(Event.StartTime,''MM dd yyyy''),''&time='',FORMAT(Event.StartTime,''HH:mm:ss.000''), ''&windowSize=10&timeWindowUnits=2&eventid='', Event.ID) AS [System/PQBrowserLink], (SELECT TOP 1 VALUE FROM Setting WHERE Name = ''Notifications.URL'') AS [System/Notifications], ( SELECT DisturbanceType.Name AS Type, Phase.Name AS Phase, Disturbance.StartTime, Disturbance.EndTime, Disturbance.PerUnitMagnitude, Disturbance.DurationSeconds * 1000.0 AS DurationMilliseconds, Disturbance.DurationCycles, DisturbanceSeverity.SeverityCode AS Severity FROM Disturbance Worst JOIN Phase WorstPhase ON Worst.PhaseID = WorstPhase.ID AND WorstPhase.Name = ''Worst'' JOIN Disturbance ON Disturbance.EventID = Worst.EventID AND Disturbance.EventTypeID = Worst.EventTypeID AND Disturbance.StartTime = Worst.StartTime AND Disturbance.EndTime = Worst.EndTime AND Disturbance.PerUnitMagnitude = Worst.PerUnitMagnitude AND Disturbance.PhaseID <> Worst.PhaseID JOIN EventType DisturbanceType ON Disturbance.EventTypeID = DisturbanceType.ID JOIN Phase ON Disturbance.PhaseID = Phase.ID LEFT OUTER JOIN DisturbanceSeverity ON DisturbanceSeverity.DisturbanceID = Disturbance.ID WHERE Disturbance.EventID = Event.ID ORDER BY Disturbance.StartTime FOR XML PATH(''Disturbance''), TYPE ) [Disturbances], RIGHT(DataFile.FilePath, CHARINDEX(CHAR(92), REVERSE(DataFile.FilePath)) - 1) [File/Name] FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID JOIN Location ON Meter.LocationID = Location.ID JOIN Asset ON Event.AssetID = Asset.ID JOIN DataFile ON Event.FileGroupID = DataFile.FileGroupID AND ( DataFile.FilePath LIKE ''%.DAT'' OR DataFile.FilePath LIKE ''%.D00'' OR DataFile.FilePath LIKE ''%.PQD'' OR DataFile.FilePath LIKE ''%.RCD'' OR DataFile.FilePath LIKE ''%.RCL'' OR DataFile.FilePath LIKE ''%.SEL'' OR DataFile.FilePath LIKE ''%.EVE'' OR DataFile.FilePath LIKE ''%.CEV'' ) WHERE Event.ID = @eventID FOR XML PATH(''EventDetail''), TYPE ') GO