DX Infrastructure Manager

Expand all | Collapse all

Alert for Database deletion through SQLserver probe

  • 1.  Alert for Database deletion through SQLserver probe

    Posted 03-21-2019 10:40 AM

    Hi All,

     

    In our environment we got a requirement to trigger an alarm when the Database in deleted from the SQL server.

     

    will we be able to trigger an alarm by using SQLserver probe.

     

    At present we are using CA UIM 8.51 version and SQLserver 5.10 version.

     

    Regards,

    Sharmila.



  • 2.  Re: Alert for Database deletion through SQLserver probe

    Posted 03-25-2019 02:56 PM

    Perhaps one of the built in checkpoints can fulfill the need such as:

    QOS_SQLSERVER_database_count

    QOS_SQLSERVER_database_state

    sqlserver Metrics - CA Unified Infrastructure Management Probes - CA Technologies Documentation 

    If not then check out:

    How to create a new (custom) checkpoint using the - CA Knowledge 



  • 3.  Re: Alert for Database deletion through SQLserver probe

    Posted 03-25-2019 04:58 PM

    You could use a query like this and edit it to be more specific if required. I just added a new database and then deleted it to test it and the entry is picked up in the trace events.

     

    With cteObjectTypes AS
    (
    SELECT
    TSV.trace_event_id,
    TSV.subclass_name,
    TSV.subclass_value
    FROM
    sys.trace_subclass_values AS TSV JOIN
    sys.trace_columns AS TC ON
    TSV.trace_column_id = TC.trace_column_id
    WHERE
    TC.[name] = 'ObjectType'
    ),
    cteEventSubClasses AS
    (
    SELECT
    TSV.trace_event_id,
    TSV.subclass_name,
    TSV.subclass_value
    FROM
    sys.trace_subclass_values AS TSV JOIN
    sys.trace_columns AS TC ON
    TSV.trace_column_id = TC.trace_column_id
    WHERE
    TC.[name] = 'EventSubClass'
    )
    SELECT
    TE.[name],
    I.ApplicationName,
    I.BigintData1,
    I.ClientProcessID,
    I.ColumnPermissions,
    I.DatabaseID,
    I.DatabaseName,
    I.DBUserName,
    I.Duration,
    I.EndTime,
    I.Error,
    I.EventSequence,
    Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
    I.FileName,
    I.HostName,
    I.IndexID,
    I.IntegerData,
    I.IsSystem,
    I.LineNumber,
    I.LoginName,
    I.LoginSid,
    I.NestLevel,
    I.NTDomainName,
    I.NTUserName,
    I.ObjectID,
    I.ObjectID2,
    I.ObjectName,
    Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
    I.OwnerName,
    I.ParentName,
    I.Permissions,
    I.RequestID,
    I.RoleName,
    I.ServerName,
    I.SessionLoginName,
    I.Severity,
    I.SPID,
    I.StartTime,
    I.State,
    I.Success,
    I.TargetLoginName,
    I.TargetLoginSid,
    I.TargetUserName,
    I.TextData,
    I.TransactionID,
    I.Type,
    I.XactSequence
    FROM
    sys.traces T CROSS Apply
    sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0
    THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
    ELSE T.[path]
    End, T.max_files) I JOIN
    sys.trace_events AS TE ON
    I.EventClass = TE.trace_event_id LEFT JOIN
    cteEventSubClasses AS ESC ON
    TE.trace_event_id = ESC.trace_event_id And
    I.EventSubClass = ESC.subclass_value LEFT JOIN
    cteObjectTypes AS OT ON
    TE.trace_event_id = OT.trace_event_id AND
    I.ObjectType = OT.subclass_value
    WHERE
    T.is_default = 1 AND
    TE.NAME = 'Object:Deleted'