Is there a way to audit component creation in a template?

We have several dozen servers running large 50+ component count templates updated by maybe 30 people in the NOC on a regular basis as new URLs need to be added and old ones are removed from monitoring. Mainly these are watching a couple of HTTP/HTTPS checks or running a specific SQL query per site database and comparing responses. I need to break these down into more manageable templates applied to external nodes or something but for now I need to deal with them

Currently we can see who made a change to a template and when but we can't tell what components specifically they changed.

Is there any way to audit for component changes in an application?

Thanks

-Jim

Find more posts tagged with

sam 6.6

audit event

application template

Accepted answers

frak

Okay this got quite complex. We have a need for this here, so I could spend the time and build/test it. This audits the component template settings, and not the app template settings. Similiar work would be done to extend it though - ie another similiar trigger.

There are a number of unusual things as to how Solarwinds writes its data, that had to be handled - see the comments in the code. The biggest, updates (for component templates at least) updates are done as insert and delete operations. So work needs to be done to convert ins/del into an update audit log, and to only log actual changes.

For script monitors (and possibly others I have not handled) the actual script is written into another table, and an ID stored as the 'value'. When the component is saved, the associated record is deleted and re-created, and so is the ID. When the APM_ComponentTemplateSetting record for that script is deleted, the associated record is already gone, so not possible to get the original script for comparison, when working in a trigger on the templatesetting table.

My Solarwinds database is SolarWindsOrion, and I have another database called SolarWindsAux for most customisation work. To use this you need to change these values.

