trying to make basic user inventory / info script

simon-t · October 28, 2013, 5:50am

get-aduser â€“filter * -property * | select-object Name, LastLogonDate, passwordneverexpires | out-file c:\users\everon\desktop\userinfotrue.csv

this is what i have, however when i add anything related to:

-organizational unit

-security group

-account disabled

the script either errors out, or i get a <> in the field.

Basically I am trying to gather: name, lastlogondate, passwordneverexpires (t/f), account disabled (t/f), organization unit and security groups this account belongs to.

could someone please help me out?

Thanks.

richard-siddaway · October 28, 2013, 6:21am

Try this
Get-ADUser -Filter * -Properties * | Select Name, LastLogondate, PasswordNeverExpires, Enabled, @{N=‘OU’; E={($($.distinguishedName) -split “,”,2)[1] }}, @{N=‘Groups’; E={$($.Memberof)}} | Export-csv -Path users.csv -NoTypeInformation

simon-t · October 28, 2013, 6:36am

You are the man!

notarat · October 31, 2013, 12:22am

I’m a little late to the thread but if you decide at some later date that you want a hardware inventory as well, you can use Jesse Hamrick’s excellent Powershell script. It creates a spreadsheet with tabs holding the information relating to various hardware specs for all your computers.

# ==============================================================================================
# 
# Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2007
# 
# NAME: Server/Workstation Inventory (CompInv_v2.ps1)
# 
# AUTHOR: Jesse Hamrick
# DATE  : 2/25/2009
# Web	: www.PowerShellPro.com
# COMMENT: Script Inventories Computers and sends results to an excel file.
# 
# ==============================================================================================

# ==============================================================================================
# Functions Section
# ==============================================================================================
# Function Name 'WMILookup' - Gathers info using WMI and places results in Excel
# ==============================================================================================
Function WMILookup {
foreach ($StrComputer in $colComputers){
		$GenItems1 = gwmi Win32_ComputerSystem -Comp $StrComputer
		$GenItems2 = gwmi Win32_OperatingSystem -Comp $StrComputer
		$SysItems1 = gwmi Win32_BIOS -Comp $StrComputer
		$SysItems2 = gwmi Win32_TimeZone -Comp $StrComputer
		$SysItems3 = gwmi Win32_WmiSetting -Comp $StrComputer
		$ProcItems1 = gwmi Win32_Processor -Comp $StrComputer
		$MemItems1 = gwmi Win32_PhysicalMemory -Comp $StrComputer
		$memItems2 = gwmi Win32_PhysicalMemoryArray -Comp $StrComputer
		$DiskItems = gwmi Win32_LogicalDisk -Comp $StrComputer
		$NetItems = gwmi Win32_NetworkAdapterConfiguration -Comp $StrComputer |`
					where{$_.IPEnabled -eq "True"}
		
				
# Populate General Sheet(1) with information
	foreach ($objItem in $GenItems1){
		$Sheet1.Cells.Item($intRow, 1) = $StrComputer
		Switch($objItem.DomainRole)
			{
			0{$Sheet1.Cells.Item($intRow, 2) = "Stand Alone Workstation"}
			1{$Sheet1.Cells.Item($intRow, 2) = "Member Workstation"}
			2{$Sheet1.Cells.Item($intRow, 2) = "Stand Alone Server"}
			3{$Sheet1.Cells.Item($intRow, 2) = "Member Server"}
			4{$Sheet1.Cells.Item($intRow, 2) = "Back-up Domain Controller"}
			5{$Sheet1.Cells.Item($intRow, 2) = "Primary Domain Controller"}
			default{"Undetermined"}
			}
		$Sheet1.Cells.Item($intRow, 3) = $objItem.Manufacturer
		$Sheet1.Cells.Item($intRow, 4) = $objItem.Model
		$Sheet1.Cells.Item($intRow, 5) = $objItem.SystemType
		$Sheet1.Cells.Item($intRow, 6) = $objItem.NumberOfProcessors
		$Sheet1.Cells.Item($intRow, 7) = $objItem.TotalPhysicalMemory / 1024 / 1024
		}
	foreach ($objItem in $GenItems2){
		$Sheet1.Cells.Item($intRow, 8) = $objItem.Caption
		$Sheet1.Cells.Item($intRow, 9) = $objItem.csdversion
		}
			
#Populate Systems Sheet
	foreach ($objItem in $SysItems1){
		$Sheet2.Cells.Item($intRow, 1) = $StrComputer
		$Sheet2.Cells.Item($intRow, 2) = $objItem.Name
		$Sheet2.Cells.Item($intRow, 3) = $objItem.SMBIOSbiosVersion
		$Sheet2.Cells.Item($intRow, 4) = $objItem.SerialNumber
		}
	foreach ($objItem in $SysItems2){	
		$Sheet2.Cells.Item($intRow, 5) = $objItem.Caption
		}
	foreach ($objItem in $SysItems3){
		$Sheet2.Cells.Item($intRow, 6) = $objItem.BuildVersion
		}
				
#Populate Processor Sheet		
	foreach ($objItem in $ProcItems1){
		$Sheet3.Cells.Item($intRowCPU, 1) = $StrComputer
		$Sheet3.Cells.Item($intRowCPU, 2) = $objItem.DeviceID+" "+$objItem.Name
		$Sheet3.Cells.Item($intRowCPU, 3) = $objItem.Description
		$Sheet3.Cells.Item($intRowCPU, 4) = $objItem.family
		$Sheet3.Cells.Item($intRowCPU, 5) = $objItem.currentClockSpeed
		$Sheet3.Cells.Item($intRowCPU, 6) = $objItem.l2cacheSize
		$Sheet3.Cells.Item($intRowCPU, 7) = $objItem.UpgradeMethod
		$Sheet3.Cells.Item($intRowCPU, 8) = $objItem.SocketDesignation
		$intRowCPU = $intRowCPU + 1
		}
				
#Populate Memory Sheet
$bankcounter = 1
	foreach ($objItem in $memItems2){
		$MemSlots = $objItem.MemoryDevices +1
			
	foreach ($objItem in $MemItems1){
		$Sheet4.Cells.Item($intRowMem, 1) = $StrComputer
		$Sheet4.Cells.Item($intRowMem, 2) = "Bank " +$bankcounter
	if($objItem.BankLabel -eq ""){
		$Sheet4.Cells.Item($intRowMem, 3) = $objItem.DeviceLocator}
	Else{$Sheet4.Cells.Item($intRowMem, 3) = $objItem.BankLabel}
		$Sheet4.Cells.Item($intRowMem, 4) = $objItem.Capacity/1024/1024
		$Sheet4.Cells.Item($intRowMem, 5) = $objItem.FormFactor
		$Sheet4.Cells.Item($intRowMem, 6) = $objItem.TypeDetail
		$intRowMem = $intRowMem + 1
		$bankcounter = $bankcounter + 1
		}
	while($bankcounter -lt $MemSlots)	
		{
		$Sheet4.Cells.Item($intRowMem, 1) = $StrComputer
		$Sheet4.Cells.Item($intRowMem, 2) = "Bank " +$bankcounter
		$Sheet4.Cells.Item($intRowMem, 3) = "is Empty"
		$Sheet4.Cells.Item($intRowMem, 4) = ""
		$Sheet4.Cells.Item($intRowMem, 5) = ""
		$Sheet4.Cells.Item($intRowMem, 6) = ""
		$intRowMem = $intRowMem + 1
		$bankcounter = $bankcounter + 1
		}
	}
			
			
#Populate Disk Sheet
	foreach ($objItem in $DiskItems){
		$Sheet5.Cells.Item($intRowDisk, 1) = $StrComputer
		Switch($objItem.DriveType)
		{
		2{$Sheet5.Cells.Item($intRowDisk, 2) = "Floppy"}
		3{$Sheet5.Cells.Item($intRowDisk, 2) = "Fixed Disk"}
		5{$Sheet5.Cells.Item($intRowDisk, 2) = "Removable Media"}
		default{"Undetermined"}
		}
		$Sheet5.Cells.Item($intRowDisk, 3) = $objItem.DeviceID
		$Sheet5.Cells.Item($intRowDisk, 4) = $objItem.Size/1024/1024
		$Sheet5.Cells.Item($intRowDisk, 5) = $objItem.FreeSpace/1024/1024
		$intRowDisk = $intRowDisk + 1
		}
		
#Populate Network Sheet
	foreach ($objItem in $NetItems){
		$Sheet6.Cells.Item($intRowNet, 1) = $StrComputer
		$Sheet6.Cells.Item($intRowNet, 2) = $objItem.Caption+" (enabled)"
		$Sheet6.Cells.Item($intRowNet, 3) = $objItem.DHCPEnabled
		$Sheet6.Cells.Item($intRowNet, 4) = $objItem.IPAddress
		$Sheet6.Cells.Item($intRowNet, 5) = $objItem.IPSubnet
		$Sheet6.Cells.Item($intRowNet, 6) = $objItem.DefaultIPGateway
		$Sheet6.Cells.Item($intRowNet, 7) = $objItem.DNSServerSearchOrder
		$Sheet6.Cells.Item($intRowNet, 8) = $objItem.FullDNSRegistrationEnabled
		$Sheet6.Cells.Item($intRowNet, 9) = $objItem.WINSPrimaryServer
		$Sheet6.Cells.Item($intRowNet, 10) = $objItem.WINSSecondaryServer
		$Sheet6.Cells.Item($intRowNet, 11) = $objItem.WINSEnableLMHostsLookup
		$intRowNet = $intRowNet + 1
		}
		
$intRow = $intRow + 1
$intRowCPU = $intRowCPU + 1
$intRowMem = $intRowMem + 1
$intRowDisk = $intRowDisk + 1
$intRowNet = $intRowNet + 1
}
}

# ==============================================================================================
# Function Name 'WMILookupCred'-Uses Alternative Credential-Gathers info using WMI.
# ==============================================================================================
Function WMILookupCred {
foreach ($StrComputer in $colComputers){
		$GenItems1 = gwmi Win32_ComputerSystem -Comp $StrComputer -Credential $cred
		$GenItems2 = gwmi Win32_OperatingSystem -Comp $StrComputer -Credential $cred
		$SysItems1 = gwmi Win32_BIOS -Comp $StrComputer -Credential $cred
		$SysItems2 = gwmi Win32_TimeZone -Comp $StrComputer -Credential $cred
		$SysItems3 = gwmi Win32_WmiSetting -Comp $StrComputer -Credential $cred
		$ProcItems1 = gwmi Win32_Processor -Comp $StrComputer -Credential $cred
		$MemItems1 = gwmi Win32_PhysicalMemory -Comp $StrComputer -Credential $cred
		$memItems2 = gwmi Win32_PhysicalMemoryArray -Comp $StrComputer -Credential $cred
		$DiskItems = gwmi Win32_LogicalDisk -Comp $StrComputer -Credential $cred
		$NetItems = gwmi Win32_NetworkAdapterConfiguration -Comp $StrComputer -Credential $cred |`
					where{$_.IPEnabled -eq "True"}
		
				
# Populate General Sheet(1) with information
	foreach ($objItem in $GenItems1){
		$Sheet1.Cells.Item($intRow, 1) = $StrComputer
		Switch($objItem.DomainRole)
			{
			0{$Sheet1.Cells.Item($intRow, 2) = "Stand Alone Workstation"}
			1{$Sheet1.Cells.Item($intRow, 2) = "Member Workstation"}
			2{$Sheet1.Cells.Item($intRow, 2) = "Stand Alone Server"}
			3{$Sheet1.Cells.Item($intRow, 2) = "Member Server"}
			4{$Sheet1.Cells.Item($intRow, 2) = "Back-up Domain Controller"}
			5{$Sheet1.Cells.Item($intRow, 2) = "Primary Domain Controller"}
			default{"Undetermined"}
			}
		$Sheet1.Cells.Item($intRow, 3) = $objItem.Manufacturer
		$Sheet1.Cells.Item($intRow, 4) = $objItem.Model
		$Sheet1.Cells.Item($intRow, 5) = $objItem.SystemType
		$Sheet1.Cells.Item($intRow, 6) = $objItem.NumberOfProcessors
		$Sheet1.Cells.Item($intRow, 7) = $objItem.TotalPhysicalMemory / 1024 / 1024
		}
	foreach ($objItem in $GenItems2){
		$Sheet1.Cells.Item($intRow, 8) = $objItem.Caption
		$Sheet1.Cells.Item($intRow, 9) = $objItem.csdversion
		}
			
#Populate Systems Sheet
	foreach ($objItem in $SysItems1){
		$Sheet2.Cells.Item($intRow, 1) = $StrComputer
		$Sheet2.Cells.Item($intRow, 2) = $objItem.Name
		$Sheet2.Cells.Item($intRow, 3) = $objItem.SMBIOSbiosVersion
		$Sheet2.Cells.Item($intRow, 4) = $objItem.SerialNumber
		}
	foreach ($objItem in $SysItems2){	
		$Sheet2.Cells.Item($intRow, 5) = $objItem.Caption
		}
	foreach ($objItem in $SysItems3){
		$Sheet2.Cells.Item($intRow, 6) = $objItem.BuildVersion
		}
				
#Populate Processor Sheet		
	foreach ($objItem in $ProcItems1){
		$Sheet3.Cells.Item($intRowCPU, 1) = $StrComputer
		$Sheet3.Cells.Item($intRowCPU, 2) = $objItem.DeviceID+" "+$objItem.Name
		$Sheet3.Cells.Item($intRowCPU, 3) = $objItem.Description
		$Sheet3.Cells.Item($intRowCPU, 4) = $objItem.family
		$Sheet3.Cells.Item($intRowCPU, 5) = $objItem.currentClockSpeed
		$Sheet3.Cells.Item($intRowCPU, 6) = $objItem.l2cacheSize
		$Sheet3.Cells.Item($intRowCPU, 7) = $objItem.UpgradeMethod
		$Sheet3.Cells.Item($intRowCPU, 8) = $objItem.SocketDesignation
		$intRowCPU = $intRowCPU + 1
		}
				
#Populate Memory Sheet
$bankcounter = 1
	foreach ($objItem in $memItems2){
		$MemSlots = $objItem.MemoryDevices +1
			
	foreach ($objItem in $MemItems1){
		$Sheet4.Cells.Item($intRowMem, 1) = $StrComputer
		$Sheet4.Cells.Item($intRowMem, 2) = "Bank " +$bankcounter
	if($objItem.BankLabel -eq ""){
		$Sheet4.Cells.Item($intRowMem, 3) = $objItem.DeviceLocator}
	Else{$Sheet4.Cells.Item($intRowMem, 3) = $objItem.BankLabel}
		$Sheet4.Cells.Item($intRowMem, 4) = $objItem.Capacity/1024/1024
		$Sheet4.Cells.Item($intRowMem, 5) = $objItem.FormFactor
		$Sheet4.Cells.Item($intRowMem, 6) = $objItem.TypeDetail
		$intRowMem = $intRowMem + 1
		$bankcounter = $bankcounter + 1
		}
	while($bankcounter -lt $MemSlots)	
		{
		$Sheet4.Cells.Item($intRowMem, 1) = $StrComputer
		$Sheet4.Cells.Item($intRowMem, 2) = "Bank " +$bankcounter
		$Sheet4.Cells.Item($intRowMem, 3) = "is Empty"
		$Sheet4.Cells.Item($intRowMem, 4) = ""
		$Sheet4.Cells.Item($intRowMem, 5) = ""
		$Sheet4.Cells.Item($intRowMem, 6) = ""
		$intRowMem = $intRowMem + 1
		$bankcounter = $bankcounter + 1
		}
	}
			
			
#Populate Disk Sheet
	foreach ($objItem in $DiskItems){
		$Sheet5.Cells.Item($intRowDisk, 1) = $StrComputer
		Switch($objItem.DriveType)
		{
		2{$Sheet5.Cells.Item($intRowDisk, 2) = "Floppy"}
		3{$Sheet5.Cells.Item($intRowDisk, 2) = "Fixed Disk"}
		5{$Sheet5.Cells.Item($intRowDisk, 2) = "Removable Media"}
		default{"Undetermined"}
		}
		$Sheet5.Cells.Item($intRowDisk, 3) = $objItem.DeviceID
		$Sheet5.Cells.Item($intRowDisk, 4) = $objItem.Size/1024/1024
		$Sheet5.Cells.Item($intRowDisk, 5) = $objItem.FreeSpace/1024/1024
		$intRowDisk = $intRowDisk + 1
		}
		
#Populate Network Sheet
	foreach ($objItem in $NetItems){
		$Sheet6.Cells.Item($intRowNet, 1) = $StrComputer
		$Sheet6.Cells.Item($intRowNet, 2) = $objItem.Caption+" (enabled)"
		$Sheet6.Cells.Item($intRowNet, 3) = $objItem.DHCPEnabled
		$Sheet6.Cells.Item($intRowNet, 4) = $objItem.IPAddress
		$Sheet6.Cells.Item($intRowNet, 5) = $objItem.IPSubnet
		$Sheet6.Cells.Item($intRowNet, 6) = $objItem.DefaultIPGateway
		$Sheet6.Cells.Item($intRowNet, 7) = $objItem.DNSServerSearchOrder
		$Sheet6.Cells.Item($intRowNet, 8) = $objItem.FullDNSRegistrationEnabled
		$Sheet6.Cells.Item($intRowNet, 9) = $objItem.WINSPrimaryServer
		$Sheet6.Cells.Item($intRowNet, 10) = $objItem.WINSSecondaryServer
		$Sheet6.Cells.Item($intRowNet, 11) = $objItem.WINSEnableLMHostsLookup
		$intRowNet = $intRowNet + 1
		}
		
$intRow = $intRow + 1
$intRowCPU = $intRowCPU + 1
$intRowMem = $intRowMem + 1
$intRowDisk = $intRowDisk + 1
$intRowNet = $intRowNet + 1
}
}

# =============================================================================================
# Function Name 'ListComputers' - Enumerates ALL computer objects in AD
# ==============================================================================================
Function ListComputers {
$strCategory = "computer"

$objDomain = New-Object System.DirectoryServices.DirectoryEntry

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(objectCategory=$strCategory)")

$colProplist = "name"
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

$colResults = $objSearcher.FindAll()

foreach ($objResult in $colResults)
    {$objComputer = $objResult.Properties; $objComputer.name}
}

# ==============================================================================================
# Function Name 'ListServers' - Enumerates ALL Servers objects in AD
# ==============================================================================================
Function ListServers {
$strCategory = "computer"
$strOS = "Windows*Server*"

$objDomain = New-Object System.DirectoryServices.DirectoryEntry

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(&(objectCategory=$strCategory)(OperatingSystem=$strOS))")

$colProplist = "name"
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

$colResults = $objSearcher.FindAll()

foreach ($objResult in $colResults)
    {$objComputer = $objResult.Properties; $objComputer.name}
}

# ========================================================================
# Function Name 'ListTextFile' - Enumerates Computer Names in a text file
# Create a text file and enter the names of each computer. One computer
# name per line. Supply the path to the text file when prompted.
# ========================================================================
Function ListTextFile {
	$strText = Read-Host "Enter the path for the text file"
	$colComputers = Get-Content $strText
}

# ========================================================================
# Function Name 'SingleEntry' - Enumerates Computer from user input
# ========================================================================
Function ManualEntry {
	$colComputers = Read-Host "Enter Computer Name or IP" 
}

# ==============================================================================================
# Script Body
# ==============================================================================================
$erroractionpreference = "SilentlyContinue"


#Gather info from user.
Write-Host "********************************" 	-ForegroundColor Green
Write-Host "Computer Inventory Script" 			-ForegroundColor Green
Write-Host "By: Jesse Hamrick" 					-ForegroundColor Green
Write-Host "Created: 04/15/2009" 				-ForegroundColor Green
Write-Host "Contact: www.PowerShellPro.com" 	-ForegroundColor Green
Write-Host "********************************" 	-ForegroundColor Green
Write-Host " "
Write-Host "Admin rights are required to enumerate information." 	-ForegroundColor Green
Write-Host "Would you like to use an alternative credential?"		-ForegroundColor Green
$credResponse = Read-Host "[Y] Yes, [N] No"
	If($CredResponse -eq "y"){$cred = Get-Credential DOMAIN\USER}
Write-Host " "
Write-Host "Which computer resources would you like in the report?"	-ForegroundColor Green
$strResponse = Read-Host "[1] All Domain Computers, [2] All Domain Servers, [3] Computer names from a File, [4] Choose a Computer manually"
If($strResponse -eq "1"){$colComputers = ListComputers | Sort-Object}
	elseif($strResponse -eq "2"){$colComputers = ListServers | Sort-Object}
	elseif($strResponse -eq "3"){. ListTextFile}
	elseif($strResponse -eq "4"){. ManualEntry}
	else{Write-Host "You did not supply a correct response, `
	Please run script again." -foregroundColor Red}				
Write-Progress -Activity "Getting Inventory" -status "Running..." -id 1

#New Excel Application
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True

# Create 6 worksheets
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Add()
$Sheet = $Excel.Worksheets.Add()
$Sheet = $Excel.Worksheets.Add()

# Assign each worksheet to a variable and
# name the worksheet.
$Sheet1 = $Excel.Worksheets.Item(1)
$Sheet2 = $Excel.WorkSheets.Item(2)
$Sheet3 = $Excel.WorkSheets.Item(3)
$Sheet4 = $Excel.WorkSheets.Item(4)
$Sheet5 = $Excel.WorkSheets.Item(5)
$Sheet6 = $Excel.WorkSheets.Item(6)
$Sheet1.Name = "General"
$Sheet2.Name = "System"
$Sheet3.Name = "Processor"
$Sheet4.Name = "Memory"
$Sheet5.Name = "Disk"
$Sheet6.Name = "Network"

#Create Heading for General Sheet
$Sheet1.Cells.Item(1,1) = "Device_Name"
$Sheet1.Cells.Item(1,2) = "Role"
$Sheet1.Cells.Item(1,3) = "HW_Make"
$Sheet1.Cells.Item(1,4) = "HW_Model"
$Sheet1.Cells.Item(1,5) = "HW_Type"
$Sheet1.Cells.Item(1,6) = "CPU_Count"
$Sheet1.Cells.Item(1,7) = "Memory_MB"
$Sheet1.Cells.Item(1,8) = "Operating_System"
$Sheet1.Cells.Item(1,9) = "SP_Level"

#Create Heading for System Sheet
$Sheet2.Cells.Item(1,1) = "Device_Name"
$Sheet2.Cells.Item(1,2) = "BIOS_Name"
$Sheet2.Cells.Item(1,3) = "BIOS_Version"
$Sheet2.Cells.Item(1,4) = "HW_Serial_#"
$Sheet2.Cells.Item(1,5) = "Time_Zone"
$Sheet2.Cells.Item(1,6) = "WMI_Version"

#Create Heading for Processor Sheet
$Sheet3.Cells.Item(1,1) = "Device_Name"
$Sheet3.Cells.Item(1,2) = "Processor(s)"
$Sheet3.Cells.Item(1,3) = "Type"
$Sheet3.Cells.Item(1,4) = "Family"
$Sheet3.Cells.Item(1,5) = "Speed_MHz"
$Sheet3.Cells.Item(1,6) = "Cache_Size_MB"
$Sheet3.Cells.Item(1,7) = "Interface"
$Sheet3.Cells.Item(1,8) = "#_of_Sockets"

#Create Heading for Memory Sheet
$Sheet4.Cells.Item(1,1) = "Device_Name"
$Sheet4.Cells.Item(1,2) = "Bank_#"
$Sheet4.Cells.Item(1,3) = "Label"
$Sheet4.Cells.Item(1,4) = "Capacity_MB"
$Sheet4.Cells.Item(1,5) = "Form"
$Sheet4.Cells.Item(1,6) = "Type"

#Create Heading for Disk Sheet
$Sheet5.Cells.Item(1,1) = "Device_Name"
$Sheet5.Cells.Item(1,2) = "Disk_Type"
$Sheet5.Cells.Item(1,3) = "Drive_Letter"
$Sheet5.Cells.Item(1,4) = "Capacity_MB"
$Sheet5.Cells.Item(1,5) = "Free_Space_MB"

#Create Heading for Network Sheet
$Sheet6.Cells.Item(1,1) = "Device_Name"
$Sheet6.Cells.Item(1,2) = "Network_Card"
$Sheet6.Cells.Item(1,3) = "DHCP_Enabled"
$Sheet6.Cells.Item(1,4) = "IP_Address"
$Sheet6.Cells.Item(1,5) = "Subnet_Mask"
$Sheet6.Cells.Item(1,6) = "Default_Gateway"
$Sheet6.Cells.Item(1,7) = "DNS_Servers"
$Sheet6.Cells.Item(1,8) = "DNS_Reg"
$Sheet6.Cells.Item(1,9) = "Primary_WINS"
$Sheet6.Cells.Item(1,10) = "Secondary_WINS"
$Sheet6.Cells.Item(1,11) = "WINS_Lookup"

$colSheets = ($Sheet1, $Sheet2, $Sheet3, $Sheet4, $Sheet5, $Sheet6)
foreach ($colorItem in $colSheets){
$intRow = 2
$intRowCPU = 2
$intRowMem = 2
$intRowDisk = 2
$intRowNet = 2
$WorkBook = $colorItem.UsedRange
$WorkBook.Interior.ColorIndex = 20
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True
}

If($credResponse -eq "y"){WMILookupCred}
Else{WMILookup}

#Auto Fit all sheets in the Workbook
foreach ($colorItem in $colSheets){
$WorkBook = $colorItem.UsedRange															
$WorkBook.EntireColumn.AutoFit()
clear
}
Write-Host "*******************************" -ForegroundColor Green
Write-Host "The Report has been completed."  -ForeGroundColor Green
Write-Host "*******************************" -ForegroundColor Green
# ========================================================================
# END of Script
# ========================================================================

Also, here is a script I use that creates a User to Group spreadsheet for a user-selectable OU.

$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) 
 
$wb.Sheets.Add() 
$ws = $wb.Worksheets.Item(1) 
$ws.Name = "AD Groups" 
 
$groups = Get-QADGroup -searchroot "YOUR OU HERE" -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 -searchroot "YOUR OU HERE" -SizeLimit 0 
$intRow = 2 
$uslook = @{} 
foreach ($user in $users) { 
 
$uslook.Add($user.sid, $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.sid) 
$ws.Cells.item($usrow,$grcol) = "x" 
 
 
} 
}

richard-siddaway · October 31, 2013, 4:07am

Be interested to see how that runs because writing into Excel like that is very slow

notarat · October 31, 2013, 5:29am

Yeah it’s not the fastest script in the world but as long as you aren’t running it against an NMCI domain its performance is generally acceptable…

Topic		Replies	Views
Complete Newbie - Looking for Help Creating a Script to Provide List of AD User PowerShell Help	3	222	October 26, 2020
Help with get-aduser filter PowerShell Help	14	202	July 5, 2015
ADUser Script help required PowerShell Help	7	160	July 4, 2018
Powershell Basics PowerShell Help	13	1140	June 24, 2021
List users in specific group, plus active/disabled PowerShell Help	0	169	December 31, 2011

trying to make basic user inventory / info script

Related Topics