Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL inventory powershell script


SQL inventory powershell script

Author
Message
MickyD
MickyD
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 1472
Hi everyone.
Hope someone can offer some advice on my issue.
I have been learning Powershell with the aid of the SQL Server 2008 Administration with Windows Powershell book published by Wrox.
Book is good and I have learnt a lot from it.
The book covers SQL 2000, SQL 2005 and SQL 2008.
Up to a stage now where my inventory also includes SQL 2008 R2 and SQL 2012.

I don't think the scripts are configured to capture the correct parameters for capturing SQL 2008 R2 and SQL 2012 details.

I have included the full script.
Could any one offer me any advice on how to the scripts should be changed to capture SQL 2008R2 and SQL 2012 configs.
(and if possible and explanation on what was changed so I can learn from it?)

Full script:
# ======================================================================================================================
#
# NAME: Upsert-Server.ps1
#
# AUTHOR: Yan and MAK
# DATE : 6/8/2008
#
# COMMENT: This script adds a server record into inventory.
# If the server already exists, then this script updates the existing server record.
# Example: Upsert-Server -instanceName MSSQLSERVER -hostName POWERPC -status U
# =======================================================================================================================

##############################################################################
# Initialize parameters
##############################################################################
param (
[switch]$help,
[string]$instanceName = {}, # Name of the SQL Server instance to add into inventory. For a default instance, it is MSSQLSERVER.
[string]$hostName = {}, # Name of the SQL Server host.
[string]$clusterName = {}, # Name of the SQL Server cluster.
[string]$status = {} # Status of the SQL Server instance. Possible values include D, Q, P, U and R.
)

# This function connects with a SQL Server instance [$pHostName\$pInstanceName,$pTcpPort] to execute a SQL query $pSql.
function execSQL([String] $pHostName, [String] $pInstanceName, [String] $pSql, [String] $pTcpPort)
{
if ( $pInstanceName -eq 'MSSQLSERVER' ) {
(Invoke-Sqlcmd -Query "$pSql" -ServerInstance "$pHostName,$pTcpPort" -Database master).Column1
}
else {
(Invoke-Sqlcmd -Query "$pSql" -ServerInstance "$pHostName\$pInstanceName,$pTcpPort" -Database master).Column1
}
}

# This function returns the statements that can be used to get a server configuration in a column called Column1.
function getConfigSql([String] $option)
{
$strSql = "CREATE TABLE #temp (name nvarchar(35), minimum int, maximum int, config_value int, run_value int)`n"
$strSql = $strSql + "INSERT INTO #temp EXEC (`'exec sp_configure `'`'$option`'`'`')`n"
$strSql = $strSql + "SELECT run_value as Column1 from #temp`n"
$strSql = $strSql + "DROP TABLE #temp`n"

return $strSql
}

# This function connects to the HKLM registry hive of the SQL Server host $pHostName
# and retrieve the TCP/IP port number that the instance $pInstanceName is listening on.
function getTcpPort([String] $pHostName, [String] $pInstanceName)
{
$strTcpPort=""

$reg = [WMIClass]"\\$pHostName\root\default:stdRegProv"
$HKEY_LOCAL_MACHINE = 2147483650

# Default instance
if ($pInstanceName -eq 'MSSQLSERVER') {
#SQL Server 2000 or SQL Server 2005/2008 resides on the same server as SQL Server 2000
$strKeyPath = "SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp"
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
if ($strTcpPort) {
Set-Variable -Name instanceRegPath -Value "SOFTWARE\Microsoft\MSSQLServer\MSSQLServer" -Scope 1
return $strTcpPort
}

}
else {
#SQL Server 2000 or SQL Server 2005/2008 resides on the same server as SQL Server 2000
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\$pInstanceName\MSSQLServer\SuperSocketNetLib\Tcp"
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
if ($strTcpPort) {
Set-Variable -Name instanceRegPath -Value "SOFTWARE\Microsoft\Microsoft SQL Server\$pInstanceName\MSSQLServer" -Scope 1
return $strTcpPort
}
}

#SQL Server 2005
for ($i=1; $i -le 50; $i++) {
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i"
$strInstanceName=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"").svalue

if ($strInstanceName -eq $pInstanceName) {
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i\MSSQLServer\SuperSocketNetLib\tcp\IPAll"
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue

Set-Variable -Name instanceRegPath -Value "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i\MSSQLServer" -Scope 1
return $strTcpPort
}
}

#SQL Server 2008
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.$pInstanceName\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
if ($strTcpPort) {
Set-Variable -Name instanceRegPath -Value "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.$pInstanceName\MSSQLServer" -Scope 1
return $strTcpPort
}

return ""
}