USE SolarwindsAuxgoCREATE TABLE [dbo].APM_ComponentTemplateSetting_AUD(  APM_ComponentTemplateSetting_AUD_ID bigint NOT NULL IDENTITY,Operation char(1),TRANSACTION_ID bigint,ComponentTemplateID [bigint] NOT NULL,  TimeLoggedUtc datetime NOT NULL,  [Key] [varchar](50) NOT NULL,  [OldValue] [nvarchar](max)  NULL,  [Value] [nvarchar](max)  NULL,  AuditEventID bigint nullCONSTRAINT [PK_APM_ApplicationTemplateSetting_AUD] PRIMARY KEY CLUSTERED ( APM_ComponentTemplateSetting_AUD_ID )   )  CREATE INDEX I_APM_ComponentTemplateSetting_AUD_UTC on [dbo].APM_ComponentTemplateSetting_AUD ( TimeLoggedUtc )use SolarWindsOriongo  CREATE TRIGGER trg_APM_ComponentTemplateSetting_AUD on [dbo].[APM_ComponentTemplateSetting]     AFTER INSERT,DELETE,UPDATE  AS   --Mathew Frank  2018-09-20--v0.1--https://thwack.solarwinds.com/people/frakBEGIN  --prevent recursionIF ( (SELECT TRIGGER_NESTLEVEL( OBJECT_ID('trg_APM_ComponentTemplateSetting_AUD') , 'AFTER' , 'DML' ) ) > 1 ) RETURN;--SET NOCOUNT ON;   declare @UTC datetime = GETUTCDATE()declare @op char(1)--check only 10m in the past for lookup of current transaction audit recorsdeclare @UTC_Window datetime = dateadd(mi,-10, @UTC ) if exists(SELECT 1 from inserted) and exists (SELECT 1 from deleted)  begin  set @op = 'U'--update.  Never really happens given the INS/DEL process for updates  INSERT into SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD (ComponentTemplateID, TimeLoggedUtc, [Key], OldValue, Value, operation)   SELECT i.ComponentTemplateID, @UTC /*GETUTCDATE()*/, i.[Key]--these inlines are to avoid data conversion killing index use (with LEFT joins and I,case when D.[key] = 'ScriptBody' then (SELECT TOP 1 Setting FROM APM_ExternalSetting WHERE ID = cast(D.Value as bigint) )else D.Value end,case when I.[key] = 'ScriptBody' then (SELECT TOP 1 Setting FROM APM_ExternalSetting WHERE ID = cast(I.Value as bigint) )else I.Value end, @op from inserted i  inner join deleted d  on i.ComponentTemplateID = d.ComponentTemplateID  and i.[Key] = d.[Key]end    If exists (Select 1 from inserted) and not exists(Select 1 from deleted)  begin  set @op = 'I'INSERT into SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD (ComponentTemplateID, TimeLoggedUtc, [Key], OldValue, Value, Operation, TRANSACTION_ID)   SELECT i.ComponentTemplateID, @UTC /*GETUTCDATE()*/, i.[Key], NULL,case when I.[key] = 'ScriptBody' then (SELECT TOP 1 Setting FROM APM_ExternalSetting WHERE ID = cast(I.Value as bigint) )else I.Value end, @op, CURRENT_TRANSACTION_ID()   from inserted i  end      If exists(select 1 from deleted) and not exists(Select 1 from inserted)  begin   set @op = 'D'INSERT into SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD (ComponentTemplateID, TimeLoggedUtc, [Key], OldValue, Value, Operation, TRANSACTION_ID)   SELECT d.ComponentTemplateID, @UTC /*GETUTCDATE()*/, d.[Key],case when D.[key] = 'ScriptBody' then (SELECT TOP 1 Setting FROM APM_ExternalSetting WHERE ID = cast(D.Value as bigint) )else D.Value end, NULL, @op , CURRENT_TRANSACTION_ID() from deleted d  END  --SW logs changes as seperate ins and del operations.  Detect these, compress the info into updates with just the changes.  Step 1 delete non-changes-- updated record in this trigger.--for speed only checks last 10 minutes  (that is a huge transaction time, but being parnoid...)--problem:  in these quasi updates, SW inserts all records in one operation, then deletes the records one at a time.--to process these we need to wait till the inserts are finished - ie there are an equal number of deletes and inserts for this transaction.if (@op = 'I')BEGINif exists (select 1FROM SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD aWHERE TimeLoggedUtc > @UTC_WindowAND transaction_id = CURRENT_TRANSACTION_ID() HAVING sum(case when a.Operation = 'I' then 1 else 0 end) = sum(case when a.Operation = 'D' then 1 else 0 end))BEGIN--delete non-changesDELETE FROM SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUDWHERE TimeLoggedUtc > @UTC_WindowAND EXISTS (select ComponentTemplateID, TRANSACTION_ID,  [Key]from SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD  cwhere TimeLoggedUtc > @UTC_WindowAND c.transaction_id = CURRENT_TRANSACTION_ID() AND c.ComponentTemplateID = APM_ComponentTemplateSetting_AUD.ComponentTemplateIDAND c.TRANSACTION_ID = APM_ComponentTemplateSetting_AUD.TRANSACTION_IDand c.[key] = APM_ComponentTemplateSetting_AUD.[key]group by c.ComponentTemplateID, c.TRANSACTION_ID, c.[Key]having  max(c.oldValue) = max(c.Value))--compress remaining split updates into one (if there are any left)UPDATE dstSET dst.OldValue = src.OldValue, TRANSACTION_ID = NULL --may as well clear this as it is useless. , Operation = 'U'FROM  SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD Dstinner join SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD srcon src.TimeLoggedUtc > @UTC_WindowAND src.ComponentTemplateID = dst.ComponentTemplateIDAND src.[key] = dst.[key]AND src.transaction_id = dst.transaction_idAND src.OldValue is not nullWHERE dst.TimeLoggedUtc > @UTC_WindowAND dst.transaction_id = CURRENT_TRANSACTION_ID() AND Dst.Value is not null----delete remaing (rubbish) rowDELETE FROM SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUDWHERE TimeLoggedUtc > @UTC_WindowAND transaction_id = CURRENT_TRANSACTION_ID() AND Value is nullprint 'DONE'ENDENDEND

An example of the resulting log (as stated above, script values will always come up as 'inserts' due to the oldvalue always being NULL. This could be solved by a trigger on the associated table.

Lastly, the audit record is written last for the SolarWindsOrion.dbo.AuditingEvents table, so the timestamps for audit records created by this script are a few microseconds before those. You will see my audit table does have an AuditEventID column. This would need to be populated by a trigger on SolarWindsOrion.dbo.AuditingEvents.

I will probably add to this in another article - when I do I will link it from here.

All comments

frak

If you want to know what somebody changed, then put a database trigger on [dbo].[APM_ComponentTemplateSetting] and log the changes to another table, including the timestamp in UTC. You can then line that up with entries in AuditingEvents to determine who did it.

This trigger for example:

CREATE TABLE [dbo].APM_ComponentTemplateSetting_AUD(
[ApplicationTemplateID] [bigint] NOT NULL,
TimeLoggedUtc datetime NOT NULL,
[Key] [varchar](50) NOT NULL,
[OldValue] [nvarchar](max) NOT NULL,
[Value] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_APM_ApplicationTemplateSetting] PRIMARY KEY CLUSTERED ( [ApplicationTemplateID] ASC, TimeLoggedUtc ASC, [Key] ASC) 
)


