My auto audit script

[ACooperman]

All,

I thought I would post this useful nugget of info. I will admit the script is a little on the messy side but it works and some of you may find it helpful.

Please post any improvements or suggestions that you may find.

# Auto Audit Script by Adam Cooperman
#
# This script can be used to automatically audit the following information into excel 2007.
#
# • Machine name
# • Make
# • Model
# • Operating system
# • Service pack
# • Initial install date
# • Last start-up time
# • RAM
# • Paging file size
# • Number of processors
# • Number of cores
# • Processor speed
# • System architecture (32/64 bit)
# • Processor name/type
# • Free space for all drives
# • Total size of all drives
# • Free space percentage for all drives
# • Software
# o Name
# o Description
# o ID Number
# o Installation Date
# o Installation Location
# o Installation State
# o Caption
# o Package Cache
# o SKU Number (mostly unused)
# o Vendor
# o Version
# • Active directory groups (filterable for membership by group)
# • Active directory users (filterable for membership by user)
#
# Usage: To use this script simply place a file named server.txt in the root of c:\
# this file should contain the name of every server you wish the script to audit
# in the following format:
#
# Server1
# Server2
# Server3
#
# A worksheet within Excel will be created for each server the script is run against as well as
# several generic worksheets named: AD Groups, Drive Info and General Info.
#
# General Info: Machine Name, Make, Model, Operating System, Service Pack, First Installed, Last Startup time,
# RAM, Paging File, Number of Processors, Number of Cores, Processor Speed, System Architecture and Processor Name.
#
# Drive Info: Machine Name, Drive, Total Size (MB), Free Space (MB) and Free Space (%).
#
# AD Groups: Group/User I recommend that a freeze is done on the top row along with filters, normally I would
# also transpose the information into a new sheet and apply the freeze and filter. This allows people
# to filter by group or user allowing easy access to relevant information.
#
#
#
#

$erroractionpreference = "SilentlyContinue"
$xl = New-Object -comobject Excel.Application
$xl.visible = $True

$wb = $xl.Workbooks.Add()
$s3 = $wb.Sheets | where {$_.name -eq "Sheet3"}
$s3.delete()
$ws = $wb.Worksheets.Item(1)

$ws.Name = "Drive Info"

$ws.Cells.Item(1,1) = "Machine Name"
$ws.Cells.Item(1,2) = "Drive"
$ws.Cells.Item(1,3) = "Total size (MB)"
$ws.Cells.Item(1,4) = "Free Space (MB)"
$ws.Cells.Item(1,5) = "Free Space (%)"

$d = $ws.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()

$intRow = 2

$servers = get-content c:\servers.txt.txt
foreach ($srv in $servers)
{
$objDisks = get-wmiobject Win32_LogicalDisk -computername $srv -Filter "DriveType = 3"
foreach ($objdisk in $objDisks)
{
$ws.Cells.Item($intRow, 1) = $srv.ToUpper()
$ws.Cells.Item($intRow, 2) = $objDisk.DeviceID
$ws.Cells.Item($intRow, 3) = "{0:N0}" -f ($objDisk.Size/1MB)
$ws.Cells.Item($intRow, 4) = "{0:N0}" -f ($objDisk.FreeSpace/1MB)
$ws.Cells.Item($intRow, 5) = "{00}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)
$intRow = $intRow + 1
}
}


#-------------------------------------

$ws = $wb.Worksheets.Item(2)

$ws.Name = "General Info"

$ws.Cells.Item(1,1) = "Machine Name"
$ws.Cells.Item(1,2) = "Make"
$ws.Cells.Item(1,3) = "Model"
$ws.Cells.Item(1,4) = "Operating System"
$ws.Cells.Item(1,5) = "Service Pack"
$ws.Cells.Item(1,6) = "First Installed"
$ws.Cells.Item(1,7) = "Last Startup Time"
$ws.Cells.Item(1,8) = "RAM"
$ws.Cells.Item(1,9) = "Paging File"
$ws.Cells.Item(1,10) = "Number of Processors"
$ws.Cells.Item(1,11) = "Number of Cores"
$ws.Cells.Item(1,12) = "Processor Speed"
$ws.Cells.Item(1,13) = "System Architecture"
$ws.Cells.Item(1,14) = "Processor Name"

$d = $ws.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()

$intRow = 2

