Learning Code & Activity: February 2016

Hi guys! Yesterday my boss asked me to build a tool to monitor Windows servers and Sql server services. I searched on Google and find few codes, i modified the code changed powershell concept and made below code.

In this post I shall tell you about how you monitor your all servers. The only thing you need is SQL Server which runs with an account having admin privilege on the network, or you can monitor those systems, the account having admin rights.

Lets start-

Create a database and two tables-

----------------------------------------------------------------------

Use master
Go
Create Database DBAMonitor
GO
Use DBAMonitor
GO 

CREATE TABLE [dbo].[tbl_ServerInfo](
 [Id] [int] identity(1,1) Not NULL,
 [WindowsServerName] [varchar](100) Not NULL,
 [SQLinstanceName] [varchar](100)  NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl_ServiceInfo](
 [SQLServiceName] [varchar](500) NULL,
 [servicestatus] [varchar](100) NULL,
 [windowservername] [varchar](500) NULL,
 [startmode] [varchar](100) NULL,
 [UpdateDate] [datetime] NULL DEFAULT GETDATE(),
 [startname] [varchar](1000) NULL,
 [InstanceName] [varchar](1000) NULL,
 [ServerID] INT NULL
) ON [PRIMARY]

----------------------------------------------------------------------

Please add your server details on tbl_serverInfo you want to monitor-

----------------------------------------------------------------------

--For example

insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('Proddb','Dashboard')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.512','sql2008r2')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.500','')
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.510','')

--Checking details

----------------------------------------------------------------------

Now we need to run below script, but make sure xp_cmdshell is enabled. To enable it, please run below code-

----------------------------------------------------------------------

sp_configure 'Show Advanced Options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
go   
reconfigure
go

----------------------------------------------------------------------

Now run the script and look the power-

----------------------------------------------------------------------

set NoCount on
--Truncate table DBAMonitor..[tbl_ServiceInfo]
-- Variable to store windows server name
DECLARE @server_name varchar(100)
DECLARE @SQLInstance_name varchar(100)
DECLARE @SrvrID int
-- table to store PowerShell script output
CREATE TABLE #ServiceInfoData
(line varchar(max) null)
-- Declaring cursor to fetch windows server name
DECLARE server_cursor CURSOR FOR 
select distinct LTRIM(rtrim(windowsservername)) as windowsServerName,SQLInstanceName,Id from DBAMonitor..[tbl_ServerInfo]
OPEN server_cursor
FETCH NEXT FROM server_cursor 
INTO @server_name, @SqlInstance_Name,@SrvrID
WHILE @@FETCH_STATUS = 0
BEGIN
declare @svrName varchar(255)
declare @sql varchar(800)
set @svrName = @server_name
-- Preparing PowerShell Dynamic Statement
set @sql = 'powershell.exe -c "get-wmiobject -Class win32_service -Computername ' + QUOTENAME(Isnull(@svrName,''),'''') + ' | where {$_.name -like ''MSSQLServer'' -OR $_.name -like ''MSSQL$' + Isnull(@SqlInstance_Name,'') + ''' -OR $_.name -like ''SQLSERVERAGENT'' -OR $_.name -like ''ReportServer$' + Isnull(@SqlInstance_Name,'') + ''' -OR $_.name -like ''SQLAgent$' + Isnull(@SqlInstance_Name,'') + ''' } | select-object  Name,state,systemname,startmode,startname | %{$_.name+''|''+$_.state+''%''+$_.systemname+''*''+$_.startmode+''@''+$_.startname+''!''} "'
-- Inserting PowerShell Output to temporary table
--print @sql
insert #ServiceInfoData 
EXEC xp_cmdshell @sql
-- Deleting the rows which contains error or has not sufficient data
delete from #ServiceInfoData  where len(line) < 30
update #ServiceInfoData set line = line + '!' where line not like '%!%'
IF (SELECT COUNT(*) FROM #ServiceInfoData where line like '%Get-Wmi%') = 0
begin
insert into DBAMonitor..[tbl_ServiceInfo](SQLServiceName,servicestatus,windowservername,startmode,startname,InstanceName,ServerId)
select  rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as SQLServiceName 
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))) as ServiceStatus
--,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))) as WindowsServerName 
,@server_name
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1, (CHARINDEX('@',line) -1)-CHARINDEX('*',line)) ))) as startmode
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1, (CHARINDEX('!',line) -1)-CHARINDEX('@',line)) ))) as startname
,@SQLInstance_name
,@SrvrID
from #ServiceInfoData
where line is not null and LEN(line) > 30
end
-- Clearing output table
truncate table #ServiceInfoData
-- Next windows record
FETCH NEXT FROM server_cursor 
INTO @server_name,@SQLInstance_name,@SrvrID
END 
CLOSE server_cursor;
DEALLOCATE server_cursor;
-- dropping the temporary table
drop table #ServiceInfoData
Select 
Srvr.windowsServerName
,Srvr.SQLinstanceName 
,sevi.SQLServiceName
,sevi.startname [ServiceAccount]
,sevi.servicestatus
,sevi.UpdateDate [StatusTime]
,Case when sevi.serverid is null Then 'Server is down or Access denied!!' Else 'Check status!!' End [Remarks] 
From DBAMonitor..[tbl_ServerInfo] srvr
Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid

---------------------------------------------------------

For better results, Create a job and run the above script from sql agent.

You can also create mailer for same to notify.

The below code is to notify if any service is stopped or any server is down.

----------------------------------------------------------------------

Declare @MailSubject varchar(1000)='Monitor info for Windows Servers and Sql Services'
Declare @MailBodyHeader varchar(3000)='Hi,<br><br>Please find the details of servers or services, which are not working perfectly-<br><br>
  <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" style="border-top:1px solid black; border-left:1px solid black;"><TBODY>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="3" WIDTH="100PX" Color="white"><B>Server Monitor Info</B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="2" WIDTH="100PX" Color="white"><B>Status </B></FONT></TD></TR>
          <TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Windows Server Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Sql Instance Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Sql Service Name</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Service Account</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Service Status</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Status Time</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Remarks</B></FONT></TD>
           </TR>'
Declare @MailBody varchar(max)
Select @MailBody= Isnull(@MailBody,'')+'<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Srvr.windowsServerName,'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Srvr.SQLinstanceName,'')+'</B></FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.SQLServiceName,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.startname,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.servicestatus,'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(sevi.UpdateDate as varchar(30)),'')+'</FONT></TD>
           <TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Case when sevi.serverid is null Then 'Server is down or Access denied!!' Else 'Check status!!' End +'</FONT></TD>
           </TR>'
     From DBAMonitor..[tbl_ServerInfo] srvr
     Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid
     Where servicestatus<>'Running' or servicestatus is null

Set  @MAILBODY= @MailBodyHeader +@MAILBODY+'</Table>'
IF((Select Count(1) from DBAMonitor..[tbl_ServerInfo] srvr Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid Where servicestatus<>'Running' or servicestatus is null)>0)
Begin
EXEC MSDB.DBO.SP_SEND_DBMAIL 
     @RECIPIENTS ='vimal.lohani@gmail.com',
     @COPY_RECIPIENTS = 'vimal.lohani@gmail.com',                                   
     @BODY = @MailBody,
     @SUBJECT = @MailSubject,
     @BODY_FORMAT = 'HTML',
     @PROFILE_NAME ='dbamail' --Your profile name
End

----------------------------------------------------------------------

You can also schedule above script through agent.

Reference

MSSqlTips

Monitor Windows Server having SQL Services Agent Services Reporting Services