5 Replies Latest reply on Sep 20, 2018 6:14 PM by frak

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

    jburke

      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

        • Re: Is there a way to audit component creation in a template?
          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
          2 of 2 people found this helpful
            • Re: Is there a way to audit component creation in a template?
              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.

                • Re: Is there a way to audit component creation in a template?
                  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 SolarwindsAux
                  go
                  
                  CREATE 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 null
                  CONSTRAINT [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 SolarWindsOrion
                  go  
                  
                  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/frak
                  
                  
                  BEGIN  
                  --prevent recursion
                  IF ( (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 recors
                  declare @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')
                  BEGIN
                  if exists (
                  select 1
                  FROM SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD a
                  WHERE TimeLoggedUtc > @UTC_Window
                  AND 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-changes
                  DELETE FROM SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD
                  WHERE TimeLoggedUtc > @UTC_Window
                  AND EXISTS (
                  select ComponentTemplateID, TRANSACTION_ID,  [Key]
                  from SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD  c
                  where TimeLoggedUtc > @UTC_Window
                  AND c.transaction_id = CURRENT_TRANSACTION_ID() 
                  AND c.ComponentTemplateID = APM_ComponentTemplateSetting_AUD.ComponentTemplateID
                  AND c.TRANSACTION_ID = APM_ComponentTemplateSetting_AUD.TRANSACTION_ID
                  and 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 dst
                  SET dst.OldValue = src.OldValue
                  , TRANSACTION_ID = NULL --may as well clear this as it is useless. 
                  , Operation = 'U'
                  FROM  SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD Dst
                  inner join SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD src
                  on src.TimeLoggedUtc > @UTC_Window
                  AND src.ComponentTemplateID = dst.ComponentTemplateID
                  AND src.[key] = dst.[key]
                  AND src.transaction_id = dst.transaction_id
                  AND src.OldValue is not null
                  WHERE dst.TimeLoggedUtc > @UTC_Window
                  AND dst.transaction_id = CURRENT_TRANSACTION_ID() 
                  AND Dst.Value is not null
                  
                  
                  ----delete remaing (rubbish) row
                  DELETE FROM SolarWindsAux.dbo.APM_ComponentTemplateSetting_AUD
                  WHERE TimeLoggedUtc > @UTC_Window
                  AND transaction_id = CURRENT_TRANSACTION_ID() 
                  AND Value is null
                  print 'DONE'
                  END
                  END
                  END  
                  

                   

                  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.

                    • Re: Is there a way to audit component creation in a template?
                      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

                        • Re: Is there a way to audit component creation in a template?
                          frak

                          Maybe I jinxed myself by saying it was easy in the first place lol.  I did gain a lot of understanding - like why making a minor change and pressing [save] takes so long on the SAM Template screen.

                           

                          Anyway the amount of work/logic required is why I plan to build more of a suite of additional auditing.  What comes next logically would be:

                          - Template header info changes

                          - App monitor (and header) changes

                          - audit dbo.APM_ExternalSetting table so script changes can be detected properly - as it stands any change to a template logs an 'insert' for the entire script, which is quite the waste of storage.

                          - add the dbo.AuditingEvents ID to the dbo.APM_ComponentTemplateSetting_AUD records - by trigger on AuditingEvents or by a regular SQL job.

                          - a View to combine the standard auditing data with this new auditing data

                          - a procedure to dump the data for Splunk consumption  (this is the context in which I am able to do this work - security auditing)

                           

                          If there is more people may want, please reply here and I will add it to my list for when I get around to it.

                           

                          Mathew