powershell collect sql server information

#* FileName: ServerInventory.ps1
#*=============================================================================
#* Script Name: [ServerInventory]
#* Created: [12/14/07]
#* Author: Jesse Hamrick
#* Company: PowerShell Pro!
#* Email:
#* Web: http://www.powershellpro.com
# Modified by : jiulu sun
# Modified at : Oct 28th, 2010
# usage : CTV IT managed sqlservers
# How to use : run powershell, type $PathOf\filename.ps1>outputfilePath and file, it will loop all servers in file d:cvb\Computers.txt
# you need to change executionpolicy first if its at restricted mode. or you will get an error
# "File C:\vb\sqlserver_info1.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_sig
# ning" for more details."
# this is an example
# PS C:\vb> Get-ExecutionPolicy
# Restricted
# PS C:\vb> set-executionpolicy remotesigned
# PS C:\vb> Get-ExecutionPolicy
# RemoteSigned
# PS C:\vb> .\sqlserver_info1.ps1 > sqlserver.txt
#*=============================================================================
#* Purpose: Server Inventory
#*
#*
#*=============================================================================


$ONETAB+=" "
$TWOTABS=" "
$BLANKLINE=""
Function SysInfo {

$colItems = Get-WmiObject Win32_ComputerSystem -Namespace "root\CIMV2" `
-ComputerName $strComputer

foreach($objItem in $colItems) {
$ONETAB+ "Computer Manufacturer: " +$objItem.Manufacturer
$ONETAB+ "Computer Model: " +$objItem.Model
$ONETAB+ "Total Memory: " +$objItem.TotalPhysicalMemory +"bytes"
}

}

#*=============================================================================
#* FUNCTION LISTING
#*=============================================================================
#* Function: BIOSInfo
#* Created: [12/14/07]
#* Author: Jesse Hamrick
#* Arguments:
#*=============================================================================
#* Purpose: WMI Function that enumerate win32_BIOS properties
#*
#*
#*=============================================================================

Function BIOSInfo {

$colItems = Get-WmiObject Win32_BIOS -Namespace "root\CIMV2" -computername $strComputer
foreach($objItem in $colItems) {
$ONETAB+ "BIOS:"+$objItem.Description
$ONETAB+ "Version:"+$objItem.SMBIOSBIOSVersion+"."+`
$objItem.SMBIOSMajorVersion+"."+$objItem.SMBIOSMinorVersion
$ONETAB+ "Serial Number:" +$objItem.SerialNumber
}

}

#*=============================================================================
#* FUNCTION LISTING
#*=============================================================================
#* Function: OSInfo
#* Created: [12/14/07]
#* Author: Jesse Hamrick
#* Arguments:
#*=============================================================================
#* Purpose: WMI Function that enumerate win32_OperatingSystem properties
#*
#*
#*=============================================================================

Function OSInfo {

$colItems = Get-WmiObject Win32_OperatingSystem -Namespace "root\CIMV2"`
-Computername $strComputer

foreach($objItem in $colItems) {
$ONETAB+ "Operating System:" +$objItem.Name
}

}

#*=============================================================================
#* FUNCTION LISTING
#*=============================================================================
#* Function: CPUInfo
#* Created: [12/14/07]
#* Author: Jesse Hamrick
#* Arguments:
#*=============================================================================
#* Purpose: WMI Function that enumerate win32_Processor properties
#*
#*
#*=============================================================================

Function CPUInfo {

$colItems = Get-WmiObject Win32_Processor -Namespace "root\CIMV2"`
-Computername $strComputer

foreach($objItem in $colItems) {
$ONETAB+ "Processor:" +$objItem.DeviceID +$objItem.Name
}

}

#*=============================================================================
#* FUNCTION LISTING
#*=============================================================================
#* Function: DiskInfo
#* Created: [12/14/07]
#* Author: Jesse Hamrick
#* Arguments:
#*=============================================================================
#* Purpose: WMI Function that enumerate win32_DiskDrive properties
#*
#*
#*=============================================================================

