Alarm DB Logger: добавление пользовательских полей в базу

[кТуЛхУ]

Для идентификации событий/алармов логгер сохраняет в разные таблицы БД поля AlarmComment, TagName и пр. Поле AlarmComment после квитации перезаписывается соответствующим сообщением и получается, что в таблицах не остается описания тэга.
Может кто подскажет есть ли возможность сохранять в таблицу AlarmMaster еще и значение поля Comment тэга, которое в последствии не перезаписывается.

[Генератор зла]

Попробуйте создавать БД (в AlarmDBLogger) не Consolidated, а Detailed.

[кТуЛхУ]

Попробовал 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

[кТуЛхУ]

Оказывается в AlarmDbViewCtrl нельзя фильтровать по дате-времени (поле Time)!!!
Зато можно фильтровать по полям User1, User2. Добавил еще в таблицу в свободное поле User1 количество дней с 01/01/1970, чтобы можно было фильтровать по дате.

[NewOrdered]

Можно фильтровать по дате/времени
Используйте свойства
.StartTime, .EndTime, и .SpecificTime

[кТуЛхУ]

Спасибо. Так и сделал.
Может подскажете как организовать печать из объектов Archestra TrendClient и AlarmClient? Не могу никак найти есть такая возможность или нет.
Пока опять не начал велосипедить

[Генератор зла]

Спасибо. Так и сделал.
Может подскажете как организовать печать из объектов Archestra TrendClient и AlarmClient? Не могу никак найти есть такая возможность или нет.
Пока опять не начал велосипедить

Увы, только печать экрана (есть в InTouch такая ф-ция).
Либо используйте доп. компонент wwGenericSQLGrid, он поддерживает ф-цию печати содержимого.

[кТуЛхУ]

Эту штуку тоже пробовал. Брал отсюда.
Но он не пошел. Когда его бросаешь на экран выглядит просто как пустой прямоугольник и окно свойств пустое - только имя, координаты и GUID (скрин во вложении).
У меня установлен DevStudio 2014R2SP1 - ActiveX старый, возможно в этой версии уже не поддерживается.

[NewOrdered]

Свойства и методы wwGenericSQLGrid прекрасно дергаются в рантайме скриптами

[кТуЛхУ]

Свойства и методы wwGenericSQLGrid прекрасно дергаются в рантайме скриптами

До этого пока дело даже не доходит. Если обратите внимание, сам виджет на экране выглядит как пустой прямоугольник. Т.е. похоже, что ActiveX конфликтует с текущей версией интача.
P.S.: До этого пытался устанавливать вместо TrendClient разные варианты ActiveX трендов от WinCC. Так какой-то из вариантов также отобразился в виде пустого прямоугольника и после этого окно с этим объектом не мог ни открыть, ни удалить, ни запустить проект. InTouch просто зависал и убивался только из диспетчера задач, да и то не с первого раза. После этого решил не экспериментировать

[Генератор зла]

А как устанавливали wwGenericSQLGrid? Инсталлятором или ручками? Как он выглядит в панели инструментов (Wizards) можете показать?

[кТуЛхУ]

Как в описании: сначала установил из wwGenericDataGrid.msi, а потом зарегистрировал в WindowMaker через Configure/Wizard/ActiveX Installation.

[Генератор зла]

Как он выглядит в панели инструментов (Wizards) можете показать?