Fuzzy Lookup Transformation: SQL Server Object Monitoring Alert

/*AT Server Level*/
[ddl_trig_database]
ON ALL SERVER
FOR DROP_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)
SET @subjectText = 'DATABASE Droped on ' + @@SERVERNAME + ' by ' + SUSER_SNAME()
SET @results =
(SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))

--Uncomment the below line if you want to not be alerted on certain DB names
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB_Profile',
@recipients = 'abc@xyzcom',
@body = @results,
@subject = @subjectText
GO
ENABLE TRIGGER [ddl_trig_database] ON ALL SERVER
GO

/*AT DB Level*/

[TRG_Monitor]
ON DATABASE
AFTER
CREATE_TABLE,ALTER_TABLE,CREATE_PROCEDURE,DROP_TABLE,DROP_PROCEDURE,CREATE_VIEW,DROP_VIEW,ALTER_VIEW,CREATE_FUNCTION,DROP_FUNCTION,ALTER_FUNCTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @Object_Name SYSNAME,@Obj_Type varchar(25),@EventType varchar(25),@Mail_Flag Bit,@Act_U_Name Varchar(100),@Emp_id Varchar(10),@Receipt Varchar(1000),@CC Varchar(500),@App Varchar(100)
Set @Mail_Flag = 0
Set @Act_U_Name = ''
Set @Emp_id = ''
SELECT @Object_Name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')
SELECT @Obj_Type = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
SELECT @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')

Select @EventType = Replace(Upper(@EventType),'_',SPACE(1))
Select @Act_U_Name = Isnull([USER_NAME],''),@Emp_id = Isnull(Emp_Id,'') from [User_Empid_Mapping]
Where PC_Name = HOST_NAME() And Active_Flag = 'A'

declare @results varchar(max)
declare @subjectText varchar(max)
--declare @databaseName VARCHAR(255)
Select @subjectText = Convert(Varchar(255),@EventType)+' ALERT on '+DB_NAME()+' database - ' + @@SERVERNAME
SET @results =
(SELECT EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME'))
--SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))

-- ==================================================================

Insert Into DDL_Track(ServerName,DBName,HostName,IP_Address,UserName,UserName_Actual,ObjectType,ObjectName)
Select @@SERVERNAME,DB_Name(),HOST_NAME(),CONVERT(Varchar(50),CONNECTIONPROPERTY('client_net_address')), SUSER_SNAME(),@Act_U_Name,@EventType,@Object_Name
-- =================================================================
-- Set Mail flag
-- =================================================================
If Not Exists(Select 1 from DBA_User_Info Where UserName = SUSER_SNAME())
Begin
If(Left(@EventType,6) ='CREATE' OR @EventType ='DROP_TABLE')
Set @Mail_Flag = 1
End

--If(@EventType In('DROP_TABLE','DROP_PROCEDURE','DROP_FUNCTION','DROP_VIEW'))
If(@EventType ='DROP_PROCEDURE' OR @EventType ='DROP_VIEW' OR @EventType ='DROP_FUNCTION')
Set @Mail_Flag = 1

If(@Mail_Flag = 1)
Begin

-- =============================================================
--Check for the recpient; If DB is not configured then set default
-- =============================================================
If Exists(Select Top 1 [DB_ID] from DB_Blocking_Reciepient_List Where [DB_Name] = DB_Name())
Begin
Select @Receipt = recipients,@CC = CC,@App = [Application_Name] from DB_Blocking_Reciepient_List Where [DB_Name] = DB_Name()
End
Else
Begin
Select @Receipt = 'abc@xyz.com',@CC = '',@App = ''
End

-- =============================================================
-- Header portion
-- =============================================================

