(Download it). That script retrieve cpu, memory, OS, interfaces, disks informations using SQL server queries. To use it, modify next lines by yours.
$hashOptions["strServerInstance"] = "DBserver\DBInstance"
$hashOptions["strDatabase"] = "SMS_XXX"
#
#
# Export SCCM Infos
#
# by F.Richard 2010-09
#
set-psdebug -strict
# Script Directory
$strCurDir = Split-Path -parent $MyInvocation.MyCommand.Path
Set-Location $strCurDir | Out-Null
#Write-Host "Current Dir: $strCurDir"
#$debug = $True
$debug = $False
# ***********************************************
Function executeSQLQuery {
Param ([hashtable]$options)
$strServerInstance = $(if ($options.ContainsKey("strServerInstance")) {$options["strServerInstance"]} else {"(local)"}) # ex: srv\inst
$strConnectionName = $(if ($options.ContainsKey("strConnectionName")) {$options["strConnectionName"]} else {"connectionname"})
$strDatabase = $(if ($options.ContainsKey("strDatabase")) {$options["strDatabase"]} else {"default"})
$strCommandTimeout = $(if ($options.ContainsKey("strCommandTimeout")) {$options["strCommandTimeout"]} else {"15"})
$strUser = $(if ($options.ContainsKey("strUser")) {"User Id=" + $options['strUser'] + ";"} else {""})
$strPassword = $(if ($options.ContainsKey("strPassword")) {"Password=" + $options['strPassword'] + ";"} else {""})
$strAuthentication = $(if ($strUser -and $strPassword) {"$strUser$strPassword"} else {"Integrated Security=SSPI"})
$strQuery = $(if ($options.ContainsKey("strQuery")) {$options['strQuery']} else {""})
If ($debug) {
Write-Host "DEBUG: strServerInstance:" $strServerInstance
Write-Host "DEBUG: strConnectionName:" $strConnectionName
Write-Host "DEBUG: strDatabase:" $strDatabase
Write-Host "DEBUG: strCommandTimeout:" $strCommandTimeout
Write-Host "DEBUG: strUser:" $strUser
Write-Host "DEBUG: strPassword:" $strPassword
Write-Host "DEBUG: strAuthentication:" $strAuthentication
Write-Host "DEBUG: strQuery:" $strQuery
}
# Create SqlConnection
$conn = New-Object ('System.Data.SqlClient.SqlConnection')
If (!$conn) {
Write-Host "ERROR: Sql Connection could not be created!"
Exit
}
# http://www.connectionstrings.com/ for more details
# "Integrated Security=SSPI" = "Trusted_Connection=true" ->is telling SQL Server to use windows authentication -> User Id & Password not used
$connString = "Server=$strServerInstance;$strAuthentication;Database=$strDatabase;Application Name=$strConnectionName"
If ($debug) {
Write-Host "DEBUG: Sql Connection String:" $connString
}
$conn.ConnectionString = $connString
$dtResult = New-Object "System.Data.DataTable"
$conn.Open()
If ($conn.State -eq 1) {
$sqlCmd = New-Object "System.Data.SqlClient.SqlCommand"
If ($sqlCmd) {
$sqlCmd.CommandTimeout = $strCommandTimeout
$sqlCmd.CommandText = $strQuery
$sqlCmd.Connection = $conn
# INSERT, UPDATE or DELETE ExecuteNonQuery()
# SELECT ExecuteReader()
$data = $sqlCmd.ExecuteReader()
$dtResult.Load($data) # fill
$data.Dispose()
$sqlCmd.Dispose() # frees all resources that were used by the object.
} Else {
Write-Host "ERROR: Cannot create SqlCommand object!";
}
} Else {
Write-Host "ERROR: Connection cannot be opened!";
}
$conn.Close()
$conn = $Null
#$dtResult | Format-Table -autosize
return $dtResult
}
# ***********************************************
Function getServersList {
$yesterday = Get-Date((Get-Date).AddDays(-1)) -uformat "%d/%m/%Y"
If ($debug) { Write-Host "yesterday:" $yesterday }
$query = "SELECT DISTINCT system.[Name0] AS name_machine
,comp.[Model0] AS model
,system.SystemRole0 AS name_model
,comp.[Manufacturer0] AS constructor
,comp.[Domain0] AS domain
,MAX(system2.User_Name0) AS lastLoggedInUser
FROM [v_GS_SYSTEM] system
JOIN [v_R_System] system2 ON system2.ResourceID = system.ResourceID
JOIN [v_GS_COMPUTER_SYSTEM] comp ON comp.ResourceID = system.ResourceID
JOIN [v_Gs_Workstation_Status] WStatus ON WStatus.ResourceID = comp.ResourceID
WHERE 1=1
AND convert(VarChar(11), WStatus.LastHwScan, 103) = '$yesterday' -- 103 = dd/mm/yy but here = dd/mm/yyyy
GROUP BY system.[Name0], comp.[Model0], system.SystemRole0, comp.[Manufacturer0], comp.[Domain0]
ORDER BY name_machine"
$dtResult = New-Object "System.Data.DataTable"
$hashOptions = @{ }
$hashOptions["strServerInstance"] = "DBserver\DBInstance"
$hashOptions["strDatabase"] = "SMS_XXX"
#$hashOptions["strUser"] = "user"
#$hashOptions["strPassword"] = "password"
$hashOptions["strQuery"] = $query
$dtResult = executeSQLQuery($hashOptions)
If ($dtResult -ne $Null) {
New-Item ".\SCCM_Hardware.csv" -type file -force | out-null
Foreach ($row in $dtResult){
$query = "SELECT [SerialNumber0]
FROM V_GS_PC_BIOS bios
LEFT OUTER JOIN v_GS_SYSTEM system
ON bios.ResourceID = system.ResourceID
WHERE UPPER(system.Name0)='" + $row.name_machine.ToUpper() + "'"
$hashOptions["strQuery"] = $query
$dtResult2 = executeSQLQuery($hashOptions)
If ($dtResult2 -ne $Null) {
Foreach ($row2 in $dtResult2){
$result = $row.name_machine + ";" + $row.name_model + ";" + $row.constructor + ";" + $row.model + ";" + $row.domain + ";" + $row.lastLoggedInUser + ";" + $row2.SerialNumber0
$result | out-file -encoding OEM ".\SCCM_Hardware.csv" -append
}
}
}
# Memory
New-Item ".\SCCM_Memory.csv" -type file -force | out-null
Foreach ($row in $dtResult){
$query = "SELECT mem.[TotalPhysicalMemory0] AS totalphysicalmem -- 3407160
FROM v_GS_X86_PC_MEMORY mem
LEFT OUTER JOIN v_GS_SYSTEM system ON system.ResourceID = mem.ResourceID
WHERE UPPER(system.Name0)='" + $row.name_machine.ToUpper() + "'
AND mem.RevisionID = (
SELECT MAX(mem2.RevisionID) FROM v_GS_X86_PC_MEMORY mem2
LEFT OUTER JOIN v_GS_SYSTEM system2 ON system2.ResourceID = mem2.ResourceID
WHERE UPPER(system2.Name0)='" + $row.name_machine.ToUpper() + "')"
$hashOptions["strQuery"] = $query
$dtResult2 = executeSQLQuery($hashOptions)
If ($dtResult2 -ne $Null) {
Foreach ($row2 in $dtResult2){
$result = $row.name_machine + ";" + $row2.totalphysicalmem
$result | out-file -encoding OEM ".\SCCM_Memory.csv" -append
}
}
}
# CPU
New-Item ".\SCCM_CPU.csv" -type file -force | out-null
Foreach ($row in $dtResult){
$query = "SELECT process.nb_socket
,COUNT(DISTINCT processor.DeviceID0) AS number_core
,processor.[MaxClockSpeed0] AS frequency
,RTRIM(LTRIM(processor.Name0)) AS type_cpu
FROM [v_GS_PROCESSOR] processor
LEFT OUTER JOIN v_GS_SYSTEM system
ON processor.ResourceID = system.ResourceID
LEFT OUTER JOIN (SELECT p.ResourceID, COUNT(DISTINCT p.[SocketDesignation0]) AS nb_socket FROM [v_GS_PROCESSOR] p GROUP BY p.ResourceID)
process ON process.ResourceID = system.ResourceID
WHERE UPPER(system.Name0) = '" + $row.name_machine.ToUpper() + "'" + "
GROUP BY process.nb_socket
,processor.[MaxClockSpeed0]
,processor.Name0"
$hashOptions["strQuery"] = $query
$dtResult2 = executeSQLQuery($hashOptions)
If ($dtResult2 -ne $Null) {
Foreach ($row2 in $dtResult2){
$result = $row.name_machine + ";" + $row2.nb_socket + ";" + $row2.number_core + ";" + $row2.frequency + ";" + $row2.type_cpu
$result | out-file -encoding OEM ".\SCCM_CPU.csv" -append
}
}
}
# Interfaces
New-Item ".\SCCM_Interfaces.csv" -type file -force | out-null
Foreach ($row in $dtResult){
$query = "SELECT netadapt.[DefaultIPGateway0] AS gateway
,netadapt.[DHCPServer0] AS dhcp
,netadapt.[IPAddress0] AS ip
,netadapt.[IPSubnet0] AS mask
,netadapt.[MACAddress0] mac_interface
FROM v_GS_SYSTEM system
LEFT OUTER JOIN v_GS_NETWORK_ADAPTER_CONFIGUR netadapt ON
netadapt.ResourceID = system.ResourceID
WHERE UPPER(system.Name0)= '" + $row.name_machine.ToUpper() + "' AND
DefaultIPGateway0 IS NOT NULL"
$hashOptions["strQuery"] = $query
$dtResult2 = executeSQLQuery($hashOptions)
If ($dtResult2 -ne $Null) {
Foreach ($row2 in $dtResult2){
$result = $row.name_machine + ";" + $row2.gateway + ";" + $row2.dhcp + ";" + $row2.ip + ";" + $row2.mask + ";" + $row2.mac_interface
$result | out-file -encoding OEM ".\SCCM_Interfaces.csv" -append
}
}
}
# Os
New-Item ".\SCCM_OS.csv" -type file -force | out-null
Foreach ($row in $dtResult){
$query = "SELECT [Caption0] AS capsystem
,[CSDVersion0] AS patch
,[InstallDate0] AS date_install
,[LastBootUpTime0] AS lastbootuptime
,[Version0] AS version_os
FROM v_GS_OPERATING_SYSTEM os
LEFT OUTER JOIN v_GS_SYSTEM system ON system.ResourceID = os.ResourceID
WHERE UPPER(system.Name0) = '" + $row.name_machine.ToUpper() + "'"
$hashOptions["strQuery"] = $query
$dtResult2 = executeSQLQuery($hashOptions)
If ($dtResult2 -ne $Null) {
Foreach ($row2 in $dtResult2){
$result = $row.name_machine + ";" + $row2.capsystem + ";" + $row2.patch + ";" + $row2.date_install + ";" + $row2.lastbootuptime + ";" + $row2.version_os
$result | out-file -encoding OEM ".\SCCM_OS.csv" -append
}
}
}
# Disk
New-Item ".\SCCM_Disk.csv" -type file -force | out-null
Foreach ($row in $dtResult){
$query = "SELECT LTRIM(RTRIM([Description0])) AS id_type_disk -- local Fixed Disk or CD-ROM Disc
,[DeviceID0] AS letter -- D: or Z:
,[FileSystem0] AS id_file_system -- NTFS, FAT32, etc.
,[FreeSpace0] AS free_space
,system.[Name0] AS id_machine
,[Size0] AS size
,[VolumeName0] AS volume_name
,[VolumeSerialNumber0] AS volume_serial_number
FROM [v_GS_LOGICAL_DISK] logdisk
LEFT OUTER JOIN [v_GS_SYSTEM] system ON system.ResourceID = logdisk.ResourceID
WHERE UPPER(system.Name0) = '" + $row.name_machine.ToUpper() + "' AND [FileSystem0] IS NOT NULL"
$hashOptions["strQuery"] = $query
$dtResult2 = executeSQLQuery($hashOptions)
If ($dtResult2 -ne $Null) {
Foreach ($row2 in $dtResult2){
$result = $row.name_machine + ";" + $row2.id_type_disk + ";" + $row2.letter + ";" + $row2.id_file_system + ";" + $row2.free_space + ";" + $row2.id_machine + ";" + $row2.size + ";" + $row2.volume_name + ";" + $row2.volume_serial_number
$result | out-file -encoding OEM ".\SCCM_Disk.csv" -append
}
}
}
}
$dtResult = $Null
# FTP all .CSV
$today = Get-Date -uformat "%Y-%m-%d" # 2010-03-01
If (!(Test-Path "$strCurDir\log")) {
New-Item -Path "$strCurDir\log" -type directory | Out-Null
}
$ftp = "
open yourftpserver
ftpuser
ftppassword
mput `"$strCurDir\*.csv`"
quit
"
$ftp | out-file -encoding OEM ".\ftp-send.txt"
$cmdline = "ftp -i -s:`"" + $strCurDir + "\ftp-send.txt`" > `"" + $strCurDir + "\log\ftp_$today.txt`""
If ($debug) { Write-Host $cmdline }
cmd /c $cmdline
}
# ***********************************************
getServersList
