AD Inactive User Report

hi all,

i working automate inactive user reporting. have created script create report based on different ou's , save xls files in folder ou name. send reports divisions.

the requirement generate report in organisational excel template. have saved template shared folder. report getting generated fine first ou mentioned in input file next ou's headings getting created in correct row/coloumn , colour data not getting populated. there doing wrong.

==================================================================================

add-pssnapin quest.activeroles.admanagement
set-qadpssnapinsettings -defaultsizelimit 0
$reportmonth=get-date -format "mmm-yy"
$comparedate=get-date
$numberdays=90
get-content "d:\inactiveusers\oupath.txt" |
foreach-object{
$oupath=$_
$excel = new-object -com excel.application
$excel.visible = $true
$wbook = $excel.workbooks.open("d:\report.xls")
$wsheet = $excel.worksheets.item(1)
$wsheet.cells.item(17,3) = "name:"
$wsheet.cells.item(17,4) = "userid:"
$wsheet.cells.item(17,5) = "lastlogontimestamp:"
$workbook = $wsheet.usedrange
$wsheet.range("c$(17):f$(17)").interior.colorindex = 9
$wsheet.range("c$(17):f$(17)").font.colorindex = 2
$workbook.font.bold = $true
$introw=18
$disusers=get-qaduser -includedproperties displayname,samaccountname,lastlogontimestamp -searchroot "ou=$oupath,dc=domain,dc=com" | { ($comparedate-$_.lastlogontimestamp).days -gt $numberdays } | select name,displayname,description,samaccountname,lastlogontimestamp
foreach ($disabled in $disusers)
{
$wsheet.cells.item($introw,3) = $disabled.displayname
$wsheet.cells.item($introw,4) = $disabled.samaccountname
$wsheet.cells.item($introw,5) = $disabled.lastlogontimestamp
$introw++
}
$workbook.entirecolumn.autofit()
$excel.displayalerts=$true
$check = test-path -pathtype container y:\$reportmonth
if($check -eq $false){new-item y:\$reportmonth -type directory}
$wsheet.saveas("y:\$reportmonth\$oupath-inactiveusers.xls")
$excel.quit()
remove-variable * -force -erroraction silentlycontinue
}

i haven't tested code, looks need move parts of outside foreach-object loop.  try this:

add-pssnapin quest.activeroles.admanagement set-qadpssnapinsettings -defaultsizelimit 0  $reportmonth=get-date -format "mmm-yy" $comparedate=get-date $numberdays=90  $excel = new-object -com excel.application $excel.visible = $true $wbook = $excel.workbooks.open("d:\report.xls") $wsheet = $excel.worksheets.item(1) $wsheet.cells.item(17,3) = "name:" $wsheet.cells.item(17,4) = "userid:" $wsheet.cells.item(17,5) = "lastlogontimestamp:" $workbook = $wsheet.usedrange $wsheet.range("c$(17):f$(17)").interior.colorindex = 9 $wsheet.range("c$(17):f$(17)").font.colorindex = 2 $workbook.font.bold = $true $introw=18   get-content "d:\inactiveusers\oupath.txt" | foreach-object{     $oupath=$_          $disusers=get-qaduser -includedproperties displayname,samaccountname,lastlogontimestamp -searchroot "ou=$oupath,dc=domain,dc=com" | { ($comparedate-$_.lastlogontimestamp).days -gt $numberdays } | select name,displayname,description,samaccountname,lastlogontimestamp     foreach ($disabled in $disusers)     {         $wsheet.cells.item($introw,3) = $disabled.displayname         $wsheet.cells.item($introw,4) = $disabled.samaccountname         $wsheet.cells.item($introw,5) = $disabled.lastlogontimestamp         $introw++     } }  $workbook.entirecolumn.autofit() $excel.displayalerts=$true $check = test-path -pathtype container y:\$reportmonth if($check -eq $false){new-item y:\$reportmonth -type directory}  $wsheet.saveas("y:\$reportmonth\$oupath-inactiveusers.xls") $excel.quit() remove-variable * -force -erroraction silentlycontinue 

Windows Server  >  Windows PowerShell