Blog Layout

SQL SERVER Audit Log HTML Report

E.DIN Editor • Apr 22, 2020
/***********************************************************************/
TRIGGER TO CREATE REPORT DATA

CREATE TRIGGER [dbo].[trgAdminFeesAudit] on [myDatabase].[dbo].[AdminFeesAudit]
FOR UPDATE, INSERT
AS
BEGIN
 /* TRIGGER TO LOG INCORRECT AdminType  */
DECLARE @AdminTypePrev varchar(20) = null;
IF EXISTS(SELECT TOP 1 AdminType FROM deleted) BEGIN
  SELECT @AdminTypePrev = AdminType FROM deleted;
END;
  INSERT INTO AdminFeesAudit
  ( [SystemUser],[HostName],[EventType],[SPID],[SchemaName],[ObjectName],[ObjectType],[AdminTypeID],[AdminTypeNetworkID],[EffectiveDate],[TerminationDate],[AdminType],[AdminTypePrev],[Fee])
  SELECT SUSER_SNAME(), HOST_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'),
 EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','smallint'),
    EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)'),
    EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'),
    EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)'),
 i.ID, i.AdminTypeNetworkID, i.EffectiveDate, i.TerminationDate, i.AdminType, @AdminTypePrev, CONVERT(money, i.Fee)
  FROM  AdminFees p WITH(nolock)
 INNER JOIN inserted i ON p.ID = i.ID --i.AdminTypeID
END
GO
ALTER TABLE [dbo].[AdminFees] ENABLE TRIGGER [trgAdminFeesAudit]
GO
/***********************************************************************/
REPORT

DECLARE @body NVARCHAR(MAX);
BEGIN TRY 
SET @body = N'<html><body><H3>Audit Log Info</H3><H4> .CORRECT THE INVALID AdminType and Stop the SQL job.</H4><table>'
 + N'<tr><th> Eventdate </th> <th> SystemUser </th> <th> HostName </th> <th> EventType </th> <th> SPID </th> <th> AdminTypeID </th> <th> AdminTypeNetworkID </th> <th> EffectiveDate </th> <th> TerminationDate </th> <th> FeeType </th> <th> FeeTypePrev </th> <th> Fee </th></tr>' 
 + CAST((
  SELECT Convert(Varchar(20),pa.[DateStamp],120) AS td
      ,ISNULL(pa.[systemuser],'') AS td
      ,ISNULL(pa.[hostname],'') AS td
      ,ISNULL(pa.[EventType],'') AS td
      ,ISNULL(pa.[SPID],'') AS td
      ,ISNULL(pa.[AdminTypeID],'') AS td
      ,ISNULL(pa.[AdminTypeNetworkID],'') AS td
      ,ISNULL(pa.[EffectiveDate],'') AS td
      ,ISNULL(pa.[TerminationDate],'') AS td
      ,ISNULL(pa.[FeeType],'') AS td
      ,ISNULL(pa.[FeeTypePrev],'') AS td
      ,ISNULL(pa.[Fee],'') AS td
  FROM myDatabase.dbo.AdminFeesAudit [pa] (NOLOCK) WHERE AdminTypeID = @AdminTypeID
  FOR XML RAW('tr'), ELEMENTS
 ) AS NVARCHAR(MAX)) +'</table></body></html>';
 /* DBAs OPTIONAL CODE HERE - EITHER SEND HERE 1 TIME OR ON FAILURE ERROR OR BOTH */
 /* IF THIS CODE IS ENABLED THEN THERE IS NO NEED TO EMAIL ON FAILURE - JUST TRACK IT AS A FAILING JOB */
  -- EXEC msdb.dbo.sp_send_dbmail
  --@profile_name  = 'ReportingDB'
  --  ,@recipients    = 'JobMonitor@myDomain.com;DataOps@myDomain.com'
  --  ,@subject       = 'Audit Log Info Report'
  --  ,@body          = @body
  --  ,@body_format   = 'HTML';
  --SELECT cast(@body AS NVARCHAR(MAX));
  UPDATE myDatabase.dbo.AdminFeesAudit SET AlertSent = 1 WHERE AdminTypeID = @AdminTypeID and AlertSent = 0;

  SET @body = ('<?xml version="1.0"?><xml>' + @body + '</xml>');
  SELECT cast(@body AS NVARCHAR(MAX));
  THROW 58001, @body, 1;
 END TRY 
 BEGIN CATCH 
   DECLARE @message NVARCHAR(2048) = N'ERROR: Audit Log Alert - INVALID AdminType. ' +  ISNULL(ERROR_MESSAGE(),' NULL ERROR_MESSAGE.');
   THROW 58002, @message, 1;
 END CATCH;

