USE EnterpriseVaultDirectory
BEGIN
DECLARE@SiteEID VARCHAR(255),
@SiteNameVARCHAR(255),
@CompEIDVARCHAR(255),
@CompNameAltVARCHAR(255),
@CompRootPathVARCHAR(255),
@SWVersionVARCHAR(255),
@ExNameVARCHAR(255),
@EnableFileVARCHAR(255),
@DisableFileVARCHAR(255),
@UncNameVARCHAR(255),
@ShopRootPathVARCHAR(255),
@StorageEIDVARCHAR(255),
@IndexEIDVARCHAR(255),
@IndexRootPathVARCHAR(255),
@VSEIDVARCHAR(255),
@VSNameVARCHAR(255),
@VSsqlVARCHAR(255),
@VSdbVARCHAR(255),
@PartitionNameVARCHAR(255),
@PartitionRootPathVARCHAR(255),
@DeviceTypeVARCHAR(255),
@IPAddressesVARCHAR(255),
@WriteOnceMediaVARCHAR(255),
@PartitionStatusVARCHAR(255),
@NumVARCHAR(255),
@TaskEIDVARCHAR(255),
@TaskNameVARCHAR(255)
/* Declare SiteEntry cursor */
SET NOCOUNT ON
DECLARE se_cursor CURSOR FOR
SELECT SiteEntryID, SiteName FROM SiteEntry
FOR READ ONLY
/* Open cursor */
OPEN se_cursor
/* Fetch each SiteEntry record */
FETCH NEXT FROM se_cursor INTO @SiteEID, @SiteName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CHAR(13)
PRINT 'The following services and tasks are being run on the following servers in site: ' + UPPER(@SiteName)
/* Declare ComputerEntry cursor */
DECLARE ce_cursor CURSOR FOR
SELECT ComputerEntryID, ComputerNameAlternate, ComputerRootPath, VaultSoftwareVersion FROM ComputerEntry WHERE ComputerSiteEntryId = @SiteEID
FOR READ ONLY
/* Open cursor */
OPEN ce_cursor
/* Fetch each ComputerEntry record */
FETCH NEXT FROM ce_cursor INTO @CompEID, @CompNameAlt, @CompRootPath, @SWVersion
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CHAR(13)
PRINT @CompNameAlt + ' (Installation Directory = ' + @CompRootPath + ')'
PRINT CHAR(13)
/* See if there is a Task Controller Service running on the computer */
DECLARE tcs_cursor CURSOR FOR
SELECT Description FROM TaskControllerServiceEntry WHERE ComputerEntryId = @CompEID
FOR READ ONLY
/* Open cursor */
OPEN tcs_cursor
/* Fetch each TaskContollerServiceEntry record */
FETCH NEXT FROM tcs_cursor INTO @TaskName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CHAR(149) + ' Task Contoller Service, which managees the following Tasks:'
PRINT CHAR(13)
FETCH NEXT FROM tcs_cursor INTO @TaskName
END
/* Close and deallocate cursor */
CLOSE tcs_cursor
DEALLOCATE tcs_cursor
/* See what tasks are running on the computer */
DECLARE task_cursor CURSOR FOR
SELECT TaskEntryId, Name FROM Task WHERE ComputerEntryId = @CompEID ORDER BY TaskType
FOR READ ONLY
/* Open cursor */
OPEN task_cursor
/* Fetch each Task record **/
FETCH NEXT FROM task_cursor INTO @TaskEID, @TaskName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' ' + @TaskName
FETCH NEXT FROM task_cursor INTO @TaskEID, @TaskName
END
/* Close and deallocate cursor */
CLOSE task_cursor
DEALLOCATE task_cursor
/* See if there is FSA running on the computer */
DECLARE fsa_cursor CURSOR FOR
SELECT UncName FROM FileServerEntry WHERE ComputerEntryId = @CompEID
FOR READ ONLY
/* Open cursor */
OPEN fsa_cursor
/* Fetch each FileServerEntry record */
FETCH NEXT FROM fsa_cursor INTO @UncName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CHAR(13)
PRINT CHAR(149) + ' File System Archiving for ' + @UncName
FETCH NEXT FROM fsa_cursor INTO @UncName
END
/* Close and deallocate cursor */
CLOSE fsa_cursor
DEALLOCATE fsa_cursor
/* See if there is a Indexing Service running on the computer */
DECLARE ie_cursor CURSOR FOR
SELECT ServiceEntryId FROM IndexingServiceEntry WHERE ComputerEntryId = @CompEID
FOR READ ONLY
/* Open cursor */
OPEN ie_cursor
/* Fetch each IndexingServiceEntry record */
FETCH NEXT FROM ie_cursor INTO @IndexEID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CHAR(13)
PRINT CHAR(149) + ' Indexing Service'
/* Look for Root Paths associated with the Indexing Service */
SELECT @Num = COUNT(*) FROM IndexRootPathEntry WHERE IndexServiceEntryId = @IndexEID
IF @Num > 0
BEGIN
PRINT CHAR(13)
PRINT ' Index locations ='
DECLARE irp_cursor CURSOR FOR
SELECT IndexRootPath FROM IndexRootPathEntry WHERE IndexServiceEntryId = @IndexEID
FOR READ ONLY
/* Open cursor */
OPEN irp_cursor
/* Fetch each IndexRootPathEntry record */
FETCH NEXT FROM irp_cursor INTO @IndexRootPath
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' ' + @IndexRootPath
FETCH NEXT FROM irp_cursor INTO @IndexRootPath
END
/* Close and deallocate cursor */
CLOSE irp_cursor
DEALLOCATE irp_cursor
END
ELSE
BEGIN
PRINT CHAR(13)
PRINT ' No Index locations'
END
FETCH NEXT FROM ie_cursor INTO @IndexEID
END
/* Close and deallocate cursor */
CLOSE ie_cursor
DEALLOCATE ie_cursor
/* See if there is a Shopping Service running on the computer */
DECLARE shop_cursor CURSOR FOR
SELECT ShoppingRootPath FROM ShoppingServiceEntry WHERE ComputerEntryId = @CompEID
FOR READ ONLY
/* Open cursor */
OPEN shop_cursor
/* Fetch each ShoppingServiceEntry record */
FETCH NEXT FROM shop_cursor INTO @ShopRootPath
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CHAR(13)
PRINT CHAR(149) + ' Shopping Service'
PRINT CHAR(13)
PRINT ' Shopping data location ='
PRINT ' ' + @ShopRootPath
FETCH NEXT FROM shop_cursor INTO @ShopRootPath
END
/* Close and deallocate cursor */
CLOSE shop_cursor
DEALLOCATE shop_cursor
/* See if there is a SPS Service running on the computer */
DECLARE sps_cursor CURSOR FOR
SELECT SPSServer FROM SPSServiceEntry WHERE ComputerEntryId = @CompEID
FOR READ ONLY
/* Open cursor */
OPEN sps_cursor
/* Fetch each SPSServiceEntry record */
FETCH NEXT FROM sps_cursor INTO @ExName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CHAR(13)
PRINT CHAR(149) + ' SPS 2001 Service for ' + @ExName
FETCH NEXT FROM sps_cursor INTO @ExName
END
/* Close and deallocate cursor */
CLOSE sps_cursor
DEALLOCATE sps_cursor
/* See if there is a Storage Service running on the computer */
DECLARE storage_cursor CURSOR FOR
SELECT ServiceEntryId FROM StorageServiceEntry WHERE ComputerEntryId = @CompEID
FOR READ ONLY
/* Open cursor */
OPEN storage_cursor
/* Fetch each StorageServiceEntry record */
FETCH NEXT FROM storage_cursor INTO @StorageEID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CHAR(13)
PRINT CHAR(149) + ' Storage Service'
/* Look for Vault Store NTFS locations associated with the Storage Service */
SELECT @Num = COUNT(*) FROM VaultStoreEntry WHERE StorageServiceEntryId = @StorageEID
IF @Num > 0
BEGIN
DECLARE vs_cursor CURSOR FOR
SELECT VaultStoreEntryId, VaultStoreName, SQLServer, DatabaseDSN FROM VaultStoreEntry WHERE StorageServiceEntryId = @StorageEID
FOR READ ONLY
/* Open cursor */
OPEN vs_cursor
/* Fetch each VaultStoreRootPathEntry record */
FETCH NEXT FROM vs_cursor INTO @VSEID, @VSName, @VSsql, @VSdb
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CHAR(13)
PRINT ' Vault Store Name = ' + @VSName
PRINT ' Vault Store Database Location = ' + @VSsql
PRINT ' Vault Store Database Name = ' + @VSdb
/* Get the partition information for the vault store */
DECLARE partition_cursor CURSOR FOR
SELECT PartitionName, PartitionRootPath, DeviceType, IPAddressList, WriteOnceMedia, PartitionStatus FROM PartitionEntry WHERE VaultStoreEntryId = @VSEID
FOR READ ONLY
/* Open cursor */
OPEN partition_cursor
/* Fetch each PartitonEntry record */
FETCH NEXT FROM partition_cursor INTO @PartitionName, @PartitionRootPath, @DeviceType, @IPAddresses, @WriteOnceMedia, @PartitionStatus
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CHAR(13)
PRINT ' Partition Name = ' + @PartitionName
IF @DeviceType = '1'
BEGIN
PRINT ' Partition Type = EMC Centera'
PRINT ' IP Address List = ' + @IPAddresses
END
IF @DeviceType = '0'
BEGIN
IF @WriteOnceMedia = '1'
BEGIN
PRINT ' Partition Type = NetApp SnapLock WORM'
END
IF @WriteOnceMedia = '0'
BEGIN
PRINT ' Partition Type = NTFS Location'
END
PRINT ' Partition Location = ' + @PartitionRootPath
PRINT ' Partition Status = ' + @PartitionStatus
END
FETCH NEXT FROM partition_cursor INTO @PartitionName, @PartitionRootPath, @DeviceType, @IPAddresses, @WriteOnceMedia, @PartitionStatus
END
/* Close and deallocate cursor */
CLOSE partition_cursor
DEALLOCATE partition_cursor
FETCH NEXT FROM vs_cursor INTO @VSEID, @VSName, @VSsql, @VSdb
END
/* Close and deallocate cursor */
CLOSE vs_cursor
DEALLOCATE vs_cursor
END
ELSE
BEGIN
PRINT CHAR(13)
PRINT ' No Vault Store locations'
END
FETCH NEXT FROM storage_cursor INTO @StorageEID
END
/* Close and deallocate cursor */
CLOSE storage_cursor
DEALLOCATE storage_cursor
FETCH NEXT FROM ce_cursor INTO @CompEID, @CompNameAlt, @CompRootPath, @SWVersion
END
/* Close and deallocate cursor */
CLOSE ce_cursor
DEALLOCATE ce_cursor
FETCH NEXT FROM se_cursor INTO @SiteEID, @SiteName
END
/* Close and deallocate cursor */
CLOSE se_cursor
DEALLOCATE se_cursor
SET NOCOUNT OFF
END