Re: Alarm DB Logger: добавление пользовательских полей в баз
![Сообщение Сообщение](./styles/prosilver/imageset/icon_post_target.gif)
Добавлено:
Чт июн 08, 2017 3:32 pm
кТуЛхУ
Попробовал Detailed, там то же самое - AlarmComment перезаписывается.
Может кому-то пригодится, решил проблему следующим образом: в интаче вывожу алармы и события из базы с помощью AlmDbViewCtrl. Эта штука читает данные из вьюхи v_AlarmEventHistoryInternal. В таблице AlarmConsolidated (у меня включен консолидированный режим) есть поле AckCommentId - по которому строка из таблицы Comment идет в поле Description AlmDbViewCtrl, а есть поле CommentId в котором хранится id коммента тэга. Поэтому в запросе для v_AlarmEventHistoryInternal в поле User3 записываю коммент тэга из таблицы Comment по его CommentId.
Может не совсем понятно написал, на всякий случай новый запрос ниже: (хотел приложить файлом, но настройки форума не позволяют).
//>============================================================================================================================================
SELECT DATEADD(mi, AlarmMaster.OriginationTimeZoneOffset - (AlarmMaster.OriginationDaylightAdjustment * 7.5), AlarmMaster.OriginationTime) AS EventStamp,
CAST('UNACK_ALM' AS nVarChar(9)) AS AlarmState, CAST(AlarmMaster.TagName AS nVarChar(132)) AS TagName, CAST(Comment.Comment AS nVarChar(255))
AS Description, CAST(AlarmMaster.GroupName AS nVarChar(32)) AS Area, CAST(AlarmMaster.AlarmType AS nVarChar(6)) AS Type, AlarmMaster.ValueString AS Value,
AlarmMaster.LimitString AS CheckValue, AlarmMaster.Priority, CAST(AlarmMaster.AlarmClass AS nVarChar(5)) AS Category,
CAST((RTRIM(ProviderSession.NodeName) + '\' + RTRIM(ProviderSession.ProviderType)) AS nVarChar(65)) AS Provider,
CAST(AlarmConsolidated.OperatorName AS nVarChar(131)) AS Operator, CAST(OperatorDetails.UserDomainName AS nVarChar(155)) AS DomainName,
CAST(OperatorDetails.UserFullName AS nVarChar(255)) AS UserFullName, replicate(' ', 17) AS UnAckDuration, AlarmMaster.User1 AS User1,
AlarmMaster.User2 AS User2, CAST(Comment.Comment AS nVarChar(131)) AS User3, AlarmMaster.OriginationTime AS EventStampUTC,
AlarmMaster.OriginationTimeFracSec AS MilliSec, CAST(AlarmConsolidated.OperatorNode AS nVarChar(131)) AS OperatorNode
FROM AlarmMaster INNER JOIN
AlarmConsolidated ON AlarmMaster.AlarmId = AlarmConsolidated.AlarmId INNER JOIN
ProviderSession ON AlarmMaster.ProviderId = ProviderSession.ProviderId LEFT OUTER JOIN
Comment ON AlarmConsolidated.CommentId = Comment.CommentId LEFT OUTER JOIN
OperatorDetails ON AlarmConsolidated.OperatorID = OperatorDetails.OperatorID
WHERE AlarmConsolidated.AlarmTime <> '9999-12-12 23:59:59.997'
UNION
SELECT DATEADD(mi, AlarmConsolidated.AlarmTimeZoneOffset - (AlarmConsolidated.AlarmDaylightAdjustment * 7.5), AlarmConsolidated.AlarmTime) AS EventStamp,
CAST('UNACK_ALM' AS nVarChar(9)) AS AlarmState, CAST(AlarmMaster.TagName AS nVarChar(132)) AS TagName, CAST(Comment.Comment AS nVarChar(255))
AS Description, CAST(AlarmMaster.GroupName AS nVarChar(32)) AS Area, CAST(AlarmConsolidated.AlarmType AS nVarChar(6)) AS Type,
AlarmConsolidated.ValueString AS Value, AlarmConsolidated.LimitString AS CheckValue, AlarmConsolidated.Priority, CAST(AlarmMaster.AlarmClass AS nVarChar(5))
AS Category, CAST((RTRIM(ProviderSession.NodeName) + '\' + RTRIM(ProviderSession.ProviderType)) AS nVarChar(65)) AS Provider,
CAST(AlarmConsolidated.OperatorName AS nVarChar(131)) AS Operator, CAST(OperatorDetails.UserDomainName AS nVarChar(155)) AS DomainName,
CAST(OperatorDetails.UserFullName AS nVarChar(255)) AS UserFullName, replicate(' ', 17) AS UnAckDuration, AlarmConsolidated.User1 AS User1,
AlarmConsolidated.User2 AS User2, CAST(Comment.Comment AS nVarChar(131)) AS User3, AlarmConsolidated.AlarmTime AS EventStampUTC,
AlarmConsolidated.AlarmTimeFracSec AS MilliSec, CAST(AlarmConsolidated.OperatorNode AS nVarChar(32)) AS OperatorNode
FROM AlarmMaster INNER JOIN
AlarmConsolidated ON AlarmMaster.AlarmId = AlarmConsolidated.AlarmId INNER JOIN
ProviderSession ON AlarmMaster.ProviderId = ProviderSession.ProviderId LEFT OUTER JOIN
Comment ON AlarmConsolidated.CommentId = Comment.CommentId LEFT OUTER JOIN
OperatorDetails ON AlarmConsolidated.OperatorID = OperatorDetails.OperatorID
WHERE AlarmConsolidated.AlarmTime <> '9999-12-12 23:59:59.997' AND AlarmConsolidated.AlarmTime <> AlarmMaster.OriginationTime
UNION
SELECT DATEADD(mi, AlarmConsolidated.AckTimeZoneOffset - (AlarmConsolidated.AckDaylightAdjustment * 7.5), AlarmConsolidated.AckTime) AS EventStamp,
CAST('ACK_ALM' AS nVarChar(9)) AS AlarmState, CAST(AlarmMaster.TagName AS nVarChar(132)) AS TagName, CAST(Comment.Comment AS nVarChar(255))
AS Description, CAST(AlarmMaster.GroupName AS nVarChar(32)) AS Area, CAST(AlarmConsolidated.AlarmType AS nVarChar(6)) AS Type,
AlarmConsolidated.ValueString AS Value, AlarmConsolidated.LimitString AS CheckValue, AlarmConsolidated.Priority, CAST(AlarmMaster.AlarmClass AS nVarChar(5))
AS Category, CAST((RTRIM(ProviderSession.NodeName) + '\' + RTRIM(ProviderSession.ProviderType)) AS nVarChar(65)) AS Provider,
CAST(AlarmConsolidated.AckOperatorName AS nVarChar(131)) AS Operator, CAST(OperatorDetails.UserDomainName AS nVarChar(155)) AS DomainName,
CAST(OperatorDetails.UserFullName AS nVarChar(255)) AS UserFullName, CAST(AlarmConsolidated.UnAckDuration AS nVarChar(17)) AS UnAckDuration,
AlarmConsolidated.User1 AS User1, AlarmConsolidated.User2 AS User2, CAST(Comment1.Comment AS nVarChar(131)) AS User3,
AlarmConsolidated.AckTime AS EventStampUTC, AlarmConsolidated.AckTimeFracSec AS MilliSec, CAST(AlarmConsolidated.OperatorNode AS nVarChar(32))
AS OperatorNode
FROM AlarmMaster INNER JOIN
AlarmConsolidated ON AlarmMaster.AlarmId = AlarmConsolidated.AlarmId INNER JOIN
ProviderSession ON AlarmMaster.ProviderId = ProviderSession.ProviderId LEFT OUTER JOIN
Comment ON AlarmConsolidated.AckCommentId = Comment.CommentId LEFT OUTER JOIN
Comment AS Comment1 ON AlarmConsolidated.CommentId = Comment1.CommentId LEFT OUTER JOIN
OperatorDetails ON AlarmConsolidated.AckOperatorID = OperatorDetails.OperatorID
WHERE AlarmConsolidated.AckTime <> '9999-12-12 23:59:59.997' AND AlarmConsolidated.AckTime < AlarmConsolidated.ReturnTime AND
(AlarmConsolidated.AlarmTime = '9999-12-12 23:59:59.997' OR
AlarmConsolidated.AckTime > AlarmConsolidated.AlarmTime)
UNION
SELECT DATEADD(mi, AlarmConsolidated.AckTimeZoneOffset - (AlarmConsolidated.AckDaylightAdjustment * 7.5), AlarmConsolidated.AckTime) AS EventStamp,
CAST('ACK' AS nVarChar(9)) AS AlarmState, CAST(AlarmMaster.TagName AS nVarChar(132)) AS TagName, CAST(Comment.Comment AS nVarChar(255)) AS Description,
CAST(AlarmMaster.GroupName AS nVarChar(32)) AS Area, CAST(AlarmConsolidated.AlarmType AS nVarChar(6)) AS Type,
AlarmConsolidated.ReturnValueString AS Value, AlarmConsolidated.LimitString AS CheckValue, AlarmConsolidated.Priority,
CAST(AlarmMaster.AlarmClass AS nVarChar(5)) AS Category, CAST((RTRIM(ProviderSession.NodeName) + '\' + RTRIM(ProviderSession.ProviderType))
AS nVarChar(65)) AS Provider, CAST(AlarmConsolidated.AckOperatorName AS nVarChar(131)) AS Operator, CAST(OperatorDetails.UserDomainName AS nVarChar(155))
AS DomainName, CAST(OperatorDetails.UserFullName AS nVarChar(255)) AS UserFullName, CAST(AlarmConsolidated.UnAckDuration AS nVarChar(17))
AS UnAckDuration, AlarmConsolidated.User1 AS User1, AlarmConsolidated.User2 AS User2, CAST(Comment1.Comment AS nVarChar(131)) AS User3,
AlarmConsolidated.AckTime AS EventStampUTC, AlarmConsolidated.AckTimeFracSec AS MilliSec, CAST(AlarmConsolidated.OperatorNode AS nVarChar(32))
AS OperatorNode
FROM AlarmMaster INNER JOIN
AlarmConsolidated ON AlarmMaster.AlarmId = AlarmConsolidated.AlarmId INNER JOIN
ProviderSession ON AlarmMaster.ProviderId = ProviderSession.ProviderId LEFT OUTER JOIN
Comment ON AlarmConsolidated.AckCommentId = Comment.CommentId LEFT OUTER JOIN
Comment AS Comment1 ON AlarmConsolidated.CommentId = Comment1.CommentId LEFT OUTER JOIN
OperatorDetails ON AlarmConsolidated.AckOperatorID = OperatorDetails.OperatorID
WHERE AlarmConsolidated.AckTime <> '9999-12-12 23:59:59.997' AND AlarmConsolidated.AckTime = AlarmConsolidated.ReturnTime
UNION
SELECT DATEADD(mi, AlarmConsolidated.ReturnTimeZoneOffset - (AlarmConsolidated.ReturnDaylightAdjustment * 7.5), AlarmConsolidated.ReturnTime) AS EventStamp,
CAST('ACK_RTN' AS nVarChar(9)) AS AlarmState, CAST(AlarmMaster.TagName AS nVarChar(132)) AS TagName, CAST(Comment.Comment AS nVarChar(255))
AS Description, CAST(AlarmMaster.GroupName AS nVarChar(32)) AS Area, CAST(AlarmConsolidated.AlarmType AS nVarChar(6)) AS Type,
AlarmConsolidated.ReturnValueString AS Value, AlarmConsolidated.LimitString AS CheckValue, AlarmConsolidated.Priority,
CAST(AlarmMaster.AlarmClass AS nVarChar(5)) AS Category, CAST((RTRIM(ProviderSession.NodeName) + '\' + RTRIM(ProviderSession.ProviderType))
AS nVarChar(65)) AS Provider, CAST(AlarmConsolidated.AckOperatorName AS nVarChar(131)) AS Operator, CAST(OperatorDetails.UserDomainName AS nVarChar(155))
AS DomainName, CAST(OperatorDetails.UserFullName AS nVarChar(255)) AS UserFullName, replicate(' ', 17) AS UnAckDuration, AlarmConsolidated.User1 AS User1,
AlarmConsolidated.User2 AS User2, CAST(Comment1.Comment AS nVarChar(131)) AS User3, AlarmConsolidated.ReturnTime AS EventStampUTC,
AlarmConsolidated.ReturnTimeFracSec AS MilliSec, CAST(AlarmConsolidated.OperatorNode AS nVarChar(32)) AS OperatorNode
FROM AlarmMaster INNER JOIN
AlarmConsolidated ON AlarmMaster.AlarmId = AlarmConsolidated.AlarmId INNER JOIN
ProviderSession ON AlarmMaster.ProviderId = ProviderSession.ProviderId LEFT OUTER JOIN
Comment ON AlarmConsolidated.AckCommentId = Comment.CommentId LEFT OUTER JOIN
Comment AS Comment1 ON AlarmConsolidated.CommentId = Comment1.CommentId LEFT OUTER JOIN
OperatorDetails ON AlarmConsolidated.AckOperatorID = OperatorDetails.OperatorID
WHERE dbo.AlarmConsolidated.AckTime <> '9999-12-12 23:59:59.997' AND dbo.AlarmConsolidated.ReturnTime <> '9999-12-12 23:59:59.997' AND
AlarmMaster.bActive <> 1 AND dbo.AlarmConsolidated.AckTime <= dbo.AlarmConsolidated.ReturnTime AND
(dbo.AlarmConsolidated.AlarmTime = '9999-12-12 23:59:59.997' OR
dbo.AlarmConsolidated.AckTime > dbo.AlarmConsolidated.AlarmTime)
UNION
SELECT DATEADD(mi, AlarmConsolidated.AckTimeZoneOffset - (AlarmConsolidated.AckDaylightAdjustment * 7.5), AlarmConsolidated.AckTime) AS EventStamp,
CAST('ACK_RTN' AS nVarChar(9)) AS AlarmState, CAST(AlarmMaster.TagName AS nVarChar(132)) AS TagName, CAST(Comment.Comment AS nVarChar(255))
AS Description, CAST(AlarmMaster.GroupName AS nVarChar(32)) AS Area, CAST(AlarmConsolidated.AlarmType AS nVarChar(6)) AS Type,
AlarmConsolidated.ReturnValueString AS Value, AlarmConsolidated.LimitString AS CheckValue, AlarmConsolidated.Priority,
CAST(AlarmMaster.AlarmClass AS nVarChar(5)) AS Category, CAST((RTRIM(ProviderSession.NodeName) + '\' + RTRIM(ProviderSession.ProviderType))
AS nVarChar(65)) AS Provider, CAST(AlarmConsolidated.AckOperatorName AS nVarChar(131)) AS Operator, CAST(OperatorDetails.UserDomainName AS nVarChar(155))
AS DomainName, CAST(OperatorDetails.UserFullName AS nVarChar(255)) AS UserFullName, CAST(AlarmConsolidated.UnAckDuration AS nVarChar(17))
AS UnAckDuration, AlarmConsolidated.User1 AS User1, AlarmConsolidated.User2 AS User2, CAST(Comment1.Comment AS nVarChar(131)) AS User3,
AlarmConsolidated.AckTime AS EventStampUTC, AlarmConsolidated.AckTimeFracSec AS MilliSec, CAST(AlarmConsolidated.OperatorNode AS nVarChar(32))
AS OperatorNode
FROM AlarmMaster INNER JOIN
AlarmConsolidated ON AlarmMaster.AlarmId = AlarmConsolidated.AlarmId INNER JOIN
ProviderSession ON AlarmMaster.ProviderId = ProviderSession.ProviderId LEFT OUTER JOIN
Comment ON AlarmConsolidated.AckCommentId = Comment.CommentId LEFT OUTER JOIN
Comment AS Comment1 ON AlarmConsolidated.CommentId = Comment1.CommentId LEFT OUTER JOIN
OperatorDetails ON AlarmConsolidated.AckOperatorID = OperatorDetails.OperatorID
WHERE dbo.AlarmConsolidated.AckTime <> '9999-12-12 23:59:59.997' AND dbo.AlarmConsolidated.ReturnTime <> '9999-12-12 23:59:59.997' AND
AlarmMaster.bActive <> 1 AND dbo.AlarmConsolidated.AckTime > dbo.AlarmConsolidated.ReturnTime
UNION
SELECT DATEADD(mi, AlarmConsolidated.ReturnTimeZoneOffset - (AlarmConsolidated.ReturnDaylightAdjustment * 7.5), AlarmConsolidated.ReturnTime) AS EventStamp,
CAST('UNACK_RTN' AS nVarChar(9)) AS AlarmState, CAST(AlarmMaster.TagName AS nVarChar(132)) AS TagName, CAST(Comment.Comment AS nVarChar(255))
AS Description, CAST(AlarmMaster.GroupName AS nVarChar(32)) AS Area, CAST(AlarmConsolidated.AlarmType AS nVarChar(6)) AS Type,
AlarmConsolidated.ReturnValueString AS Value, AlarmConsolidated.LimitString AS CheckValue, AlarmConsolidated.Priority,
CAST(AlarmMaster.AlarmClass AS nVarChar(5)) AS Category, CAST((RTRIM(ProviderSession.NodeName) + '\' + RTRIM(ProviderSession.ProviderType))
AS nVarChar(65)) AS Provider, CAST(AlarmConsolidated.OperatorName AS nVarChar(131)) AS Operator, CAST(OperatorDetails.UserDomainName AS nVarChar(155))
AS DomainName, CAST(OperatorDetails.UserFullName AS nVarChar(255)) AS UserFullName, replicate(' ', 17) AS UnAckDuration, AlarmConsolidated.User1 AS User1,
AlarmConsolidated.User2 AS User2, CAST(Comment.Comment AS nVarChar(131)) AS User3, AlarmConsolidated.ReturnTime AS EventStampUTC,
AlarmConsolidated.ReturnTimeFracSec AS MilliSec, CAST(AlarmConsolidated.OperatorNode AS nVarChar(32)) AS OperatorNode
FROM AlarmMaster INNER JOIN
AlarmConsolidated ON AlarmMaster.AlarmId = AlarmConsolidated.AlarmId INNER JOIN
ProviderSession ON AlarmMaster.ProviderId = ProviderSession.ProviderId LEFT OUTER JOIN
Comment ON AlarmConsolidated.CommentId = Comment.CommentId LEFT OUTER JOIN
OperatorDetails ON AlarmConsolidated.OperatorID = OperatorDetails.OperatorID
WHERE dbo.AlarmConsolidated.ReturnTime <> '9999-12-12 23:59:59.997' AND (dbo.AlarmConsolidated.AckTime = '9999-12-12 23:59:59.997' OR
dbo.AlarmConsolidated.AckTime > dbo.AlarmConsolidated.ReturnTime OR
(dbo.AlarmConsolidated.AlarmTime <> '9999-12-12 23:59:59.997' AND dbo.AlarmConsolidated.AckTime < dbo.AlarmConsolidated.AlarmTime))
UNION ALL
SELECT DATEADD(mi, Events.EventTimeZoneOffset - (Events.EventDaylightAdjustment * 7.5), Events.EventTime) AS EventStamp, CAST(Events.EventState AS nVarChar(9))
AS AlarmState, CAST(Events.TagName AS nVarChar(132)) AS TagName, CAST(Events.Comment AS nVarChar(255)) AS Description,
CAST(Events.GroupName AS nVarChar(32)) AS Area, CAST(Events.EventType AS nVarChar(4)) AS Type, Events.ValueString AS Value,
Events.LimitString AS CheckValue, Events.EventPriority AS Priority, CAST(Events.EventClass AS nVarChar(8)) AS Category, CAST(RTRIM(NodeName)
+ '\' + RTRIM(ProviderType) AS nVarChar(65)) AS Provider, CAST(Events.OperatorName AS nVarChar(131)) AS Operator,
CAST(OperatorDetails.UserDomainName AS nVarChar(155)) AS DomainName, CAST(OperatorDetails.UserFullName AS nVarChar(255)) AS UserFullName, replicate(' ',
17) AS UnAckDuration, Events.User1 AS User1, Events.User2 AS User2, CAST(Events.Comment AS nVarChar(131)) AS User3, Events.EventTime AS EventStampUTC,
Events.EventTimeFracSec AS MilliSec, CAST(Events.OperatorNode AS nVarChar(32)) AS OperatorNode
FROM Events INNER JOIN
ProviderSession ON Events.ProviderId = ProviderSession.ProviderId LEFT OUTER JOIN
OperatorDetails ON Events.OperatorID = OperatorDetails.OperatorID