Web.Config Base64 Encoding With Simple Salt (and decoding)

By E.DIN Editor • 17 Nov, 2021
Title: Base64 Encoding With Simple Salt (and decoding)

XPCMD_SHELL Move & Load

By E.DIN Editor • 17 Nov, 2021
XPCMD_SHELL Move & Load Data from Directory

SSIS ScriptTask Main_Script_Executable.cs

By E.DIN Editor • 17 Nov, 2021

SSIS Script Task - Write Log To Disk

By E.DIN Editor • 17 Nov, 2021

Data Monitor XML & HTML Email

By E.DIN Editor • 01 Oct, 2020
 ***** My Valid Data Monitor **** XML OUT *** HTML EMAIL DELIVERY  *****IF OBJECT_ID('tempdb..#tCustomerID') IS NOT NULL DROP TABLE #tCustomerID    select distinct         mm.CustomerID  Into #tCustomerID    from myDatabase..myTable (nolock) mm     left join myDatabase.dbo.myOtherTable (nolock) mo  on mm.refID = mo.id     left join myDatabase.dbo.myOtherTableDetail (nolock) mod  on mo.id = mod.myOtherTableid         and mm.SearchValue = mod.SearchValue     where mm.refID is not null     and mm.quantity is not null      and mod.SearchValue is nullIF Exists (Select 1 From #tCustomerID)BEGIN DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)SET @xml = CAST(( SELECT [CustomerID] AS 'td' FROM #tCustomerID ORDER BY CustomerID FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))SET @body ='

My Valid Data Monitor

'    SET @body = @body + @xml +'
CustomerID
'--SELECT @body  exec msdb.dbo.sp_send_dbmail  @profile_name  = 'dbmailProfile' ,@recipients    = 'usergroup@mydomain.com' ,@subject       = 'My Valid Data Monitor' ,@body          = @body ,@body_format   = 'HTML'END

Make the most of the season by following these simple guidelines

By websitebuilder • 20 Sep, 2020
The new season is a great reason to make and keep resolutions. Whether it’s eating right or cleaning out the garage, here are some tips for making and keeping resolutions.

Keep in touch with site visitors and boost loyalty

By websitebuilder • 20 Sep, 2020
There are so many good reasons to communicate with site visitors. Tell them about sales and new products or update them with tips and information.

Tips for writing great posts that increase your site traffic

By websitebuilder • 20 Sep, 2020
Write about something you know. If you don’t know much about a specific topic that will interest your readers, invite an expert to write about it.

How to Turn Up Mic Volume in Windows 10 – Appuals.com

By tekp • 15 Jul, 2020
Turn Up! Not every microphone has the same base volume for transmitting your voice through your computer to whoever or whatever is on the other end of the exchange. Some microphones have a higher volume as compared to others, and some microphones have a volume that is so low that the person on the other […] The post How to Turn Up Mic Volume in Windows 10 – Appuals.com appeared first on TekPreacher.

BCP Parameters INPUT: UNCPathAndLogTable - UNC Path And LogTable OF Parameter file

By E.DIN Editor • 09 Jun, 2020
'+@UNCPathAndLogTable+'
More Posts
Share by: