New DBA Inventory – Instance Details

This is a multi-part series on my newly created DBA Inventory system. The main page with a table of contents to link to all the posts is located here

This post will detail the start of the Inventory database schema and the powershell script(s) that poll at the Instance level, to get things like number of SQL Version, Edition, Service Account, Service Packs, Instance Root, Collation, Default Database and TLOG paths, etc.

There are two key tables, a main table and a STAGE table. The stage table is loaded with the nightly polling Powershell script and then a merge statement is run to insert any new or update any thing that changes. Linked here is the same script from the previous post as it has the InstanceDetails tables and the ServerInstances table which relates multiple instances to a single server or cluster.

Here’s the script to create these tables — Inventory_ServerDetailsSchema

There is also a new table used in this script to get Alternate Credentials – basically we store the netbios name of the server (SourceServerNetBIOS) the script is running on, an instance name, the username, and a secure string for the password. Using powershell’s functionality, only the same computer and same user can decrypt that secure string and use it. Since I run the Inventory system on a cluster, there are two physical computers that could run these scripts.

USE [Inventory]
GO

CREATE TABLE [dbo].[AltSqlCredentials](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[instancename] [varchar](128) NOT NULL,
	[uname] [varchar](64) NOT NULL,
	[pword] [varchar](512) NOT NULL,
	[moddt] [datetime] NULL,
	[moduser] [varchar](50) NULL,
	[SourceServerNetBIOS] [varchar](128) NOT NULL,
 CONSTRAINT [PK_AltSqlCredentials_InstanceName] PRIMARY KEY CLUSTERED 
(
	[instancename] ASC,
	[SourceServerNetBIOS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AltSqlCredentials] ADD  CONSTRAINT [DF_AltSqlCredentials_Mod_DT]  DEFAULT (getdate()) FOR [moddt]
GO

ALTER TABLE [dbo].[AltSqlCredentials] ADD  CONSTRAINT [DF_AltSqlCredentials_ModUser]  DEFAULT (suser_sname()) FOR [moduser]
GO

Here’s the powershell script, using SMO to pull it’s data from each instance.

[void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

if (Get-PSSnapin -Registered -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue )
{
	Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
	Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}
elseif (Get-Module sqlps -ListAvailable -ErrorAction SilentlyContinue)
{
	Import-Module sqlps -ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}	
else
{
	import-module "C:\DBATools\dbatoolsPre2008.psm1"  -ErrorAction SilentlyContinue
}

Import-Module "C:\DBATools\DBATOOLSCommon.psm1"

$dbaToolsServer = "CS-SQL-INFRAMONITORING-US-DEV-001"
$inventoryDB = "Inventory"

Function Get-InstanceInfo ($ServerName, $altCredential, $runDt)
{
	$serverName
	$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"

	if ($altCredential -ne $null) {
		write-host "Using Alt Credentials!"
		$server.ConnectionContext.LoginSecure=$false
		$user=$altCredential.UserName
		$pass=$altCredential.Password

		$server.ConnectionContext.set_Login($user)
		$server.ConnectionContext.set_SecurePassword($pass)
	}

	if ($server.count -gt 0) 
	{
    
		switch ($server.version.Major) 
		{
			8 {$version = "SQL Server 2000"}
			9 {$version = "SQL Server 2005"}
			10 {if ($server.version.Minor -eq 0 ) 
				{
					$version = "SQL Server 2008"
				} else {
					$version = "SQL Server 2008 R2"
				}
			}
			11 {$version = "SQL Server 2012"}
			12 {$version = "SQL Server 2014"}
			13 {$version = "SQL Server 2016"}
			default {$version = "Unknown"}
	       }
		if ($serverName.contains("\")) 
		{
        		$currName = $serverName.split("\")
			$name = $currName[0]
		} else {$name = $serverName}

		$instance=$name
		$platform = $server.Information.Platform.toString()
		$osVersion = $server.Information.OsVersion
		$clustered=0
		$fullText=0
		if ($server.IsClustered -eq $True) {$clustered=1} 
		if ($server.IsFullTextInstalled -eq $True) {$fullText=1}
		[int]$memory = $server.Information.PhysicalMemory
		[int]$processors = $server.Processors
		$serviceAccount = $server.ServiceAccount
		$sqlVersion = $server.Information.VersionString.toString()
		$edition = $server.Information.Edition
		$productLevel = $server.Information.ProductLevel.toString()
		$affinityType = 0
		#$server.AffinityInfo.AffinityType'
		$collation = $server.Collation
		$rootDirectory = $server.Information.RootDirectory.toString()
		$masterDBPath = $server.Information.MasterDBPath
		$installDataDirectory = $server.InstallDataDirectory
		$installSharedDirectory = $server.InstallSharedDirectory
		$errorLogPath = $server.ErrorLogPath
		$defaultFile = $server.DefaultFile
		$defaultLog = $server.DefaultLog
		$backupDirectory = $server.BackupDirectory

		$sqlQuery = "
			INSERT into [InstanceDetails_STAGE] 
			(	InstanceName,
				Platform,
				OSVersion,
				IsClustered,
				Processors,
				Memory,
				Instance,
				ServiceAccount,
				BuildNumber,
				Edition,
				SQLVersion,
				SPLevel,
				AutoAffinity,
				Collation,
				FullText,
				RootPath,
				MasterDBPath,
				InstallPath,
				SharedPath,
				ErrorLogPath,
				DefaultDataPath,
				DefaultLogPath,
				BackupPath 
			) values (
    				'$serverName',
				'$platform',
				'$osVersion',
				'$clustered',
				'$processors',
				'$memory',
				'$instance',
				'$serviceAccount',
				'$sqlVersion',
				'$edition',
				'$version',
				'$productLevel',
				'$affinityType',
				'$collation',
				'$fullText',
				'$rootDirectory',
				'$masterDBPath',
				'$installDataDirectory',
				'$installSharedDirectory',
				'$errorLogPath',
				'$defaultFile',
				'$defaultLog',
				'$backupDirectory'
			)
		"
		#$sqlQuery
		QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000        
	}
}
 
###########################################################################################
#
#		MAIN
#
###########################################################################################

$startDate = get-date
$runDt = Get-Date -format "yyyy-MM-dd HH:mm:00"

QuerySql $dbaToolsServer $inventoryDB "TRUNCATE TABLE [InstanceDetails_STAGE]" 60000

$sqlQuery = @"
		SELECT ID.InstanceName, 
			[UName] = AC.Uname,
			[PWord] = AC.PWord
		FROM InstanceDetails ID
		LEFT OUTER JOIN [AltSqlCredentials] AC
			ON ID.InstanceName = AC.instancename
			and AC.[SourceServerNetBIOS] = (SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
		WHERE ID.Retired = 0
		AND ID.LicenseOnly = 0

"@

$instances = QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

foreach ($instance in $instances) 
{
	#$serverName = $serverNameRow | % { $_.instancename} #Convert the datatablerow into a string
	$instanceName = $instance[0]
	$user = $instance[1]
	$pass = $instance[2]

	$altCredential = $null
	if (-not(([DBNull]::Value).Equals($user)) ) {
		write-host "Found alt credential for $instanceName"
		$pass = $pass|ConvertTo-SecureString 
		$altCredential = New-Object -TypeName System.Management.Automation.PsCredential `
			-ArgumentList $user, $pass
	}

	Get-InstanceInfo $instanceName $altCredential $runDt
}

$sqlQuery = "BEGIN TRAN
MERGE InstanceDetails as T
USING InstanceDetails_STAGE as S
ON (T.InstanceName = S.InstanceName)
WHEN NOT MATCHED BY TARGET
	THEN INSERT 
	(
		[InstanceName] , 
		[Platform] ,
		[OSVersion] ,
		[IsClustered] , 
		[Processors] ,
		[Memory] ,
		[Instance] , 
		[ServiceAccount] ,
		[BuildNumber] ,
		[Edition] ,
		[SQLVersion] ,
		[SPLevel] ,
		[AutoAffinity] ,
		[Collation] ,
		[FullText] ,
		[RootPath] ,
		[MasterDBPath] ,
		[InstallPath] ,
		[SharedPath] ,
		[ErrorLogPath] , 
		[DefaultDataPath] ,
		[DefaultLogPath] ,
		[BackupPath]
	) VALUES (
		S.[InstanceName] , 
		S.[Platform] ,
		S.[OSVersion] ,
		S.[IsClustered] , 
		S.[Processors] ,
		S.[Memory] ,
		S.[Instance] , 
		S.[ServiceAccount] ,
		S.[BuildNumber] ,
		S.[Edition] ,
		S.[SQLVersion] ,
		S.[SPLevel] ,
		S.[AutoAffinity] ,
		S.[Collation] ,
		S.[FullText] ,
		S.[RootPath] ,
		S.[MasterDBPath] ,
		S.[InstallPath] ,
		S.[SharedPath] ,
		S.[ErrorLogPath] , 
		S.[DefaultDataPath] ,
		S.[DefaultLogPath] ,
		S.[BackupPath]
	)
WHEN MATCHED AND S.SQLVersion != 'Unknown' or S.Edition != ''
	THEN UPDATE 
	SET T.[InstanceName] 		= S.[InstanceName], 	
		T.[Platform]		= S.[Platform],		
		T.[OSVersion]		= S.[OSVersion],		
		T.[IsClustered] 	= S.[IsClustered], 	
		T.[Processors]		= S.[Processors],		
		T.[Memory]		= S.[Memory],			
		T.[Instance] 		= S.[Instance], 		
		T.[ServiceAccount]	= S.[ServiceAccount],	
		T.[BuildNumber]		= S.[BuildNumber],		
		T.[Edition]		= S.[Edition],			
		T.[SQLVersion]		= S.[SQLVersion],		
		T.[SPLevel]		= S.[SPLevel],			
		T.[AutoAffinity]	= S.[AutoAffinity],	
		T.[Collation]		= S.[Collation],		
		T.[FullText]		= S.[FullText],		
		T.[RootPath]		= S.[RootPath],		
		T.[MasterDBPath]	= S.[MasterDBPath],	
		T.[InstallPath]		= S.[InstallPath],		
		T.[SharedPath]		= S.[SharedPath],		
		T.[ErrorLogPath] 	= S.[ErrorLogPath], 	
		T.[DefaultDataPath]	= S.[DefaultDataPath],	
		T.[DefaultLogPath]	= S.[DefaultLogPath],	
		T.[BackupPath]		= S.[BackupPath],
		T.[Mod_User]		= 'Inventory Script Update',
		T.[Mod_Dt]		= getdate()	
;
COMMIT TRAN
"
QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

$endDate = get-date
$sqlQuery = "INSERT INTO MonitoringJobStatus 
		(JOBNAME, STATUS, STARTDT, ENDDT, RANGESTARTDT, RANGEENDDT, ERROR)
		VALUES
		('InstanceDetails','C','$startDate', '$endDate', '', '', '')"



QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

exit

In the main script we query Inventory to generate the list of instances to query… again with the Alternate Credentials if needed. I also have the concept of “License Only” which are ones that we track for licensing purposes but we do not support. Perhaps it’s a vendor based solution, or some unknown install. The point is we don’t care that much about it, so don’t poll it.

		SELECT ID.InstanceName, 
			[UName] = AC.Uname,
			[PWord] = AC.PWord
		FROM InstanceDetails ID
		LEFT OUTER JOIN [AltSqlCredentials] AC
			ON ID.InstanceName = AC.instancename
			and AC.[SourceServerNetBIOS] = (SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
		WHERE ID.Retired = 0
		AND ID.LicenseOnly = 0

For each instance returned, parse and prep the credentials if there are any, and call Get-InstanceInfo to capture and then load the instance information we want.


foreach ($instance in $instances) 
{
	#$serverName = $serverNameRow | % { $_.instancename} #Convert the datatablerow into a string
	$instanceName = $instance[0]
	$user = $instance[1]
	$pass = $instance[2]

	$altCredential = $null
	if (-not(([DBNull]::Value).Equals($user)) ) {
		write-host "Found alt credential for $instanceName"
		$pass = $pass|ConvertTo-SecureString 
		$altCredential = New-Object -TypeName System.Management.Automation.PsCredential `
			-ArgumentList $user, $pass
	}

	Get-InstanceInfo $instanceName $altCredential $runDt
}

Inside the Get-InstanceInfo function, we call SMO, and use the alternate credentials if needed.

	$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"

	if ($altCredential -ne $null) {
		write-host "Using Alt Credentials!"
		$server.ConnectionContext.LoginSecure=$false
		$user=$altCredential.UserName
		$pass=$altCredential.Password

		$server.ConnectionContext.set_Login($user)
		$server.ConnectionContext.set_SecurePassword($pass)
	}

Then we parse the results from SMO and insert the row into the InstanceDetails_Stage

	if ($server.count -gt 0) 
	{
    
		switch ($server.version.Major) 
		{
			8 {$version = "SQL Server 2000"}
			9 {$version = "SQL Server 2005"}
			10 {if ($server.version.Minor -eq 0 ) 
				{
					$version = "SQL Server 2008"
				} else {
					$version = "SQL Server 2008 R2"
				}
			}
			11 {$version = "SQL Server 2012"}
			12 {$version = "SQL Server 2014"}
			13 {$version = "SQL Server 2016"}
			default {$version = "Unknown"}
	       }
		if ($serverName.contains("\")) 
		{
        		$currName = $serverName.split("\")
			$name = $currName[0]
		} else {$name = $serverName}

		$instance=$name
		$platform = $server.Information.Platform.toString()
		$osVersion = $server.Information.OsVersion
		$clustered=0
		$fullText=0
		if ($server.IsClustered -eq $True) {$clustered=1} 
		if ($server.IsFullTextInstalled -eq $True) {$fullText=1}
		[int]$memory = $server.Information.PhysicalMemory
		[int]$processors = $server.Processors
		$serviceAccount = $server.ServiceAccount
		$sqlVersion = $server.Information.VersionString.toString()
		$edition = $server.Information.Edition
		$productLevel = $server.Information.ProductLevel.toString()
		$affinityType = 0
		#$server.AffinityInfo.AffinityType'
		$collation = $server.Collation
		$rootDirectory = $server.Information.RootDirectory.toString()
		$masterDBPath = $server.Information.MasterDBPath
		$installDataDirectory = $server.InstallDataDirectory
		$installSharedDirectory = $server.InstallSharedDirectory
		$errorLogPath = $server.ErrorLogPath
		$defaultFile = $server.DefaultFile
		$defaultLog = $server.DefaultLog
		$backupDirectory = $server.BackupDirectory

		$sqlQuery = "
			INSERT into [InstanceDetails_STAGE] 
			(	InstanceName,
				Platform,
				OSVersion,
				IsClustered,
				Processors,
				Memory,
				Instance,
				ServiceAccount,
				BuildNumber,
				Edition,
				SQLVersion,
				SPLevel,
				AutoAffinity,
				Collation,
				FullText,
				RootPath,
				MasterDBPath,
				InstallPath,
				SharedPath,
				ErrorLogPath,
				DefaultDataPath,
				DefaultLogPath,
				BackupPath 
			) values (
    				'$serverName',
				'$platform',
				'$osVersion',
				'$clustered',
				'$processors',
				'$memory',
				'$instance',
				'$serviceAccount',
				'$sqlVersion',
				'$edition',
				'$version',
				'$productLevel',
				'$affinityType',
				'$collation',
				'$fullText',
				'$rootDirectory',
				'$masterDBPath',
				'$installDataDirectory',
				'$installSharedDirectory',
				'$errorLogPath',
				'$defaultFile',
				'$defaultLog',
				'$backupDirectory'
			)
		"
		#$sqlQuery
		QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000        
	}

Finally back in the main function we execute the merge statement to load InstanceDetails

$sqlQuery = "BEGIN TRAN
MERGE InstanceDetails as T
USING InstanceDetails_STAGE as S
ON (T.InstanceName = S.InstanceName)
WHEN NOT MATCHED BY TARGET
	THEN INSERT 
	(
		[InstanceName] , 
		[Platform] ,
		[OSVersion] ,
		[IsClustered] , 
		[Processors] ,
		[Memory] ,
		[Instance] , 
		[ServiceAccount] ,
		[BuildNumber] ,
		[Edition] ,
		[SQLVersion] ,
		[SPLevel] ,
		[AutoAffinity] ,
		[Collation] ,
		[FullText] ,
		[RootPath] ,
		[MasterDBPath] ,
		[InstallPath] ,
		[SharedPath] ,
		[ErrorLogPath] , 
		[DefaultDataPath] ,
		[DefaultLogPath] ,
		[BackupPath]
	) VALUES (
		S.[InstanceName] , 
		S.[Platform] ,
		S.[OSVersion] ,
		S.[IsClustered] , 
		S.[Processors] ,
		S.[Memory] ,
		S.[Instance] , 
		S.[ServiceAccount] ,
		S.[BuildNumber] ,
		S.[Edition] ,
		S.[SQLVersion] ,
		S.[SPLevel] ,
		S.[AutoAffinity] ,
		S.[Collation] ,
		S.[FullText] ,
		S.[RootPath] ,
		S.[MasterDBPath] ,
		S.[InstallPath] ,
		S.[SharedPath] ,
		S.[ErrorLogPath] , 
		S.[DefaultDataPath] ,
		S.[DefaultLogPath] ,
		S.[BackupPath]
	)
WHEN MATCHED AND S.SQLVersion != 'Unknown' or S.Edition != ''
	THEN UPDATE 
	SET T.[InstanceName] 		= S.[InstanceName], 	
		T.[Platform]		= S.[Platform],		
		T.[OSVersion]		= S.[OSVersion],		
		T.[IsClustered] 	= S.[IsClustered], 	
		T.[Processors]		= S.[Processors],		
		T.[Memory]		= S.[Memory],			
		T.[Instance] 		= S.[Instance], 		
		T.[ServiceAccount]	= S.[ServiceAccount],	
		T.[BuildNumber]		= S.[BuildNumber],		
		T.[Edition]		= S.[Edition],			
		T.[SQLVersion]		= S.[SQLVersion],		
		T.[SPLevel]		= S.[SPLevel],			
		T.[AutoAffinity]	= S.[AutoAffinity],	
		T.[Collation]		= S.[Collation],		
		T.[FullText]		= S.[FullText],		
		T.[RootPath]		= S.[RootPath],		
		T.[MasterDBPath]	= S.[MasterDBPath],	
		T.[InstallPath]		= S.[InstallPath],		
		T.[SharedPath]		= S.[SharedPath],		
		T.[ErrorLogPath] 	= S.[ErrorLogPath], 	
		T.[DefaultDataPath]	= S.[DefaultDataPath],	
		T.[DefaultLogPath]	= S.[DefaultLogPath],	
		T.[BackupPath]		= S.[BackupPath],
		T.[Mod_User]		= 'Inventory Script Update',
		T.[Mod_Dt]		= getdate()	
;
COMMIT TRAN

New DBA Inventory – Server Disk Info

This is a multi-part series on my newly created DBA Inventory system. The main page with a table of contents to link to all the posts is located here

The previous post discussed Server Details, things like Memory, CPU’s, Cores, BIOS, Operating System details using WMI Calls. This next post is another poll of Server level (Operating System) type detail… Disk Info.

This Powershell “poller” grabs disk info such as the Drive Letter, the Disk Model, the Partition, a Description, if it’s a Primary Partition, the Volume Name, if it’s SAN attached, if it’s a Mount Point, Disk Size, Disk Free Space, and a Serial Number. It stores all of this in a history table by an “Audit Date”. That table is named “DiskInfo”!

CREATE TABLE [dbo].[DiskInfo](
	[ServerName] [varchar](256) NOT NULL,
	[DiskName] [varchar](256) NOT NULL,
	[AuditDt] [datetime] NOT NULL,
	[Model] [varchar](256) NULL,
	[DiskPartition] [varchar](256) NULL,
	[Description] [varchar](256) NULL,
	[PrimaryPartition] [bit] NULL,
	[VolumeName] [varchar](256) NULL,
	[Drive] [varchar](128) NULL,
	[SanAttached] [bit] NULL,
	[MountPoint] [bit] NULL,
	[DiskSizeMB] [bigint] NULL,
	[DIskFreeMB] [bigint] NULL,
	[SerialNumber] [varchar](128) NULL,
 CONSTRAINT [PK_DiskInfo] PRIMARY KEY CLUSTERED 
(
	[ServerName] ASC,
	[DiskName] ASC,
	[AuditDt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

The powershell itself actually only queries my Helper Database installed on every instance – named DBATools. Each instance has a job that runs nightly to get the local disk information. I had originally worked on a centralized script that polled via WMI but it was slow and cumbersome to run in my environment. This is much faster. So the central polling script is here, and it’s pretty simple.

[void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

if (Get-PSSnapin -Registered -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue )
{
	Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
	Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}
elseif (Get-Module sqlps -ListAvailable -ErrorAction SilentlyContinue)
{
	Import-Module sqlps #-ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}	
else
{
	import-module "C:\DBATools\dbatoolsPre2008.psm1"  -ErrorAction SilentlyContinue
}

Import-Module "C:\DBATools\DBATOOLSCommon.psm1"

$dbaToolsServer = "CS-SQL-INFRAMONITORING-US-DEV-001"
$inventoryDB = "Inventory"

Function GetDiskInfo ($serverName, $sourceDB, $altCredential, $runDt)
{

	$DiskInfo = New-Object System.Data.DataTable
	[void]$DiskInfo.Columns.Add("ServerName", "System.String")
	[void]$DiskInfo.Columns.Add("DiskName", "System.String")
	[void]$DiskInfo.Columns.Add("AuditDt", "System.DateTime")
	[void]$DiskInfo.Columns.Add("Model", "System.String")
	[void]$DiskInfo.Columns.Add("DiskPartition", "System.String")
	[void]$DiskInfo.Columns.Add("Description", "System.String")
	[void]$DiskInfo.Columns.Add("PrimaryPartition", "System.Int32")
	[void]$DiskInfo.Columns.Add("VolumeName", "System.String")
	[void]$DiskInfo.Columns.Add("Drive", "System.String")
	[void]$DiskInfo.Columns.Add("SanAttached", "System.Int32")
	[void]$DiskInfo.Columns.Add("MountPoint", "System.Int32")
	[void]$DiskInfo.Columns.Add("DiskSizeMB", "System.String")
	[void]$DiskInfo.Columns.Add("DiskFreeMB", "System.String")
	[void]$DiskInfo.Columns.Add("SerialNumber", "System.String")

	$sqlQuery = "
		SELECT [ServerName]
			  ,[DiskName]
			  ,[AuditDt]
			  ,[Model]
			  ,[DiskPartition]
			  ,[Description]
			  ,[PrimaryPartition]
			  ,[VolumeName]
			  ,[Drive]
			  ,[SanAttached]
			  ,[MountPoint]
			  ,[DiskSizeMB]
			  ,[DIskFreeMB]
			  ,[SerialNumber]
		  FROM  [DiskInfo]
	"

	if ($altCredential -ne $null) {
		write-host "Using Alt Credentials!"

		$user=$altCredential.UserName
		$pass = $altCredential.GetNetworkCredential().Password
		$splat = @{
			UserName = $user
			Password = $pass
			ServerInstance = $serverName
			Database = $sourceDB
			Query = $sqlQuery
		}
		$diskQuery = Invoke-SqlCmd @splat 
	} else {
		$diskQuery = QuerySql $servername $sourceDB $sqlQuery 60000
	}
	foreach ($row in $diskQuery)
	{
			$newRow = $DiskInfo.NewRow()
			$newRow["ServerName"] = $row.Servername
			$newRow["DiskName"] = $row.DiskName
			$newRow["AuditDt"] = [DateTime]$runDt
			$newRow["Model"] = $row.Model
			$newRow["DiskPartition"] = $row.DiskPartition
			$newRow["Description"] = $row.Description
			$newRow["PrimaryPartition"] = $row.PrimaryPartition
			$newRow["VolumeName"] = $row.VolumeName
			$newRow["Drive"] = $row.Drive
			$newRow["SanAttached"] = $row.SanAttached
			$newRow["MountPoint"] = $row.MountPoint
			$newRow["DiskSizeMB"] = $row.DiskSizeMB
			$newRow["DiskFreeMB"] = $row.DiskFreeMB
			$newRow["SerialNumber"] = $row.SerialNumber
			$DiskInfo.Rows.Add($newRow)
	}

	#$diskInfo	

	try
	{
		if ($DiskInfo.Rows.Count -gt 0) {
	
			$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$dbaToolsServer;Integrated Security=SSPI;Initial Catalog=$inventoryDB");
			$cn.Open()
			$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
			$bc.DestinationTableName = "DiskInfo"
			$bc.WriteToServer($DiskInfo)
			$cn.Close()
		}
	}
	catch [System.Exception]
	{
		$errVal = $_.Exception.GetType().FullName + " - " + $_.FullyQualifiedErrorID
		Write-Host "Insert Error Error $serverName"
		Write-Host $errVal
		$errCount ++
	}
}


###########################################################################################
#
#		MAIN
#
###########################################################################################

$startDate = get-date
$runDt = Get-Date -format "yyyy-MM-dd HH:00:00"

#Get-ClusteredDiskInfo "NAT317DBS" $runDt
#EXIT

$sqlQuery = @"
		SELECT [InstanceName] = ID.InstanceName, 
			[DBAToolsDB] = DD.DatabaseName,
			[UName] = AC.Uname,
			[PWord] = AC.PWord
		FROM InstanceDetails ID
		LEFT OUTER JOIN DatabaseDetails DD
			ON ID.InstanceName = DD.InstanceName
		LEFT OUTER JOIN [AltSqlCredentials] AC
			ON ID.InstanceName = AC.instancename
			and AC.[SourceServerNetBIOS] = (SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
		WHERE ID.Retired = 0
		AND ID.LicenseOnly = 0
		AND DD.DatabaseName like 'DBATools%'
"@

$clusteredServers = QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

foreach ($instance in $clusteredServers)
{
	$instanceName = $instance[0]
	$sourceDB = $instance[1]
	$user = $instance[2]
	$pass = $instance[3]

	$altCredential = $null
	if (-not(([DBNull]::Value).Equals($user)) ) {
		write-host "Found alt credential for $instanceName"
		$pass = $pass|ConvertTo-SecureString 
		$altCredential = New-Object -TypeName System.Management.Automation.PsCredential `
			-ArgumentList $user, $pass
	}

	$instanceName
	GetDiskInfo $instanceName $sourceDB $altCredential $runDt
}

$endDate = get-date
$sqlQuery = "INSERT INTO MonitoringJobStatus 
		(JOBNAME, STATUS, STARTDT, ENDDT, RANGESTARTDT, RANGEENDDT, ERROR)
		VALUES
		('DISKINFO','C','$startDate', '$endDate', '', '', '')"



QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

exit

This query gets the list of instances to poll and also introduces the concept of a SQL Login credential that’s stored as a Secure String to use a SQL Login to connect rather than the AD Credential (for servers not joined to the domain).

$sqlQuery = @"
		SELECT [InstanceName] = ID.InstanceName, 
			[DBAToolsDB] = DD.DatabaseName,
			[UName] = AC.Uname,
			[PWord] = AC.PWord
		FROM InstanceDetails ID
		LEFT OUTER JOIN DatabaseDetails DD
			ON ID.InstanceName = DD.InstanceName
		LEFT OUTER JOIN [AltSqlCredentials] AC
			ON ID.InstanceName = AC.instancename
			and AC.[SourceServerNetBIOS] = (SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
		WHERE ID.Retired = 0
		AND ID.LicenseOnly = 0
		AND DD.DatabaseName like 'DBATools%'
"@

The function GetDiskInfo just creates a new data table, queries the local DBATools..DiskInfo table and uses the credentials if they exist. It then bulk uploads that data into the Inventory table.

The local DiskInfo table is identical, its just in the DBATools database on every instance. So let’s look at the Powershell script that’s on each local server as part of the DBATools installation.

#
#	GetDiskSanInfo.ps1
#	
#	This script determines for SAN that is attached whether 
#	it is a mount point and whether it is replicated.
#
#	DiskExt.exe is used to determine which physical disk a mount point resides on.
#	inq.wnt.exe is used to determine whether a given SAN volume is replicated.
#
#	The dbo.DiskInfo table in DBATools is updated with this information. From
#	there the DB Inventory service will read it and update the DBInv database.
#

if (Get-PSSnapin -Registered -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue )
{
	Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
	Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}
elseif (Get-Module sqlps -ListAvailable -ErrorAction SilentlyContinue)
{
	Import-Module sqlps -ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}	
else
{
	import-module "C:\DBATools\dbatoolsPre2008.psm1"  -ErrorAction SilentlyContinue
}
Import-Module "C:\DBATools\DBATOOLSCommon.psm1"

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

$dbaToolsServer = "CS-SQL-INFRAMONITORING-US-DEV-001"
$SQLName = $args[0]
if ($SQLName -ne $null)
{ $dbaToolsServer = $SQLName }

if (Get-Module FailoverClusters -ListAvailable -ErrorAction SilentlyContinue)
{  
	Import-Module FailoverClusters	
	$clusterName = GET-Cluster
	$physicalName = $clusterName.Name.toUpper()
} else {
	$physicalName = (hostname).toUpper()
}


$runDt = Get-Date -format "MM/dd/yyyy HH:00:00"

# this is the table that will hold the data during execution
$serverDisk = New-Object System.Data.DataTable
[void]$serverDisk.Columns.Add("ServerName", "System.String")
[void]$serverDisk.Columns.Add("DiskName", "System.String")
[void]$serverDisk.Columns.Add("AuditDt", "System.DateTime")
[void]$serverDisk.Columns.Add("Model", "System.String")
[void]$serverDisk.Columns.Add("DiskPartition", "System.String")
[void]$serverDisk.Columns.Add("Description", "System.String")
[void]$serverDisk.Columns.Add("PrimaryPartition", "System.Boolean")
[void]$serverDisk.Columns.Add("VolumeName", "System.String")
[void]$serverDisk.Columns.Add("Drive", "System.String")
[void]$serverDisk.Columns.Add("SanAttached", "System.Boolean")
[void]$serverDisk.Columns.Add("MountPoint", "System.Boolean")
[void]$serverDisk.Columns.Add("DiskSizeMB", "System.Int64")
[void]$serverDisk.Columns.Add("DiskFreeMB", "System.Int64")
[void]$serverDisk.Columns.Add("SerialNumber", "System.String")
                
# WMI data
$wmi_diskdrives = Get-WmiObject -Class Win32_DiskDrive
$wmi_mountpoints = Get-WmiObject -Class Win32_Volume -Filter "DriveType=3 AND DriveLetter IS NULL AND NOT Name like '\\\\?\\Volume%'" | Select $WMI_DiskMountProps
                
$AllDisks = @()
$DiskElements = @('ComputerName','Disk','Model','Partition','Description','PrimaryPartition','VolumeName','Drive', 'SanAttached', 'MountPoint', 'DiskSize','FreeSpace','DiskType','SerialNumber')
                foreach ($diskdrive in $wmi_diskdrives) 
                {
                    $partitionquery = "ASSOCIATORS OF {Win32_DiskDrive.DeviceID=`"$($diskdrive.DeviceID.replace('\','\\'))`"} WHERE AssocClass = Win32_DiskDriveToDiskPartition"
                    $partitions = @(Get-WmiObject -Query $partitionquery)
                    foreach ($partition in $partitions)
                    {
                        $logicaldiskquery = "ASSOCIATORS OF {Win32_DiskPartition.DeviceID=`"$($partition.DeviceID)`"} WHERE AssocClass = Win32_LogicalDiskToPartition"
                        $logicaldisks = @(Get-WmiObject -Query $logicaldiskquery)
                        foreach ($logicaldisk in $logicaldisks)
                        {
				$newRow = $serverDisk.NewRow()
				$newRow["ServerName"] = $physicalName
				$newRow["DiskName"] = $logicalDisk.Name
				$newRow["AuditDt"] = [DateTime]$runDt
				$newRow["Model"] = $diskDrive.Model
				$newRow["DiskPartition"] = $partition.Name
				$newRow["Description"] = $partition.Description
				$newRow["PrimaryPartition"] = $partition.PrimaryPartition
				$newRow["VolumeName"] = $logicalDisk.volumeName
				$newRow["Drive"] = $diskDrive.Name
				$newRow["SanAttached"] = if ($diskdrive.Model.StartsWith("NETAPP")) { $TRUE } else { $FALSE }
				$newRow["MountPoint"] = $FALSE
				$newRow["DiskSizeMB"] = [math]::truncate($logicalDisk.Size / 1MB)
				$newRow["DiskFreeMB"] = [math]::truncate($logicalDisk.FreeSpace / 1MB)
				$newRow["SerialNumber"] = $diskDrive.serialNumber
				$serverDisk.Rows.Add($newRow)
                        }
                    }
                }
                # Mountpoints are weird so we do them seperate.
                if ($wmi_mountpoints)
                {
                    foreach ($mountpoint in $wmi_mountpoints)
                    {
				$newRow = $serverDisk.NewRow()
				$newRow["ServerName"] = $physicalName
				$newRow["DiskName"] = $mountpoint.Name
				$newRow["AuditDt"] = [DateTime]$runDt
				$newRow["Model"] = 'Mount Point'
				$newRow["DiskPartition"] = $DBNULL
				$newRow["Description"] = $mountpoint.Caption
				$newRow["PrimaryPartition"] = $FALSE
				$newRow["VolumeName"] = $mountpoint.Caption
				$newRow["Drive"] = [Regex]::Match($mountpoint.Caption, "(^.:)").Value
				$newRow["SanAttached"] = $TRUE
				$newRow["MountPoint"] = $TRUE
				$newRow["DiskSizeMB"] = [math]::truncate($mountpoint.Capacity / 1MB)
				$newRow["DiskFreeMB"] = [math]::truncate($mountpoint.FreeSpace / 1MB)
				$newRow["SerialNumber"] = $DBNULL
				$serverDisk.Rows.Add($newRow)
                    }
                }

$sqlCmd = "Select name from sys.databases where name like 'DBATools%'"
$dbatoolsDBName = QuerySql  $dbaToolsServer "master" $sqlCmd 

$DBAToolsDB = $dbatoolsDBName[0]

$sqlCmd = "TRUNCATE TABLE dbo.DiskInfo"
QuerySql $dbaToolsServer $DBAToolsDB $sqlCmd 

# create the connection for the bulk loads
$connString = "Data Source=$dbaToolsServer; Initial Catalog=$DBAToolsDB; Integrated Security=True; Application Name=GetSanDiskInfo.ps1; "
$sqlConn = New-Object System.Data.SqlClient.SqlConnection $connString
$sqlConn.Open()
try
{
	# upload the table to DBATools
	$bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConn)
	$bulkCopy.DestinationTableName = "dbo.DiskInfo"
	$bulkCopy.WriteToServer($serverDisk)
}
catch [System.Exception]
{
	$errVal = $_.Exception.GetType().FullName + " - " + $_.FullyQualifiedErrorID
	Write-Host "Bulkcopy Error"
	Write-Host $errVal
	$errCount ++
}

Very similar as it creates a data table for each disk row. It also imports FailoverClusters to get the cluster name (in the case of a cluster, it stores all the disks under the Cluster Name) It then queries via WMI out to WIN32_DiskDrive and then to Win32_Volume. The filter on the Win32_Volume limits to just mount points via -Filter “DriveType=3 AND DriveLetter IS NULL AND NOT Name like ‘\\\\?\\Volume%'”.

It then uses some WMI Magic of “ASSOCIATORS OF {Win32_DiskDrive} by device ID” to get partitions, and another “ASSOCIATORS OF {Win32_DiskPartition} by device ID” to get the logical disks. This is the part that really slowed down running this from a centralized location, and forcing me to run it locally on each server.

I do some assumptions here, with the SanAttached value, if the Drive model starts with “NETAPP”, I assume it’s SAN Attached. I only have Netapp arrays in the company, so I can make that assumption. I’m sure there would be additional models, for EMC, 3Par, Dell, etc… but this works for my environment today.

I do a separate loop to load Mountpoints into the data table, and always assume they are SAN Attached.

So, if you’re astute, you’ll notice or think to yourself, if I have multiple instances on the same server, each of those DBATools databases will have a DiskInfo table and the same information in it. And then my centralized polling powershell will pull those and load them into the Inventory database…. but that table has a Primary Key on the ServerName, DiskName, and AuditDt. And you’re right it is the physical server name, or in the case of a cluster, the Virtual Computer Name of the Cluster. What I do is discard the duplicates – who needs them? Why write a bunch of logic to check for it, and not poll it, or whatnot, let the Constraint of the Clustered Primary Key do the work and disallow dupes. For example, I have one two node “active/passive” cluster that has seven instances on it. Each instance has a DBATools database, and each instance runs the local powershell script and loads the disk info for the cluster into each of the seven DBATools databases. The centralized polling script connects to each of those seven instances and attempts to load the same data seven times. It discards six of them – big deal, as long as I get one that’s all that matters…. I now have a list of all disks, volumes, mount points, total space and free space for every server that I have in my Inventory, and I have it every day. I can now trend growth rate at the server / disk layer over time.

Here’s the sample data from a single cluster of mine, with six instances on it (I group instances on a drive letter with three mountpoints under it, one for User Database Datafiles, one for User Database Tran Logs, and one for System DB’s / instance root). Just think of the trending and PowerBI charts you could make with a year’s worth of data from every SQL Server in your environment!