Select @results = '<td>Hi All,</td> </tr>'
--Select @results = @results + '<td>'+'The operation '+Convert(Varchar(255),@EventType)+' (for object name - '+Convert(Varchar(255),@Object_Name)+') has been occurred on '+DB_Name()+' database of ' + @@SERVERNAME + ' by ' + SUSER_SNAME() +' (on host name ' + HOST_NAME() +' IP Address - '+CONVERT(Varchar(50),CONNECTIONPROPERTY('client_net_address'))+'). Please do the needful if required. </td> '
Select @results = @results + '<td>'+'The operation '+Convert(Varchar(255),@EventType)+' (for object name - '+Convert(Varchar(255),@Object_Name)+') has been occurred on '+DB_Name()+' database of ' + @@SERVERNAME + ' by ' + SUSER_SNAME() +'. Please find below the additional details and do the needful if required. </td> '

-- =============================================================
-- Table portion
-- =============================================================
Select @results = @results+' <td>Additional Information:</td></tr> '

--1 Server_name ,
Set @results = @results + '<table border=8 ><tr><td bgcolor="#E7F5FE">'+'Server Name'+'</td>'
Set @results = @results + '<td>'+Convert(Varchar(100),@@SERVERNAME)+'</td></tr>'

--2 DB_name
Set @results = @results + '<td bgcolor="#E7F5FE">'+'Database Name'+'</td>'
Set @results = @results + '<td>'+Convert(Varchar(100),DB_Name())+'</td></tr>'

--3 HostName
Set @results = @results + '<td bgcolor="#E7F5FE">'+'Host Name'+'</td>'
Set @results = @results + '<td>'+Convert(Varchar(100),HOST_NAME())+'</td></tr>'

--4 IP_Address
Set @results = @results + '<td bgcolor="#E7F5FE">'+'IP Address'+'</td>'
Set @results = @results + '<td>'+Convert(Varchar(100),CONNECTIONPROPERTY('client_net_address'))+'</td></tr>'

--5 Emp Id
If(LEN(@Emp_id)>1)
Begin
Set @results = @results + '<td bgcolor="#E7F5FE">'+'Employee Id'+'</td>'
Set @results = @results + '<td>'+@Emp_id+'</td></tr>'
End

--6 UserName
Set @results = @results + '<td bgcolor="#E7F5FE">'+'Login Name'+'</td>'
Set @results = @results + '<td>'+SUSER_SNAME()+'</td></tr>'

If(LEN(@Act_U_Name)>1)
Begin
--7 UserName_Actual
Set @results = @results + '<td bgcolor="#E7F5FE">'+'User Name'+'</td>'
Set @results = @results + '<td>'+@Act_U_Name+'</td></tr>'
End

--8 ObjectType
Set @results = @results + '<td bgcolor="#E7F5FE">'+'Event Type'+'</td>'
Set @results = @results + '<td>'+@EventType+'</td></tr>'

--9 ObjectName
Set @results = @results + '<td bgcolor="#E7F5FE">'+'Object Name'+'</td>'
Set @results = @results + '<td>'+@Object_Name+'</td></tr>'

Select @results = @results+'</table>'
-- =============================================================
-- Footer Portion
-- =============================================================
Select @results = @results+' <td>Thanks & Regards,</td> </tr>'
Select @results = @results+'<td>Team</td> </tr>'
Select @results = @results+' '
Select @results = @results+'<td>Note : System generated mail, For any concern revert back to DBA team (abc@xyz.com).</td></tr>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_DB',
@recipients = 'abc@xyz.com',
@body = @results,
@subject = @subjectText,
@body_format = 'HTML'
/*Emp_ID;User_Name;PC_Name;Proj_ID;Network_ID;Active_Flag
DDL_Track - Row_id;ServerName;DBName;HostName;IP_Address;UserName;UserName_Actual;ObjectType;ObjectName;Time_Stamp */

End
SET NOCOUNT OFF
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [TRG_Monitor] ON DATABASE
GO
ENABLE TRIGGER [TRG_Monitor] ON DATABASE
GO

Fuzzy Lookup Transformation

Monday, February 5, 2018

SQL Server Object Monitoring Alert

No comments:

Post a Comment

My Blog List