$servers = get-content c:\servers.txt.txt
foreach ($srv in $servers)
{

$objMEMOR = get-wmiobject -Class win32_logicalmemoryconfiguration -ComputerName $srv
$objOPSYS = get-wmiobject Win32_Operatingsystem -computername $srv
$objCOMPS = Get-WmiObject win32_computersystem -computername $srv
$objPROCE = Get-WmiObject win32_processor -computername $srv

$objopsys1 = $objopsys.ConvertToDateTime($objopsys.installdate)
$objopsys2 = $objopsys.ConvertToDateTime($objopsys.lastbootuptime)

$ws.Cells.Item($intRow, 1) = $srv.ToUpper()
$ws.Cells.Item($intRow, 2) = $objCOMPS.Manufacturer
$ws.Cells.Item($intRow, 3) = $objCOMPS.Model
$ws.Cells.Item($intRow, 4) = $objOPSYS.Caption
$ws.Cells.Item($intRow, 5) = $objOPSYS.CSDVersion
$ws.Cells.Item($intRow, 6) = $objOPSYS1
$ws.Cells.Item($intRow, 7) = $objOPSYS2
$ws.Cells.Item($intRow, 8) = ($objMEMOR.totalphysicalmemory/1mb)
$ws.Cells.Item($intRow, 9) = ($objMEMOR.totalpagefilespace/1mb)
$ws.Cells.Item($intRow, 10) = $objCOMPS.numberofprocessors
$ws.Cells.Item($intRow, 11) = $objCOMPS.numberoflogicalprocessors
$ws.Cells.Item($intRow, 12) = $objPROCE.maxclockspeed
$ws.Cells.Item($intRow, 13) = $objCOMPS.Systemtype
$ws.Cells.Item($intRow, 14) = $objPROCE.name

$intRow = $intRow + 1

}

#-----------------------------------------------------------

Foreach ($srv in $servers) {

$wb.Sheets.Add()
$ws = $wb.Worksheets.Item(1)
$ws.Name = $srv

$ws.Cells.Item(1,1) = "Caption"
$ws.Cells.Item(1,2) = "Description"
$ws.Cells.Item(1,3) = "Identifying Number"
$ws.Cells.Item(1,4) = "Installation Date"
$ws.Cells.Item(1,5) = "Installation Date 2"
$ws.Cells.Item(1,6) = "Installation Location"
$ws.Cells.Item(1,7) = "Installation State"
$ws.Cells.Item(1,8) = "Name"
$ws.Cells.Item(1,9) = "Package Cache"
$ws.Cells.Item(1,10) = "SKU Number"
$ws.Cells.Item(1,11) = "Vendor"
$ws.Cells.Item(1,12) = "Version"

$d = $ws.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()


$intRow = 2


$colPROGR = get-wmiobject -class Win32_Product -computername $srv



Foreach ($objprogr in $colPROGR) {
$objPROGR1 = $objPROGR.ConvertToDateTime($objPROGR.installdate2)
$ws.Cells.Item($intRow, 1) = $objPROGR.Caption
$ws.Cells.Item($intRow, 2) = $objPROGR.Description
$ws.Cells.Item($intRow, 3) = $objPROGR.IdentifyingNumber
$ws.Cells.Item($intRow, 4) = $objPROGR.InstallDate
$ws.Cells.Item($intRow, 5) = $objPROGR1
$ws.Cells.Item($intRow, 6) = $objPROGR.InstallLocation
$ws.Cells.Item($intRow, 7) = $objPROGR.InstallState
$ws.Cells.Item($intRow, 8) = $objPROGR.Name
$ws.Cells.Item($intRow, 9) = $objPROGR.PackageCache
$ws.Cells.Item($intRow, 10) = $objPROGR.SKUNumber
$ws.Cells.Item($intRow, 11) = $objPROGR.Vendor
$ws.Cells.Item($intRow, 12) = $objPROGR.Version
$intRow = $intRow + 1
}

}


#----------------------------------------------------------------


$wb.Sheets.Add()
$ws = $wb.Worksheets.Item(1)
$ws.Name = "AD Groups"

$ws.Cells.Item(1,1) = "Name"
$ws.Cells.Item(1,2) = "DN"

$groups = Get-QADGroup -SizeLimit 0
$intCol = 2
$grlook = @{}
foreach ($group in $groups) {
$grlook.Add($group.dn, $intcol)
$ws.Cells.Item(1,$intCol) = $group.name
$intCol = $intCol + 1

}

$users = Get-QADUser -SizeLimit 100
$intRow = 2
$uslook = @{}
foreach ($user in $users) {

$uslook.Add($user.Name, $intRow)
$ws.Cells.item($intRow,1) = $user.name

$intRow = $intRow + 1

foreach ($usgr in $user.MemberOf) {

$grcol = $grlook.get_item($usgr)
$usrow = $uslook.get_item($user.Name)
$ws.Cells.item($usrow,$grcol) = "x"


}
}

 

[SilviaS]

You may want to point out that its using quest tools

http://blog.meigh.eu

 

[ACooperman]

What he said ^

 

[SilviaS]

looks good Acooperman
Can I blog it a credit it to yourself for my future reference?

http://blog.meigh.eu

 

[ACooperman]

Feel free. Sorry its quite so messy. Its just something I knocked up quickly for work.

Adam

PS post a link to your blog if possible

 

[ACooperman]

ahh just spotted your signature. Im not feeling very observant this monday morning

 

[SilviaS]

no worries

http://blog.meigh.eu

 

[Guest_imported]

Great script Adam. Any chance you can help me to get it working? I am so far only able to have it populate a few of the AD groups. Doesn't do all of them for the users.