在这个脚本中,我从我的SQL服务器中提取信息。我创建了psobject,然后尝试将每个对象添加到$results表中,以便我可以得到完整的最终报告。
由于某些原因,如果我运行完整的脚本,它会给出错误:
Method invocation failed because [system.management.managementobject] does not contain a method named 'op_Addition'
但是,如果我逐行运行脚本,我不会出现错误。
Method invocation failed because [system.management.managementobject] does not contain a method named 'op_Addition'
但是,如果我逐行运行脚本,我不会出现错误。
$Results = @()
$servers = get-content .\computers.txt
ForEach ($server in $servers) {
# Ping the machine to see if it's on the network
$results = Get-WMIObject -query "select StatusCode from
Win32_PingStatus where Address = '$server'"
$responds = $false
ForEach ($result in $results) {
# If the machine responds break out of the result loop and indicate success
if ($result.statuscode -eq 0) {
$responds = $true
break
}
}
If ($responds) {
# Gather info from the server because it responds
Write-Output "$server responds"
} else {
# Let the user know we couldn't connect to the server
Write-Output "$server does not respond"
}
# Check to see if a directory exists for this machine, if not create one
if (!(Test-Path -path .\$server)) {
New-Item .\$server\ -type directory
}
#get-WMI-Information
function getwmiinfo ($srv) {
# Get ComputerSystem info and write it to a CSV file
gwmi -query "select * from
Win32_ComputerSystem" -computername $srv | select Name,
Model, Manufacturer, Description, DNSHostName,
Domain, DomainRole, PartOfDomain, NumberOfProcessors,
SystemType, TotalPhysicalMemory, UserName,
Workgroup | export-csv -path .\$srv\BOX_ComputerSystem.csv -noType
# Get OperatingSystem info and write it to a CSV file
gwmi -query "select * from
Win32_OperatingSystem" -computername $srv | select Name,
Version, FreePhysicalMemory, OSLanguage, OSProductSuite,
OSType, ServicePackMajorVersion, ServicePackMinorVersion |
export-csv -path .\$server\BOX_OperatingSystem.csv -noType
# Get PhysicalMemory info and write it to a CSV file
gwmi -query "select * from
Win32_PhysicalMemory" -computername $srv | select Name,
Capacity, DeviceLocator, Tag |
export-csv -path .\$srv\BOX_PhysicalMemory.csv -noType
# Get LogicalDisk info and write it to a CSV file
gwmi -query "select * from Win32_LogicalDisk
where DriveType=3" -computername $srv | select Name, FreeSpace,
Size | export-csv -path .\$srv\BOX_LogicalDisk.csv -noType
}
# Get the server info
getwmiinfo $server
#Get-SQLInfo
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
# Create an ADO.Net connection to the instance
$cn = new-object system.data.SqlClient.SqlConnection(
"Data Source=$server;Integrated Security=SSPI;Initial Catalog=master");
# Create an SMO connection to the instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server
# Set the CSV output file name and pipe the instances Information collection to it
$outnm = ".\" + $server + "\" + $instnm + "GEN_Information.csv"
$s.Information | export-csv -path $outnm -noType
# Set ShowAdvancedOptions ON for the query
$s.Configuration.ShowAdvancedOptions.ConfigValue = 1
$s.Configuration.Alter()
# Create a DataSet for our configuration information
$ds = new-object "System.Data.DataSet" "dsConfigData"
# Build our query to get configuration, session and lock info, and execute it
$q = "exec sp_configure;
"
$q = $q + "exec sp_who;
"
$q = $q + "exec sp_lock;
"
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$da.Fill($ds)
# Build datatables for the config data, load them from the query results, and write them to CSV files
$dtConfig = new-object "System.Data.DataTable" "dtConfigData"
$dtWho = new-object "System.Data.DataTable" "dtWhoData"
$dtLock = new-object "System.Data.DataTable" "dtLockData"
$dtConfig = $ds.Tables[0]
$dtWho = $ds.Tables[1]
$dtLock = $ds.Tables[2]
$outnm = ".\" + $server + "\" + $instnm + "GEN_Configure.csv"
$dtConfig | select name, minimum, maximum, config_value, run_value | export-csv -path $outnm -noType
$outnm = ".\" + $server + "\" + $instnm + "GEN_Who.csv"
$dtWho | select spid, ecid, status, loginame, hostname, blk, dbname, cmd, request_id | export-csv -path $outnm -noType
$outnm = ".\" + $server + "\" + $instnm + "GEN_Lock.csv"
$dtLock | select spid, dbid, ObjId, IndId, Type,Resource, Mode, Status | export-csv -path $outnm -noType
# Set ShowAdvancedOptions OFF now that we're done with Config
$s.Configuration.ShowAdvancedOptions.ConfigValue = 0
$s.Configuration.Alter()
# Write the login name and default database for Logins to a CSV file
$outnm = ".\" + $server + "\" + $instnm + "GEN_Logins.csv"
$s.Logins | select Name, DefaultDatabase | export-csv -path $outnm -noType
# Write information about the databases to a CSV file
$outnm = ".\" + $server + "\" + $instnm + "GEN_Databases.csv"
$dbs = $s.Databases
$dbs | select Name, Collation, CompatibilityLevel, AutoShrink,
RecoveryModel, Size, SpaceAvailable | export-csv -path $outnm -noType
foreach ($db in $dbs) {
# Write the information about the physical files used by the database to CSV files for each database
$dbname = $db.Name
if ($db.IsSystemObject) {
$dbtype = "_SDB"
} else {
$dbtype = "_UDB"
}
# Write the user information to a CSV file
$users = $db.Users
$outnm = ".\" + $server + "\" + $instnm + $dbname + "_Users.csv"
$users | select $dbname, Name, Login, LoginType, UserType, CreateDate |
export-csv -path $outnm -noType
$fgs = $db.FileGroups
foreach ($fg in $fgs) {
$files = $fg.Files
$outnm = ".\" + $server + "\" + $instnm + $dbname + "_DataFiles.csv"
$files | select $db.Name, Name, FileName, Size,
UsedSpace | export-csv -path $outnm -noType
}
$logs = $db.LogFiles
$outnm = ".\" + $server + "\" + $instnm + $dbname + "_LogFiles.csv"
$logs | select $db.Name, Name, FileName, Size, UsedSpace |
export-csv -path $outnm -noType
}
# Create CSV files for each ErrorLog file
$outnm = ".\" + $server + "\" + $instnm + "_ERL_ErrorLog_.csv"
$s.ReadErrorLog() | export-csv -path $outnm -noType
$outnm = ".\" + $server + "\" + $instnm + "_ERL_ErrorLog_1.csv"
$s.ReadErrorLog(1) | export-csv -path $outnm -noType
$outnm = ".\" + $server + "\" + $instnm + "_ERL_ErrorLog_2.csv"
$s.ReadErrorLog(2) | export-csv -path $outnm -noType
$outnm = ".\" + $server + "\" + $instnm + "_ERL_ErrorLog_3.csv"
$s.ReadErrorLog(3) | export-csv -path $outnm -noType
$outnm = ".\" + $server + "\" + $instnm + "_ERL_ErrorLog_4.csv"
$s.ReadErrorLog(4) | export-csv -path $outnm -noType
$outnm = ".\" + $server + "\" + $instnm + "_ERL_ErrorLog_5.csv"
$s.ReadErrorLog(5) | export-csv -path $outnm -noType
$outnm = ".\" + $server + "\" + $instnm + "_ERL_ErrorLog_6.csv"
$s.ReadErrorLog(6) | export-csv -path $outnm -noType
$outnm = ".\" + $server + "\" + $instnm + "Gen_Information.csv"
$Gen_Information = Import-Csv $outnm
$outnm = ".\" + $server + "\" + $instnm + "Gen_Databases.csv"
$Gen_Databases = Import-Csv $outnm
$outnm = ".\" + $server + "\" + $instnm + "Box_ComputerSystem.csv"
$Box_ComputerSystem = Import-Csv $outnm
$outnm = ".\" + $server + "\" + $instnm + "Box_OperatingSystem.csv"
$Box_OperatingSystem = Import-Csv $outnm
$Gen_Info = $Gen_Information[0]
$Gen_DBs = ForEach($object in $Gen_Databases){$object.Name}
$Box_OS = $Box_OperatingSystem[0]
$Box_CS = $Box_ComputerSystem[0]
$Box_OSOS = $box_os.Name.Split("|")
#region Correct SQL Version Name
#correct Version Name
if($Gen_Info.VersionString -like "8.*.*.*")
{
$Gen_InfoVers = "SQL Server 2000"
}
elseif($Gen_Info.VersionString -like "12.*.*.*")
{
$Gen_InfoVers = "SQL Server 2014"
}
elseif($Gen_Info.VersionString -like "10.*.*.*" -and $Gen_info.VersionString -notlike "10.5*.*.*")
{
$Gen_InfoVers = "SQL Server 2008"
}
elseif($Gen_Info.VersionString -like "9.*.*.*")
{
$Gen_InfoVers = "SQL Server 2005"
}
elseif($Gen_Info.VersionString -like "10.5*.*.*")
{
$Gen_InfoVers = "SQL Server 2008r2"
}
elseif($Gen_Info.VersionString -like "11.*.*.*")
{
$Gen_InfoVers = "SQL Server 2012"
}
elseif($Gen_Info.VersionString -like "13.*.*.*")
{
$Gen_InfoVers = "SQL Server 2016"
}
else{$Gen_InfoVers = "Unknown Version"}
#endregion
$object = New-Object psobject -Property @{
Server = $Box_CS.Name
OS = $box_osos[0]
Edition = $Gen_Info.EngineEdition
SQLVersion = $Gen_InfoVers.ToString()
TotalDBs = $Gen_DBs.count
DatabaseName1 = $Gen_DBs[0]
DatabaseName2 = $Gen_DBs[1]
DatabaseName3 = $Gen_DBs[2]
DatabaseName4 = $Gen_DBs[3]
DatabaseName5 = $Gen_DBs[4]
DatabaseName6 = $Gen_DBs[5]
DatabaseName7 = $Gen_DBs[6]
DatabaseName8 = $Gen_DBs[7]
DatabaseName9 = $Gen_DBs[8]
DatabaseName10 = $Gen_DBs[9]
DatabaseName11 = $Gen_DBs[10]
DatabaseName12 = $Gen_DBs[11]
DatabaseName13 = $Gen_DBs[12]
DatabaseName14 = $Gen_DBs[13]
DatabaseName15 = $Gen_DBs[14]
DatabaseName16 = $Gen_DBs[15]
DatabaseName17 = $Gen_DBs[16]
DatabaseName18 = $Gen_DBs[17]
DatabaseName19 = $Gen_DBs[18]
DatabaseName20 = $Gen_DBs[19]
DatabaseName21 = $Gen_DBs[20]
DatabaseName22 = $Gen_DBs[21]
DatabaseName23 = $Gen_DBs[22]
DatabaseName24 = $Gen_DBs[23]
DatabaseName25 = $Gen_DBs[24]
DatabaseName26 = $Gen_DBs[25]
}
$object = $object | select Server,OS,SQLVersion,Edition,TotalDBs,DatabaseName1,DatabaseName2,DatabaseName3,DatabaseName4,DatabaseName5,DatabaseName6,DatabaseName7,DatabaseName8,DatabaseName9,DatabaseName10,DatabaseName11,DatabaseName12,DatabaseName13,DatabaseName14,DatabaseName15,
DatabaseName16,DatabaseName17,DatabaseName18,DatabaseName19,DatabaseName20,DatabaseName21,DatabaseName22,DatabaseName23,DatabaseName24,DatabaseName25,DatabaseName26
$object | Export-Csv ".\$server\FinalReport.csv" -NoTypeInformation -Force
$Results += $object
}
$Results | Export-Csv ".\Final\FinalReport.csv" -NoTypeInformation -Force
".\$server\${instnm}GEN_Configure.csv"
代替".\" + $server + "\" + $instnm + "GEN_Configure.csv"
。尝试重新格式化所有内容。而$Results += $object
应该是$Results += @($object)
。 - fdafadf