# This function connects to the HKLM registry hive of the SQL Server host $pHostName
# and retrieve the network protocols used by the instance $pInstanceName.
function getServerNetWorkProtocols([String] $pHostName, [String] $pInstanceName)
{
$strProtocols=""

$reg = [WMIClass]"\\$pHostName\root\default:stdRegProv"
$HKEY_LOCAL_MACHINE = 2147483650

$strKeyPath = "$instanceRegPath\SuperSocketNetLib"
#SQL Server 2000
$arrValues=$reg.GetMultiStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"ProtocolList").sValue
if ($arrValues) {
$arrValues | foreach -process { $strProtocols=$strProtocols + $_ + ',' }
return $strProtocols.Substring(0, $strProtocols.Length-1)
}
#SQL Server 2005 or 2008
else {
$strKeyPath = "$instanceRegPath\SuperSocketNetLib\Tcp"
$intEnabled=$reg.GetDWORDValue($HKEY_LOCAL_MACHINE,$strKeyPath,"Enabled").uvalue
if ($intEnabled) {
if ($intEnabled -eq 1) { $strProtocols='tcp,' }

$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$instanceNo\MSSQLServer\SuperSocketNetLib\Np"
$intEnabled=$reg.GetDWORDValue($HKEY_LOCAL_MACHINE,$strKeyPath,"Enabled").uvalue
if ($intEnabled -eq 1) { $strProtocols=$strProtocols + 'np,' }

$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$instanceNo\MSSQLServer\SuperSocketNetLib\Sm"
$intEnabled=$reg.GetDWORDValue($HKEY_LOCAL_MACHINE,$strKeyPath,"Enabled").uvalue
if ($intEnabled -eq 1) { $strProtocols=$strProtocols + 'sm,' }

$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$instanceNo\MSSQLServer\SuperSocketNetLib\Via"
$intEnabled=$reg.GetDWORDValue($HKEY_LOCAL_MACHINE,$strKeyPath,"Enabled").uvalue
if ($intEnabled -eq 1) { $strProtocols=$strProtocols + 'via,' }

return $strProtocols.Substring(0, $strProtocols.Length-1)
}
}
}


# This function connects to the HKLM registry hive of the SQL Server host $pHostName
# and retrieve the startup parameters used by the instance $pInstanceName.
function getStartupParameters([String] $pHostName, [String] $pInstanceName)
{
$reg = [WMIClass]"\\$pHostName\root\default:stdRegProv"
$HKEY_LOCAL_MACHINE = 2147483650

$strKeyPath = "$instanceRegPath\Parameters"
$arrValues=$reg.EnumValues($HKEY_LOCAL_MACHINE,$strKeyPath).sNames

#SQL Server 2000
if ($arrValues) {
for ($i=0; $i -lt $arrValues.Length; $i++) {
$strParameters=$strParameters + $reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,$arrValues[$i]).svalue + ";"
}
return $strParameters
}

#SQL Server 2005
for ($i=1; $i -le 50; $i++) {
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i"
$strInstanceName=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"").svalue

if ($strInstanceName -eq $pInstanceName) {
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i\MSSQLServer\Parameters"
$arrValues=$reg.EnumValues($HKEY_LOCAL_MACHINE,$strKeyPath).sNames

if ($arrValues) {
for ($i=0; $i -lt $arrValues.Length; $i++) {
$strParameters=$strParameters + $reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,$arrValues[$i]).svalue + ";"
}
return $strParameters
}
}
}

#SQL Server 2008
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.$pInstanceName\MSSQLServer\Parameters"
$arrValues=$reg.EnumValues($HKEY_LOCAL_MACHINE,$strKeyPath).sNames

if ($arrValues) {
for ($i=0; $i -lt $arrValues.Length; $i++) {
$strParameters=$strParameters + $reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,$arrValues[$i]).svalue + ";"
}
return $strParameters
}
}

##############################################################################
# Main Program
##############################################################################
[String] $strUpsertSql=""

[String] $instanceRegPath = '' # Registry path for the instance

if ( $help ) {
"Usage: Upsert-Server -serverName <string[]> <<-hostName <string[]>|-clusterName <string[]>> -status <string[]>"
exit 0
}

if ( $instanceName.Length -eq 0 ) {
"Please enter an instance name."

if ($instanceName -ieq 'mssqlserver') {
$instanceName='MSSQLSERVER'
}
exit 1
}

if (( $hostName.Length -eq 0) -and ($clusterName.Length -eq 0)) {
"Please enter a host name or a cluster name."
exit 1
}

if (( $hostName.Length -gt 0) -and ($clusterName.Length -gt 0)) {
"You only need to enter either a host name or a cluster name."
exit 1
}

if ( $status -notmatch '^D|Q|P|U|R$' ) {
"The status is invalid. Please enter D, Q, P, U or R."
exit 1
}

