IBM Tivoli Monitoring Service is a software used to monitor SQL servers
In order to install it, the team supporting IBM Tivoli will ask you for the information below.
The information needs to be input in a spreadsheet, which is why it's presented "horizontally" rather than in a vertical table.
(the "creation of ID" is at the bottom of the post)
/*
DESCRIPTION Get INFO for setting up Tivoli Monitoring
CONFIGURATION none
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000 -- Tivoli Not supported
*/
SET NOCOUNT ON
DECLARE @hostname NVARCHAR(40)
DECLARE @instancename SQL_VARIANT
DECLARE @DBHome NVARCHAR(1000)
DECLARE @Version SQL_VARIANT
DECLARE @SQLBitLevel NVARCHAR(6)
DECLARE @OSBitLevel NVARCHAR(6)
DECLARE @ErrorLog NVARCHAR(100)
DECLARE @LoginName NVARCHAR(8)
DECLARE @Passwd NVARCHAR(8)
/*---- working variables ----*/
DECLARE @Inst VARCHAR(100)
DECLARE @InstanceLocation VARCHAR(200)
DECLARE @RegKeyLoc VARCHAR(200)
-- This section concatenate the hostnames
-- if this is a cluster
SELECT @hostname=coalesce(@hostname,'') + ' ; ' + Nodename
FROM ::fn_virtualservernodes()
SET @hostname= coalesce((SUBSTRING(@hostname,4,len(@hostname))), convert(VARCHAR(40),serverproperty('ComputerNamePhysicalNetBIOS')))
SET @InstanceName = SERVERPROPERTY('servername')
-- Location of Instance on REGISTRY
SET @Inst = isnull(convert(VARCHAR, ServerProperty('InstanceName')),'MSSQLSERVER')
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\Microsoft SQL Server\Instance Names\SQL',
@value_name=@Inst,
@value=@InstanceLocation OUTPUT
-- Location of the Binaries
SET @RegKeyLoc = 'Software\Microsoft\Microsoft SQL Server\'+@InstanceLocation+'\Setup'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@RegKeyLoc,
@value_name='SQLPath',
@value=@DBHome OUTPUT
-- Location of Errorlog
SET @RegKeyLoc= 'Software\Microsoft\Microsoft SQL Server\'+@InstanceLocation+'\MSSQLServer\Parameters'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@RegKeyLoc,
@value_name='SQLArg1',
@value=@ErrorLog OUTPUT
SET @ErrorLog= REPLACE(@ErrorLog,'-e','')
Set @Version = serverproperty('edition')
SET @SQLBitLevel=
case
when (charindex('64',convert(nVARCHAR(30),@version))>0) then '64-bit'
else '32-bit'
end
Set @OSBitLevel=@SQLBitLevel
Set @LoginName = 'Tivoli'
SELECT
@hostname 'hostname'
, @instancename 'instance name'
, @DBHome 'DB Home'
, @Version 'Version'
, @SQLBitLevel 'SQL Bit Level'
, @OSBitLevel 'OS Bit Level'
, @ErrorLog 'ErrorFullPath'
, @LoginName 'login name'
, @Passwd 'password'
Usually, Tivoli requires a SQL login, and here is the code that creates the ID:
IF EXISTS (SELECT name FROM SYSLOGINS WHERE name = 'Tivoli') ALTER LOGIN [tivoli] WITH PASSWORD=N'Tiv0l1paSS' UNLOCK ELSE CREATE LOGIN [Tivoli] WITH PASSWORD=N'Tiv0l1paSS' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON ALTER SERVER ROLE [sysadmin] ADD MEMBER [Tivoli]
