Here is a short example how to generate a TicketID in Microsoft System Center Operations Manager 2007.

  • Open “Microsoft SQL Server Management Studio“
  • Search and expand the OperationsManager Database
  • Expand Tables-dbo.Alert and select Triggers
  • Right click on Triggers and select “New Trigger…“
  • Paste the following lines in the SQLquery window:
-- ====================================================================
-- File  : CreateTicketIdTrigger.SQL
-- Author  : Joerg Daehler
-- Create date  : 2008-09-09
-- Environment  : Microsoft System Center Operations Manager 2007SP1
-- Description  : Trigger for Table "Alert". Generates an increment
-- TicketID for new Alerts
-- =====================================================================

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Trigger_TicketId]
ON [dbo].[Alert]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @MY_ID int;
DECLARE @ALERTID uniqueidentifier;
DECLARE @TID nvarchar(150);

SELECT @ALERTID = inserted.AlertId From inserted;
IF @ALERTID IS NOT NULL
BEGIN
SELECT TOP 1 @TID = TicketId FROM Alert ORDER BY CAST(TicketId as int) DESC;
SET @MY_ID = CAST(@TID as int);

IF @TID IS NOT NULL
BEGIN
SET @MY_ID = @MY_ID + 1;
END ELSE
BEGIN
SET @MY_ID = 1;
END

SET @TID = CAST(@MY_ID as nvarchar(150));

UPDATE Alert SET TicketId = @TID WHERE AlertId = @ALERTID;

END
END
  • Execute this Script!