Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL inventory powershell script Expand / Collapse
Author
Message
Posted Saturday, September 7, 2013 5:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:21 PM
Points: 229, Visits: 1,154
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
Post #1492509
Posted Thursday, November 7, 2013 10:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 3, Visits: 233
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 ###########################
Post #1512367
Posted Friday, April 11, 2014 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 2:57 PM
Points: 4, Visits: 191
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
Post #1561124
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse