Script "AD Report"

hello guys,

please need help. have script works ok but

is sluggish. in $cc have different department our company , want create report of users in these departments

but 1 report takes 5-10 min though 2-3 users. there possibility how fast script?

 

many help 

 

$cc = get-content 'c:\temp\cc.txt'

$cc | foreach-object {

$excel = new-object -comobject excel.application

$excel.visible=$false  

$sesit= $excel.workbooks.add()

$list = $sesit.worksheets.item(1)

$workbook = $list.rows

$workbook.interior.colorindex =40

$workbook.font.colorindex = 11

$workbook.font.bold = $true

$department_query = "*$_*"

$query = get-qaduser -department $department_query

$row1 = 1

$query | foreach-object {

 if ($_.accountisdisabled -like "false") {

#define items of users

$list.cells.item($row1,1).font.bold = $true

$list.cells.item($row1,1).font.colorindex = 23

$list.cells.item($row1,1)= "name:" 

$list.cells.item($row1,2)= $_.name

 

$row1++

$list.cells.item($row1,1).font.bold = $true

$list.cells.item($row1,1).font.colorindex = 23

$list.cells.item($row1,1)= "firstname:"

$list.cells.item($row1,2) = $_.firstname

$row1++

$list.cells.item($row1,1).font.bold = $true

$list.cells.item($row1,1).font.colorindex = 23

$list.cells.item($row1,1)= "lastname:"

$list.cells.item($row1,2) = $_.lastname

$row1++

$list.cells.item($row1,1).font.bold = $true

$list.cells.item($row1,1).font.colorindex = 23

$list.cells.item($row1,1)= "email:"

$list.cells.item($row1,2) = $_.email

$row1++

$list.cells.item($row1,1).font.bold = $true

$list.cells.item($row1,1).font.colorindex = 23

$list.cells.item($row1,1)= "lastlogon:"

$list.cells.item($row1,2) = $_.lastlogon

$row1++

$list.cells.item($row1,1).font.bold = $true

$list.cells.item($row1,1).font.colorindex = 23

$list.cells.item($row1,1)= "department:"

$list.cells.item($row1,2) = $_.department

$row1++

$manager = $_.manager | get-qaduser

$list.cells.item($row1,1).font.bold = $true

$list.cells.item($row1,1).font.colorindex = 23

$list.cells.item($row1,1)= "manager:"

$list.cells.item($row1,2) = $manager.name

$list.cells.item($row1,3).font.bold = $true

$list.cells.item($row1,3).font.colorindex = 23

$list.cells.item($row1,3)= "manager email:"

$list.cells.item($row1,4)= $manager.email

$row1++

#define groups

#fileshare group

$list.cells.item($row1,1)="fileshare groups:"

 $row1++

$group = get-qadmemberof -identity $_.name -name "g fs*","g ind*" | select-object name,description,notes | sort-object -property name -unique

foreach ($grp in $group) {

$list.cells.item($row1,1) = $grp.name

$list.cells.item($row1,4) = $grp.description

$list.cells.item($row1,5) = $grp.notes 

$row1++

}

 

################################################

# application group

$row1++

$grou = get-qadmemberof -identity $_.name -name "apps*" | select-object name,description,notes | sort-object -property name -unique

$list.cells.item($row1,1).font.bold = $true

$list.cells.item($row1,1).font.colorindex = 23

$list.cells.item($row1,1)= "applications: "

$row1++

foreach ($gr in $grou) {

  $list.cells.item($row1,1) = $gr.name

$list.cells.item($row1,4) = $gr.description

$list.cells.item($row1,5) = $gr.notes 

$row1++

}

################################################

# distribution  group

$list.cells.item($row1,1)="distribution list:"

$row1+=2

$group = get-qadmemberof -identity $_.name -name "*dl*" | select-object name,description,notes | sort-object -property name -unique

foreach ($grp in $group) {

$list.cells.item($row1,1) = $grp.name

$list.cells.item($row1,4) = $grp.description

$list.cells.item($row1,5) = $grp.notes 

$row1++

}

################################################

# vpn groups

$list.cells.item($row1,1)= "vpn groups:"

$row1+=2

$group = get-qadmemberof -identity $_.name -name "g ssl*" | select-object name,description,notes | sort-object -property name -unique

foreach ($grp in $group) {

$list.cells.item($row1,1) = $grp.name

$list.cells.item($row1,4) = $grp.description

$list.cells.item($row1,5) = $grp.notes 

$row1++

}

################################################

#all group

$list.cells.item($row1,1)= "other groups:"

$row1+=2

$group = get-qadmemberof -identity $_.name -name "*" | select-object name,description,notes | sort-object -property name -unique

foreach ($grp in $group) {

$list.cells.item($row1,1) = $grp.name

$list.cells.item($row1,4) = $grp.description

$list.cells.item($row1,5) = $grp.notes 

}

$row1++

}

}

$workbook.entirecolumn.autofit()

 

#save file , close excel.

$filename = "c:\temp\report\$_" + ".xls"

$sesit.saveas($filename)

$excel.quit() 

write-host "completed!"

}

---

liibas

hi,

i'd ot strip out export excel stuff first , see how executes. tend export of reports csv , pull them excel i'm not sure on speed of com object souldn't slow.

if haven't check out ad powershell blog ther's some pretty cool stuff there. http://blogs.msdn.com/b/adpowershell/ 

j

 

Windows Server  >  Windows PowerShell