<# .SYNOPSIS Collects server inventory and loads it into staging tables. .DESCRIPTION Gathering script for server inventory. Leverages InstanceInventory in the admin database to collected related instance information. Instance information is updated, machine data is overwritten. Uses Chad Miller's Out-DataTable and Write-Table functions (combined into DataTables.ps1). http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae Mike Fal (htp://www.mikefal.net) 04-16-2013\ .PARAMETER invserv - Inventory server/instance name invdb - Inventory database .EXAMPLE .\Get-ServerInventory -invserv "ADMINSERVER" -invdb "DBA" #> #init Admin server and database #parameters param([parameter(Mandatory=$true)][string] $invserv = "localhost", [parameter(Mandatory=$true)][string] $invdb = "ADMIN") #load dependecies . .\DataTables.ps1 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement') | out-null Import-Module FailOverClusters #FUNCTIONS USED BY SCRIPT #Collects and returns SQL Instance information function Get-Instance([string]$instcoll,[int]$id,[string]$name) { try { $smo = new-object ('Microsoft.SqlServer.Management.Smo.Server') $name $sname = $smo.NetName $iname = $smo.InstanceName if($iname.Length -eq 0 -or $iname -eq $null) { $iname = "MSSQLSERVER" } $managedcomp = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $sname $output = New-Object System.Object $port = $managedcomp.ServerInstances[$iname].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value $ip = (Test-Connection $sname -count 1).IPV4Address.ToString() $output | Add-Member -type NoteProperty -name InstanceId -value $id $output | Add-Member -type NoteProperty -name SQLVersion -value $smo.VersionString $output | Add-Member -type NoteProperty -name SQLVersionDesc -value $smo.ProductLevel $output | Add-Member -type NoteProperty -name SQLEdition -value $smo.Edition $output | Add-Member -type NoteProperty -name MemoryMinMB -value $smo.Configuration.MinServerMemory.RunValue $output | Add-Member -type NoteProperty -name MemoryMaxMB -value $smo.Configuration.MaxServerMemory.RunValue $output | Add-Member -type NoteProperty -name MAXDOPVal -value $smo.Configuration.MaxDegreeOfParallelism.RunValue $output | Add-Member -type NoteProperty -name IP -value $ip $output | Add-Member -type NoteProperty -name Port -value $port $output | Add-Member -type NoteProperty -name PhysicalHost -value $smo.ComputerNamePhysicalNetBIOS return $output } catch { write-host "Error collecting $name" return $null } }#Get-Instance #Get host machine information via WMI function Get-Machine([string]$name,[string]$clst) { try { $comp = gwmi Win32_ComputerSystem -Computer $name | select Model,Manufacturer,TotalPhysicalMemory $proc = gwmi Win32_Processor -Computer $name | select NumberOfLogicalProcessors,MaxClockSpeed $os = gwmi Win32_OperatingSystem -Computer $name | select OSArchitecture,Name,Version,ServicePackMajorVersion,ServicePackMinorVersion $output = New-Object System.Object $output | Add-Member -type NoteProperty -name MachineName -value $name $output | Add-Member -type NoteProperty -name Model -value $comp.Model $output | Add-Member -type NoteProperty -name Manufacturer -value $comp.Manufacturer $output | Add-Member -type NoteProperty -name Architechture -value $os.OSArchitecture $output | Add-Member -type NoteProperty -name PhysicalCPUs -value $(if(!$proc.Length){"1"}else{$proc.Length}) $output | Add-Member -type NoteProperty -name LogicalCPUs -value ($proc | Measure-Object NumberOfLogicalProcessors -sum).Sum $output | Add-Member -type NoteProperty -name CPUSpeed -value ($proc | Measure-Object MaxClockSpeed -max).Maximum $output | Add-Member -type NoteProperty -name MaxMemory -value ($comp.TotalPhysicalMemory/1MB) $output | Add-Member -type NoteProperty -name OSName -value $os.name.split("|")[0] $output | Add-Member -type NoteProperty -name OsVersion -value $os.Version $SPMaj = $os.ServicePackMajorVersion $SPMin = $os.ServicePackMinorVersion $output | Add-Member -type NoteProperty -name SPVersion -value "$SPMaj.$SPMin" $output | Add-Member -type NoteProperty -name Cluster -value $clst return $output } catch { write-host "Error collecting $name" return $null } }#Get-Machine #Bulk load data into destination table function Load-Data($dt,$cxn,$desttbl) { $bulkloader = new-object("Data.SqlClient.SqlBulkCopy") $cxn $null = $bulkloader.DestinationTableName=$desttbl $null = $bulkloader.WriteToServer($dt) }#Load-Data #Start Script #Create Admin db connection $conn = New-Object system.data.sqlclient.sqlconnection("Data Source=$invserv;Initial Catalog=$invdb;Trusted_Connection=True;") $conn.Open() $cmd = $conn.CreateCommand() #init reporting collections $instances = @() $servers = @() #clear load tables $query = "delete from dataload.InstanceLoad; delete from dataload.MachineLoad;" $cmd.CommandText=$query $null = $cmd.ExecuteNonQuery() #Load Instances $query = "SELECT InstanceID ,SQLName FROM InstanceInventory WHERE Enabled = 1" $cmd.CommandText=$query $read = $cmd.ExecuteReader() #get SQL info while($read.Read()) { $instid = $read.GetInt32(0) $inst = $read.GetString(1) $newinst = Get-Instance $instload $instid $inst if($newinst -ne $null) {$instances += $newinst} } $read.close() #get cluster machines $domain=(gwmi WIN32_ComputerSystem).Domain $clstrs = (Get-Cluster -Domain $domain) foreach($clstr in $clstrs) { $nodes = Get-ClusterNode -Cluster $clstr foreach($node in $nodes) { $newsrv = Get-Machine $node $clstr if($newsrv -ne $null) {$servers += $newsrv} } } #get any standalone machines foreach($inst in $instances) { if(!(($servers | select -expandproperty MachineName) -contains $inst.PhysicalHost)) { $stndalone = $inst.PhysicalHost $newsrv = Get-Machine $stndalone "" if($newsrv -ne $null) {$servers += $newsrv} } } #create datatables from reporting collections for loading $instload = $instances | select instanceid,sqlversion,sqlversiondesc,sqledition,IP,Port,memoryminmb,memorymaxmb,maxdopval,PhysicalHost | out-datatable $srvload = $servers | select MachineName,Model,Manufacturer,Architechture,PhysicalCPUs,LogicalCPUs,CPUSpeed,MaxMemory,OSName,OSVersion,SPVersion,Cluster | out-datatable #Bulkload data to processing tables Load-Data $instload $conn "dataload.InstanceLoad" Load-Data $srvload $conn "dataload.MachineLoad" #execute Processing sproc $query = "execute dbo.dbasp_ProcessInventory;" $cmd.CommandText=$query $null = $cmd.ExecuteNonQuery()