Function DiskInfo {

$colItems = Get-WmiObject Win32_DiskDrive -Namespace "root\CIMV2"`
-ComputerName $strComputer

foreach($objItem in $colItems) {
$ONETAB+ "Disk:" +$objItem.DeviceID
$ONETAB+ "Name: " +$objItem.__PATH
$ONETAB+ "Size:" +$objItem.Size +"bytes"
$ONETAB+ "Drive Type:" +$objItem.InterfaceType
$ONETAB+ "Media Type: " +$objItem.MediaType
}

$colItems = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2"`
-ComputerName $strComputer

foreach($objItem in $colItems) {
$ONETAB+ "Name: " +$objItem.Name
$ONETAB+ "Size:" +$objItem.Size +"bytes"
$ONETAB+ "freespace:" +$objItem.freespace
}
}

#*=============================================================================
#* FUNCTION LISTING
#*=============================================================================
#* Function: NetworkInfo
#* Created: [12/14/07]
#* Author: Jesse Hamrick
#* Arguments:
#*=============================================================================
#* Purpose: WMI Function that enumerate win32_NetworkAdapterConfiguration
#* properties
#*
#*=============================================================================

Function NetworkInfo {

$colItems = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2"`
-ComputerName $strComputer | where{$_.IPEnabled -eq "True"}

foreach($objItem in $colItems) {
$ONETAB+ "DHCP Enabled:" +$objItem.DHCPEnabled
$ONETAB+ "IP Address:" +$objItem.IPAddress
$ONETAB+ "Subnet Mask:" +$objItem.IPSubnet
$ONETAB+ "Gateway:" +$objItem.DefaultIPGateway
$ONETAB+ "MAC Address:" +$ojbItem.MACAddress
}

}

Function sysloginInfo {
$ONETAB+ "select name,createdate,status,dbname,loginname,isntuser,sysadmin`
from master..syslogins"
sqlcmd -S $strComputer -E -Q "select ' '+cast(name as varchar(39)) as name, `
createdate,status,cast(dbname as varchar(19)) as dbname,`
cast(loginname as varchar(19)) loginname,isntuser, sysadmin `
from master..syslogins"

}

Function sysjobInfo {
$ONETAB+ "select name,enabled,description from msdb..sysjobs"
sqlcmd -S $strComputer -E -Q "select ' '+cast(a.name as varchar(29)) as name,a.enabled,`
cast(description as varchar(29)) as description, b.next_run_date,b.next_run_time, `
cast(c.command as varchar(299)) as stepcommand `
from msdb..sysjobs a, msdb..sysjobschedules b, msdb..sysjobsteps c `
where a.job_id=b.job_id and a.job_id=c.job_id"

}

Function sysdbInfo {
$ONETAB+ "select name,status,status2,crdate,cmptlevel,filename,version from sysdatabases"
sqlcmd -S $strComputer -E -Q "select ' '+cast(name as varchar(39)) as name,`
status,status2,crdate,cmptlevel `
version from sysdatabases "

}

Function sqlserverversionInfo {
$ONETAB+ "select @@version"
sqlcmd -S $strComputer -E -Q "select @@version"

}



#*=============================================================================
#* SCRIPT BODY
#*=============================================================================
#* Connect to computer
#$strComputer = "agtsds001"

$arrComputers = get-Content -Path "C:\vb\Computers.txt"

foreach ($strComputer in $arrComputers)

{ #Function Calls go here

"#########################################################################"
"Computer Name:" +$strComputer
"#########################################################################"


#* Call dbversion Function
$BLANKLINE
"@@@SQLServer version information"
sqlserverversionInfo


#* Call database syslogin Function
$BLANKLINE
"@@@SQLServer login information"
sysloginInfo


#* Call database job Function
$BLANKLINE
"@@@SQLServer agent job information"
sysjobInfo


#* Call database Function
$BLANKLINE
"@@@SQLServer database information"
sysdbInfo


####now remove the name instance.
if ($strComputer.contains("\"))
{#'this is a named instance
$position=$strComputer.indexof("\")
$strComputer = $strComputer.substring(0,$position)
"computer name is :" + $strComputer
}

#* Call SysInfo Function
"Sytem Information"
SysInfo


#* Call BIOSinfo Function
"System BIOS Information"
BIOSInfo


#* Call OSInfo Function
"Operating System Information"
OSInfo


#* Call CPUInfo Function
"Processor Information"
CPUInfo


#* Call DiskInfo Function
"Disk Information"
DiskInfo


#* Call NetworkInfo Function
"Network Information"
NetworkInfo





#*=============================================================================
#* END OF SCRIPT: [ServerInventory]
#*=============================================================================


}