go



CREATE TRIGGER trg_APM_ComponentTemplateSetting_AUD on [dbo].[APM_ComponentTemplateSetting]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
SET NOCOUNT ON;



if exists(SELECT 1 from inserted) and exists (SELECT 1 from deleted)
begin
--update
INSERT into APM_ComponentTemplateSetting_AUD (ComponentTemplateID, TimeLoggedUtc, [Key], OldValue, Value) 
SELECT i.ComponentTemplateID, GETUTCDATE(), i.[Key], d.Value, i.Value 
from inserted i
inner join deleted d
on i.ComponentTemplateID = d.ComponentTemplateID
and i.[Key] = d.[Key]
end


If exists (Select 1 from inserted) and not exists(Select 1 from deleted)
begin
INSERT into APM_ComponentTemplateSetting_AUD (ComponentTemplateID, TimeLoggedUtc, [Key], OldValue, Value) 
SELECT i.ComponentTemplateID, GETUTCDATE(), i.[Key], NULL, i.Value 
from inserted i
end


If exists(select 1 from deleted) and not exists(Select 1 from inserted)
begin 
INSERT into APM_ComponentTemplateSetting_AUD (ComponentTemplateID, TimeLoggedUtc, [Key], OldValue, Value) 
SELECT d.ComponentTemplateID, GETUTCDATE(), d.[Key], d.Value, NULL
from deleted d
END
END

frak

The above produces a lot of noise on an 'update' because solarwinds does a delete and then an insert instead. So for a template you get all values deleted, then all values inserted and that creates a lot of pointless audit.

I have a slightly complicated way of cutting that down to just a row for each updated value. Hope to post it soon - would presumably be a method to apply to any additional auditing in Solarwinds.

frak

My Solarwinds database is SolarWindsOrion, and I have another database called SolarWindsAux for most customisation work. To use this you need to change these values.

