Active Directory · PowerShell

Powershell & Active Directory : Active Directory Reports in Excel.

Aman Dhally

Hi,

Active Directory reporting, some time we thinks what should a Active Directory reports contains, should i need a tool to do that reporting job? or should i purchase a active Directory tool to do this task.

One of my friend want an Active Directory Reporting tool and he was planning to buy it, i asked what are your requirements, he told me that, I want a tool which can export a data in to the Excel, and i have a list of disabled Computer accounts, User Account so that i can delete them,

I told this that that can be done easily with powershell and for him i wrote a little powershell script. this script is based on RSAT , Active Directory module. Make sure you have RSAT tools installed before running the script.

when you run the script , that will Import Active Directory Module First and then open an Excel workbook. In excel you can find 4 sheets for now. they are contains

  1. List of inactive Users Accounts
  2. List of inactive Computer Accounts
  3. Users Accounts created within a period of week.
  4. List of Users with password never expires enabled.

Script Download link : http://gallery.technet.microsoft.com/scriptcenter/Active-Directory-Reports-bb8c1cc7

21-08-2012 13-19-01

and this excel file will be saved on users Desktop.

Script Download link : http://gallery.technet.microsoft.com/scriptcenter/Active-Directory-Reports-bb8c1cc7

Thanks

Aman Dhally

join aman on facebook Join aman on Linkedin follow aman on Twitter

6 thoughts on “Powershell & Active Directory : Active Directory Reports in Excel.”

  1. Dear Aman

    When I want to put a script into a job in a task scheduling, it’s not generate excel file.
    please your help.

    Regarless
    JM

    1. Hi Juan,

      I hope you are doing fine. I never tested this script in task scheduler.,
      can you check, if excel is installed on the server where this script is scheduled.

      can you login to the server and manually run the script and see if that is working.

      thanks
      aman

  2. Dear Aman,

    I hope you are doing.
    I saw your application its really good.
    Can you add the fields with the fallowing which makes even better like active users and domain land few other deials.

  3. Getting a ton of errors when running this script:
    You cannot call a method on a null-valued expression.
    At C:\Users\a_user\Desktop\AD_Reports.ps1:113 char:18
    + $s5.range <<<< ("C1:C1").cells="List of Inactive User Acccounts"
    + CategoryInfo : InvalidOperation: (range:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\Users\a_user\Desktop\AD_Reports.ps1:114 char:18
    + $s5.range <<<< ("A3:A3").cells="Name"
    + CategoryInfo : InvalidOperation: (range:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\Users\a_user\Desktop\AD_Reports.ps1:115 char:18
    + $s5.range <<<< ("A3:A3").font.bold = "true"
    + CategoryInfo : InvalidOperation: (range:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\Users\a_user\Desktop\AD_Reports.ps1:121 char:20
    + $cells.item <<<< ($Row5,$col5)=$Us.Name
    + CategoryInfo : InvalidOperation: (item:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\Users\a_user\Desktop\AD_Reports.ps1:128 char:35
    + $s6 = $workbook.Sheets.add <<<< ()
    + CategoryInfo : InvalidOperation: (add:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Property 'name' cannot be found on this object; make sure it exists and is settable.
    At C:\Users\a_user\Desktop\AD_Reports.ps1:129 char:13
    + $s6. <<<< name = "Report Information"
    + CategoryInfo : InvalidOperation: (name:String) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

    You cannot call a method on a null-valued expression.
    At C:\Users\a_user\Desktop\AD_Reports.ps1:132 char:18
    + $s6.range <<<< ("D7:D7").cells="Title:"
    + CategoryInfo : InvalidOperation: (range:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\Users\a_user\Desktop\AD_Reports.ps1:133 char:18
    + $s6.range <<<< ("D7:D7").font.bold = "true"
    + CategoryInfo : InvalidOperation: (range:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\Users\a_user\Desktop\AD_Reports.ps1:145 char:17
    + $workbook.SaveAs <<<< ("$env:userprofile\desktop\ActiveDirectoryReport.xlsx")
    + CategoryInfo : InvalidOperation: (SaveAs:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    This is when running against a small AD, ~300 users. Would the script be able to handle a larger AD of say 10,000 users? It seems to hold everything in memory until the end when it outputs to the Excel spreadsheet, maybe this is a necessary step when using PS to create an excel spreadsheet?

  4. Hi Aman

    I want to generate a excel report for all the delegation rights provided to users and group in a domain.

Comments are closed.