AutoMon

Add server into Automon DBA SPs – AM01

Advertisements

Adding a server to the DBA Automon CMS as other data source, if you want to have a unique name of linked server DBA_*. There is one more SP by using SQL server, both will be useful, we can use either of one.

This script will add servers into the table and creates a linked server automatically.

Add servers as other data source  SP:

use DBAData
go
alter PROCEDURE [dbo].[USP_DBA_ADDSERVER_FOR_MONITOR]
/*
Summary:     Add server into AutoMon
Contact:     Muthukkumaran Kaliyamoorthy SQL DBA
Description: Add server into AutoMon

ChangeLog:
Date         Coder                          Description
2013-jan-21  Muthukkumaran Kaliyamoorthy     Updated the 2012 functionality                   


*/

@P_SERVER SYSNAME,
@P_VERSION SYSNAME,
@P_DESC VARCHAR(50),
@P_USERNAME SYSNAME,
@P_PWD VARCHAR(100),
@P_category VARCHAR(100),
@P_location VARCHAR(100),
@P_edition VARCHAR(50),
@P_svr_status VARCHAR(20),
@P_login_mode VARCHAR(20)

--WITH ENCRYPTION

AS 
 BEGIN

DECLARE @SERVER SYSNAME
DECLARE @DATASRC SYSNAME
DECLARE @VERSION SYSNAME
DECLARE @DESC VARCHAR(50)
DECLARE @USERNAME SYSNAME
DECLARE @PWD VARCHAR(100)
DECLARE @category VARCHAR(100)
DECLARE @location VARCHAR(100)
DECLARE @edition VARCHAR(50)
DECLARE @svr_status VARCHAR(20)
DECLARE @login_mode VARCHAR(20)

declare @sql varchar(8000)
declare @ls_script  varchar(8000)


SET @SERVER=@P_SERVER
SET @DATASRC=@P_SERVER
SET @VERSION=@P_VERSION
SET @DESC=@P_DESC
SET @USERNAME=@P_USERNAME
SET @PWD=@P_PWD
SET @category=@P_category
SET @location=@P_location
SET @edition=@p_edition
SET @svr_status=@p_svr_status
SET @login_mode=@P_login_mode 

create table #insert (name varchar(50))

IF PATINDEX ('% %',@P_VERSION)=0
BEGIN

--IF ( @VERSION not like '%2000%')

--Begin

IF (@login_mode='SQL')

BEGIN

set @ls_script='
EXEC SP_ADDLINKEDSERVER @SERVER='''+@SERVER +''',@SRVPRODUCT=''SQL Server''
EXEC SP_ADDLINKEDSRVLOGIN @RMTSRVNAME='''+@SERVER +''',@USESELF=''FALSE'',@LOCALLOGIN=NULL,@RMTUSER='''+@USERNAME+''',@RMTPASSWORD='''+@PWD+'''
EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC'',@OPTVALUE=''TRUE''
EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC OUT'',@OPTVALUE=''TRUE''
'
END

ELSE 
BEGIN
set @ls_script='
EXEC SP_ADDLINKEDSERVER @SERVER='''+@SERVER +''',@SRVPRODUCT=''SQL Server''
EXEC SP_ADDLINKEDSRVLOGIN @RMTSRVNAME='''+@SERVER +''',@USESELF=''true''
EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC'',@OPTVALUE=''TRUE''
EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC OUT'',@OPTVALUE=''TRUE''
'
END

END

--ELse

---- sql 2000
--BEGIN
--set @ls_script='
--EXEC SP_ADDLINKEDSERVER @SERVER='''+@SERVER +''',@PROVIDER=''MSDASQL'',@SRVPRODUCT=''SQL Server'',@provstr=N''DRIVER={SQL Server};SERVER='+@SERVER+';''
--EXEC SP_ADDLINKEDSRVLOGIN @RMTSRVNAME='''+@SERVER +''',@USESELF=''FALSE'',@LOCALLOGIN=NULL,@RMTUSER=''SA'',@RMTPASSWORD=''SAPassword''
--EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC'',@OPTVALUE=''TRUE''
--EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC OUT'',@OPTVALUE=''TRUE''
--'


--END


--print @ls_script
exec (@ls_script)
PRINT 'LINKED SERVER CREATED'

--SELECT * FROM 
DECLARE @TABSQL VARCHAR(5000)
DECLARE @PROCSQL VARCHAR(5000)
SET @TABSQL='
USE [MASTER];