USE SolarwindsAuxgoCREATE TABLE [dbo].APM_ComponentTemplateSetting_AUD(  APM_ComponentTemplateSetting_AUD_ID bigint NOT NULL IDENTITY,Operation char(1),TRANSACTION_ID bigint,ComponentTemplateID [bigint] NOT NULL,  TimeLoggedUtc datetime NOT NULL,  [Key] [varchar](50) NOT NULL,  [OldValue] [nvarchar](max)  NULL,  [Value] [nvarchar](max)  NULL,  AuditEventID bigint nullCONSTRAINT [PK_APM_ApplicationTemplateSetting_AUD] PRIMARY KEY CLUSTERED ( APM_ComponentTemplateSetting_AUD_ID )   )  CREATE INDEX I_APM_ComponentTemplateSetting_AUD_UTC on [dbo].APM_ComponentTemplateSetting_AUD ( TimeLoggedUtc )use SolarWindsOriongo  CREATE TRIGGER trg_APM_ComponentTemplateSetting_AUD on [dbo].[APM_ComponentTemplateSetting]     AFTER INSERT,DELETE,UPDATE  AS   --Mathew Frank  2018-09-20--v0.1--https://thwack.solarwinds.com/people/frakBEGIN  --prevent recursionIF ( (SELECT TRIGGER_NESTLEVEL( OBJECT_ID('trg_APM_ComponentTemplateSetting_AUD') , 'AFTER' , 'DML' ) ) > 1 ) RETURN;--SET NOCOUNT ON;   declare @UTC datetime = GETUTCDATE()declare @op char(1)--check only 10m in the past for lookup of current transaction audit recorsdeclare @UTC_Window datetime = dateadd(mi,-10, @UTC ) if exists(SELECT 1 from inserted) and exists (SELECT 1 from deleted)  begin  set @op = 'U'--update.  Never really happens given the INS/DEL process for updates  INSERT into SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD (ComponentTemplateID, TimeLoggedUtc, [Key], OldValue, Value, operation)   SELECT i.ComponentTemplateID, @UTC /*GETUTCDATE()*/, i.[Key]--these inlines are to avoid data conversion killing index use (with LEFT joins and I,case when D.[key] = 'ScriptBody' then (SELECT TOP 1 Setting FROM APM_ExternalSetting WHERE ID = cast(D.Value as bigint) )else D.Value end,case when I.[key] = 'ScriptBody' then (SELECT TOP 1 Setting FROM APM_ExternalSetting WHERE ID = cast(I.Value as bigint) )else I.Value end, @op from inserted i  inner join deleted d  on i.ComponentTemplateID = d.ComponentTemplateID  and i.[Key] = d.[Key]end    If exists (Select 1 from inserted) and not exists(Select 1 from deleted)  begin  set @op = 'I'INSERT into SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD (ComponentTemplateID, TimeLoggedUtc, [Key], OldValue, Value, Operation, TRANSACTION_ID)   SELECT i.ComponentTemplateID, @UTC /*GETUTCDATE()*/, i.[Key], NULL,case when I.[key] = 'ScriptBody' then (SELECT TOP 1 Setting FROM APM_ExternalSetting WHERE ID = cast(I.Value as bigint) )else I.Value end, @op, CURRENT_TRANSACTION_ID()   from inserted i  end      If exists(select 1 from deleted) and not exists(Select 1 from inserted)  begin   set @op = 'D'INSERT into SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD (ComponentTemplateID, TimeLoggedUtc, [Key], OldValue, Value, Operation, TRANSACTION_ID)   SELECT d.ComponentTemplateID, @UTC /*GETUTCDATE()*/, d.[Key],case when D.[key] = 'ScriptBody' then (SELECT TOP 1 Setting FROM APM_ExternalSetting WHERE ID = cast(D.Value as bigint) )else D.Value end, NULL, @op , CURRENT_TRANSACTION_ID() from deleted d  END  --SW logs changes as seperate ins and del operations.  Detect these, compress the info into updates with just the changes.  Step 1 delete non-changes-- updated record in this trigger.--for speed only checks last 10 minutes  (that is a huge transaction time, but being parnoid...)--problem:  in these quasi updates, SW inserts all records in one operation, then deletes the records one at a time.--to process these we need to wait till the inserts are finished - ie there are an equal number of deletes and inserts for this transaction.if (@op = 'I')BEGINif exists (select 1FROM SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD aWHERE TimeLoggedUtc > @UTC_WindowAND transaction_id = CURRENT_TRANSACTION_ID() HAVING sum(case when a.Operation = 'I' then 1 else 0 end) = sum(case when a.Operation = 'D' then 1 else 0 end))BEGIN--delete non-changesDELETE FROM SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUDWHERE TimeLoggedUtc > @UTC_WindowAND EXISTS (select ComponentTemplateID, TRANSACTION_ID,  [Key]from SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD  cwhere TimeLoggedUtc > @UTC_WindowAND c.transaction_id = CURRENT_TRANSACTION_ID() AND c.ComponentTemplateID = APM_ComponentTemplateSetting_AUD.ComponentTemplateIDAND c.TRANSACTION_ID = APM_ComponentTemplateSetting_AUD.TRANSACTION_IDand c.[key] = APM_ComponentTemplateSetting_AUD.[key]group by c.ComponentTemplateID, c.TRANSACTION_ID, c.[Key]having  max(c.oldValue) = max(c.Value))--compress remaining split updates into one (if there are any left)UPDATE dstSET dst.OldValue = src.OldValue, TRANSACTION_ID = NULL --may as well clear this as it is useless. , Operation = 'U'FROM  SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD Dstinner join SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD srcon src.TimeLoggedUtc > @UTC_WindowAND src.ComponentTemplateID = dst.ComponentTemplateIDAND src.[key] = dst.[key]AND src.transaction_id = dst.transaction_idAND src.OldValue is not nullWHERE dst.TimeLoggedUtc > @UTC_WindowAND dst.transaction_id = CURRENT_TRANSACTION_ID() AND Dst.Value is not null----delete remaing (rubbish) rowDELETE FROM SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUDWHERE TimeLoggedUtc > @UTC_WindowAND transaction_id = CURRENT_TRANSACTION_ID() AND Value is nullprint 'DONE'ENDENDEND

An example of the resulting log (as stated above, script values will always come up as 'inserts' due to the oldvalue always being NULL. This could be solved by a trigger on the associated table.

I will probably add to this in another article - when I do I will link it from here.

jburke

Wow, you put in a lot of time on this one. I didn't realize this would be as big an issue as it turned into

Your time and effort are greatly appreciated.

-Jim