1) Configure DBMail - In this case the ‘TEST’ profile being created for the demo
2) Create Table – TLOG_Metrics
3) Insert the dumnmy values or You can schedule a job to store the data in TLOG_Metrics table
4) Execute the script
STEP1:
CREATE TABLE [dbo].[TLOG_DBMetrics](
[ServerName] [varchar](1000) NULL,
[userconnections] [varchar](1000) NULL,
[LogDate] [datetime] NOT NULL
)
STEP 2:
/* Insert DUMMY Values*/
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’247.00′,’2013-04-22 11:41:21.290′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’254.00′,’2013-04-22 11:31:23.390′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’259.00′,’2013-04-22 11:21:21.713′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’245.00′,’2013-04-22 11:11:21.370′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’295.00′,’2013-04-22 11:01:29.170′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’246.00′,’2013-04-22 10:51:20.510′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’239.00′,’2013-04-22 10:41:23.800′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’251.00′,’2013-04-22 10:32:35.633′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’247.00′,’2013-04-22 10:21:20.907′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’245.00′,’2013-04-22 10:11:26.327′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’289.00′,’2013-04-22 10:01:22.460′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’240.00′,’2013-04-21 09:51:21.190′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’236.00′,’2013-04-21 09:41:21.787′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’246.00′,’2013-04-22 09:31:23.463′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’238.00′,’2013-04-22 09:21:22.093′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’254.00′,’2013-04-22 09:11:22.360′)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’282.00′,’2013-04-22 09:01:25.113′)
STEP 3: /* Replace @ProfileName and Recipients list in the below script*/ For Example – SET @ProfileName =’PowerSQL’ and SET @recipients= @abc@mail.com;def@mail.com
Execute the below script
DECLARE @html1 nvarchar(MAX),
@html2 nvarchar(MAX),
@dml1 nvarchar(MAX),
@dml2 nvarchar(MAX),
@td1 varchar(10),
@td2 varchar(10),
@td3 varchar(10),
@td4 varchar(10),
@subject varchar(100),
@RowId1 int,
@LoopStatus1 int,
@RowId int,
@Loopstatus int,
@ProfileName nvarchar(50),
@recipients nvarchar(100)
SET @ProfileName=’TEST’
SET @recipients=’ABC@MAIL.COM;def@mail.com’
CREATE TABLE #Temp (
ID INT identity(1,1),
[ServerName] [varchar](1000) NULL,
[userconnections] [varchar](1000) NULL,
[LogDate] [datetime] NOT NULL)
INSERT INTO #Temp(ServerName,userconnections,LogDate)
SELECT top 10 ServerName,userconnections,LogDate
FROM [TLOG_DBMetrics]
set @html1=’<html>
<head>
<STYLE TYPE=text/css>
<table width=”100%” border=1>
<tr bgcolor=”#CCCCCC”>
<td colspan=”7” height=”25” align=”center”>
<font face=”tahoma” color=”#003399” size=”4”>
<strong>User Activity Report</strong></font>
</td>
</tr>
</table>
<table width=”100%” border=1 ><tbody>
<tr bgcolor=”#CCCCCC”>
<td width=”100%” align=”center” colSpan=3><font face=”tahoma” color=”#003399” size=”2”>
<strong> No of User Active connections</strong></font></td>
</tr>
<tr bgcolor=#CCCCCC>
<td width=”10%” align=”center”>ServerName</td>
<td width=”50%” align=”center”>UserConnection</td>
<td width=”10%” align=”center”>LogDate</td>
</tr>
‘
set @loopstatus=1
set @RowId=1
set @dml2=”
set @dml1=”
While @Loopstatus<>0
begin
select
@td1 = servername,
@td2 = userconnections,
@td3 = convert(varchar(10),Logdate,110)
from #Temp where id=@RowId
if @@ROWCOUNT=0
begin
set @Loopstatus=0
end
else
begin
set @dml1= @DML2+N’<tr><td>’+@td1+N’</td><td>’+@td2+N’</td><td>’+@td3+’</td></tr>’
set @DMl2=@dml1
set @dml1=”
end
set @RowId=@RowId+1
end
SET @html2=@html1+@Dml2+’</table>’
print @html2
set @subject=’ User Activity Report ‘+ CONVERT(varchar(10),getdate(),110)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients= @recipients,–’abc@mail.com;def@mail.com’,
@subject = @subject,
@body = @html2,
@body_format = ‘HTML’;
DROP TABLE #Temp