[String] $sqlNetworkName="" # For standalone host, it is the same as $hostName. For cluster, it is the same as $clusterName
[String] $windowsNetworkName="" # For standalone host, it is the same as $hostName. For cluster, it is the WindowsClusterName from the Clusters table.

if ($hostName.Length -gt 0) {
$sqlNetworkName=$hostName
$windowsNetworkName=$hostName
}
else {
$sqlNetworkName=$clusterName

# Find the Windows Cluster Name
$strQuerySql="SELECT WindowsClusterName FROM Clusters WHERE SQLClusterName='$clusterName'"
$sqlCluster=Invoke-Sqlcmd -Query $strQuerySql -ServerInstance $inventoryServer -Database $inventoryDatabase

$windowsNetworkName=$sqlCluster.WindowsClusterName
}

$tcpPort=(getTcpPort $windowsNetworkName $instanceName)
# If tcpPort is not available, the server or the host doesn't exist.
if ($tcpPort -eq "") {
"Tcp port is not found. Please check the server name and the host/cluster name."
exit 2
}

if ($hostName.Length -gt 0) {
$strUpsertSql = $strUpsertSql + "exec uspUpsertServers '$instanceName', '$status', '$hostName', Null, '$tcpPort', "
}
else {
$strUpsertSql = $strUpsertSql + "exec uspUpsertServers '$instanceName', '$status', Null, '$clusterName', '$tcpPort', "
}

$strUpsertSql = $strUpsertSql + "'" + (getServerNetWorkProtocols $windowsNetworkName $instanceName) + "', "

$strQuerySql = "SELECT CASE SUBSTRING(CONVERT(nvarchar, ServerProperty ('ProductVersion')), 1, CHARINDEX('.', convert(nvarchar, ServerProperty('ProductVersion')))-1 ) WHEN '10' THEN '2008' WHEN '9' THEN '2005' WHEN '8' THEN '2000' END"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + "', "

$strQuerySql = "Select ServerProperty('Edition')"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + "', "

$strQuerySql = "Select ServerProperty('ProductVersion')"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + "', "

$strQuerySql = "Select ServerProperty('ProductLevel')"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + "', "

$strParameters =(getStartupParameters $windowsNetworkName $instanceName)
$strUpsertSql = $strUpsertSql + "'" + $strParameters + "', "

$strQuerySql = "select top 1 filename as Column1 from dbo.sysfiles"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort | Split-Path -parent) + "', "

$strErrorLog=( $strParameters.Split(";") | where {$_.StartsWith("-e")} )
$strUpsertSql = $strUpsertSql + "'" + $strErrorLog.Substring(2, $strErrorLog.Length-2) + "', "

$strQuerySql = "Select ServerProperty('Collation')"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + "', "

$strQuerySql = ( getConfigSql "min server memory" )
$strUpsertSql = $strUpsertSql + ( execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + ", "

$strQuerySql = ( getConfigSql "max server memory" )
$strUpsertSql = $strUpsertSql + ( execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + ", "

$strQuerySql = ( getConfigSql "awe enabled" )
$strUpsertSql = $strUpsertSql + ( execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + ", "

$strQuerySql = ( getConfigSql "user connections" )
$strUpsertSql = $strUpsertSql + ( execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + " "

$strUpsertSql = $strUpsertSql + ";"

$strUpsertSql

Invoke-Sqlcmd -Query $strUpsertSql -ServerInstance $inventoryServer -Database $inventoryDatabase
mathiasfatene
mathiasfatene
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 391
Hi MickyD
Just modify your getStartupParameters function, and it works as a charm on all versions.


####################################### Mathias FATENE : New code###########################
# This function connects to the HKLM registry hive of the SQL Server host $pHostName
# and retrieve the startup parameters used by the instance $pInstanceName.
function getStartupParameters([String] $pHostName, [String] $pInstanceName)
{

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$server =$pHostName
$SQLServices = Get-WmiObject -ComputerName $server -Class win32_service -Filter "State='Running'" | where {($_.name -like "MSSQL$*" -and $_.name -like "*") -or $_.name -eq "MSSQLSERVER"}
$wmiserver = New-Object "Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer" $server

foreach($sqlServer in $SQLServices) {
$servicename="MSSQL$"+$pInstanceName
$servicename=$servicename -replace (' ',"")
if ('MSSQL$'+"$pInstanceName" -eq $sqlServer.Name)
{
$startupParameters = $wmiserver.Services[$sqlServer.Name].StartupParameters
$startupParameters
}
}
return $startupParameters
}
####################################### Mathias FATENE ###########################
texas1236
texas1236
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 220
I worked on a similar solution to collect Windows and SQL Server inventory by gathering scripts from SQL Family and released it as a free tool at http://crazydba.com. The tool uses powershell to grab data from all servers and stores it in SQL tables and can be viewed using SSRS reports. Can you please check it and give me any suggestions to improve it?

Thnx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search