IF object_id(''''TEMPSPACE'''') is not null
    PRINT ''''Present!''''
ELSE
 begin
 CREATE TABLE [DBO].[TEMPSPACE](
  [DRIVE] [VARCHAR](20) NULL,
  [SPACE] [INT] NULL
) ON [PRIMARY]
end
'
SET @PROCSQL='

if not exists ( select * from sysobjects 
            where name=''''USP_TEMPSPACE_POP'''' and objectproperty(id,''''IsProcedure'''')=1 )
exec(''''
create PROCEDURE [dbo].[USP_TEMPSPACE_POP]
AS
BEGIN
TRUNCATE TABLE TEMPSPACE
INSERT INTO TEMPSPACE
EXEC XP_FIXEDDRIVES
END
'''')
'

--SELECT LEN(@PROCSQL)
EXEC ('EXEC ['+@SERVER + '].MASTER.DBO.SP_EXECUTESQL N'''+@TABSQL+'''')
PRINT 'PROCEDURE CREATED'
EXEC ('EXEC ['+@SERVER + '].MASTER.DBO.SP_EXECUTESQL N'''+@PROCSQL+'''')
PRINT 'TABLE CREATED'

INSERT INTO [DBADATA].[DBO].[DBA_ALL_SERVERS]
           ([SERVERNAME]
           ,[DESCRIPTION]
           ,[VERSION]
           ,[Category] 
           ,[location] 
           ,[Login_mode]
           ,[Edition] 
           ,[SVR_status]  
           )
     VALUES
           (@SERVER
           ,@DESC
           ,@VERSION
           ,@category
           ,@location
           ,'not added'+@login_mode
           ,@edition
           ,@svr_status)
PRINT 'AUTOMATION COPMLETED'


set @sql='EXEC(''SELECT * from OPENQUERY(['+@SERVER+'],
''''SELECT name FROM master.dbo.sysobjects WHERE name = ''''''''tempspace''''''''
 '''')'')'

insert into #insert
exec (@sql)
--select * from #insert
IF EXISTS (select 1 from #insert where name='tempspace') 
begin
update [DBA_ALL_SERVERS]set Login_mode =@login_mode+'_Login' where DESCRIPTION=@DESC
end

--END
ELSE 
PRINT 'Pass the @P_VERSION parameter excluding space'
END


/*

--Custom Script to add all the servers:

select 'EXEC USP_DBA_ADDSERVER_FOR_MONITOR',''''+ServerName+''',',
''''+Version+''',',''''+ServerName+''',','''SA'',','''SApassword'',',
''''+Category+''',','''India'',',''''+Edition+''',','''Running'',',''''+Login_Mode+''''
from dbo.tbl_SQL_AutoMON where svr_Status <>'Server Not running'

--Custom Script to Drop all the servers:

select 'EXEC USP_DBA_DROPSERVER_FOR_MONITOR',''''+ServerName+''',',
''''+Version+''',',''''+ServerName+''''
from dbo.tbl_SQL_AutoMON where servername like '%ii%'

*/

Add servers as SQL server  SP:

use DBAData
go
alter PROCEDURE [dbo].[USP_DBA_ADDSERVER_FOR_MONITOR]
/*
Summary:     Add server into AutoMon
Contact:     Muthukkumaran Kaliyamoorthy SQL DBA
Description: Add server into AutoMon

ChangeLog:
Date         Coder                          Description
2013-jan-21  Muthukkumaran Kaliyamoorthy     Updated the 2012 functionality                   


*/

@P_SERVER SYSNAME,
@P_VERSION SYSNAME,
@P_DESC VARCHAR(50),
@P_USERNAME SYSNAME,
@P_PWD VARCHAR(100),
@P_category VARCHAR(100),
@P_location VARCHAR(100),
@P_edition VARCHAR(50),
@P_svr_status VARCHAR(20),
@P_login_mode VARCHAR(20)

--WITH ENCRYPTION

AS 
 BEGIN

DECLARE @SERVER SYSNAME
DECLARE @DATASRC SYSNAME
DECLARE @VERSION SYSNAME
DECLARE @DESC VARCHAR(50)
DECLARE @USERNAME SYSNAME
DECLARE @PWD VARCHAR(100)
DECLARE @category VARCHAR(100)
DECLARE @location VARCHAR(100)
DECLARE @edition VARCHAR(50)
DECLARE @svr_status VARCHAR(20)
DECLARE @login_mode VARCHAR(20)

declare @sql varchar(8000)
declare @ls_script  varchar(8000)


SET @SERVER=@P_SERVER
SET @DATASRC=@P_SERVER
SET @VERSION=@P_VERSION
SET @DESC=@P_DESC
SET @USERNAME=@P_USERNAME
SET @PWD=@P_PWD
SET @category=@P_category
SET @location=@P_location
SET @edition=@p_edition
SET @svr_status=@p_svr_status
SET @login_mode=@P_login_mode 

create table #insert (name varchar(50))

IF PATINDEX ('% %',@P_VERSION)=0
BEGIN

--IF ( @VERSION not like '%2000%')

--Begin

IF (@login_mode='SQL')

BEGIN

set @ls_script='
EXEC SP_ADDLINKEDSERVER @SERVER='''+@SERVER +''',@SRVPRODUCT=''SQL Server''
EXEC SP_ADDLINKEDSRVLOGIN @RMTSRVNAME='''+@SERVER +''',@USESELF=''FALSE'',@LOCALLOGIN=NULL,@RMTUSER='''+@USERNAME+''',@RMTPASSWORD='''+@PWD+'''
EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC'',@OPTVALUE=''TRUE''
EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC OUT'',@OPTVALUE=''TRUE''
'
END

ELSE 
BEGIN
set @ls_script='
EXEC SP_ADDLINKEDSERVER @SERVER='''+@SERVER +''',@SRVPRODUCT=''SQL Server''
EXEC SP_ADDLINKEDSRVLOGIN @RMTSRVNAME='''+@SERVER +''',@USESELF=''true''
EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC'',@OPTVALUE=''TRUE''
EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC OUT'',@OPTVALUE=''TRUE''
'
END

END

--ELse

---- sql 2000
--BEGIN
--set @ls_script='
--EXEC SP_ADDLINKEDSERVER @SERVER='''+@SERVER +''',@PROVIDER=''MSDASQL'',@SRVPRODUCT=''SQL Server'',@provstr=N''DRIVER={SQL Server};SERVER='+@SERVER+';''
--EXEC SP_ADDLINKEDSRVLOGIN @RMTSRVNAME='''+@SERVER +''',@USESELF=''FALSE'',@LOCALLOGIN=NULL,@RMTUSER=''SA'',@RMTPASSWORD=''SAPassword''
--EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC'',@OPTVALUE=''TRUE''
--EXEC SP_SERVEROPTION @SERVER='''+@SERVER +''',@OPTNAME=''RPC OUT'',@OPTVALUE=''TRUE''
--'


--END


--print @ls_script
exec (@ls_script)
PRINT 'LINKED SERVER CREATED'

--SELECT * FROM 
DECLARE @TABSQL VARCHAR(5000)
DECLARE @PROCSQL VARCHAR(5000)
SET @TABSQL='
USE [MASTER];

IF object_id(''''TEMPSPACE'''') is not null
    PRINT ''''Present!''''
ELSE
 begin
 CREATE TABLE [DBO].[TEMPSPACE](
  [DRIVE] [VARCHAR](20) NULL,
  [SPACE] [INT] NULL
) ON [PRIMARY]
end
'
SET @PROCSQL='

if not exists ( select * from sysobjects 
            where name=''''USP_TEMPSPACE_POP'''' and objectproperty(id,''''IsProcedure'''')=1 )
exec(''''
create PROCEDURE [dbo].[USP_TEMPSPACE_POP]
AS
BEGIN
TRUNCATE TABLE TEMPSPACE
INSERT INTO TEMPSPACE
EXEC XP_FIXEDDRIVES
END
'''')
'

--SELECT LEN(@PROCSQL)
EXEC ('EXEC ['+@SERVER + '].MASTER.DBO.SP_EXECUTESQL N'''+@TABSQL+'''')
PRINT 'PROCEDURE CREATED'
EXEC ('EXEC ['+@SERVER + '].MASTER.DBO.SP_EXECUTESQL N'''+@PROCSQL+'''')
PRINT 'TABLE CREATED'

INSERT INTO [DBADATA].[DBO].[DBA_ALL_SERVERS]
           ([SERVERNAME]
           ,[DESCRIPTION]
           ,[VERSION]
           ,[Category] 
           ,[location] 
           ,[Login_mode]
           ,[Edition] 
           ,[SVR_status]  
           )
     VALUES
           (@SERVER
           ,@DESC
           ,@VERSION
           ,@category
           ,@location
           ,'not added'+@login_mode
           ,@edition
           ,@svr_status)
PRINT 'AUTOMATION COPMLETED'


set @sql='EXEC(''SELECT * from OPENQUERY(['+@SERVER+'],
''''SELECT name FROM master.dbo.sysobjects WHERE name = ''''''''tempspace''''''''
 '''')'')'

insert into #insert
exec (@sql)
--select * from #insert
IF EXISTS (select 1 from #insert where name='tempspace') 
begin
update [DBA_ALL_SERVERS]set Login_mode =@login_mode+'_Login' where DESCRIPTION=@DESC
end

--END
ELSE 
PRINT 'Pass the @P_VERSION parameter excluding space'
END


/*

--Custom Script to add all the servers:

select 'EXEC USP_DBA_ADDSERVER_FOR_MONITOR',''''+ServerName+''',',
''''+Version+''',',''''+ServerName+''',','''SA'',','''SApassword'',',
''''+Category+''',','''India'',',''''+Edition+''',','''Running'',',''''+Login_Mode+''''
from dbo.tbl_SQL_AutoMON where svr_Status <>'Server Not running'

--Custom Script to Drop all the servers:

select 'EXEC USP_DBA_DROPSERVER_FOR_MONITOR',''''+ServerName+''',',
''''+Version+''',',''''+ServerName+''''
from dbo.tbl_SQL_AutoMON where servername like '%ii%'

*/

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: