trying to make basic user inventory / info script

This topic contains 5 replies, has 3 voices, and was last updated by  notarat 4 years, 1 month ago.

  • Author
    Posts
  • #11075

    simon t
    Participant

    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.

  • #11076

    Richard Siddaway
    Moderator

    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

  • #11077

    simon t
    Participant

    You are the man!

  • #11131

    notarat
    Participant

    [quote=11077]You are the man![/quote]

    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" 
     
     
    } 
    }
    
  • #11133

    Richard Siddaway
    Moderator

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

  • #11138

    notarat
    Participant

    [quote=11133]Be interested to see how that runs because writing into Excel like that is very slow[/quote]

    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...

You must be logged in to reply to this topic.