I have a list of several users in an excel spreadsheet. Is it possible to
retrieve the users in the excel spreadsheet and parse active directory to
ensure that those users are disabled?

Is it a CSV file or plain Excel?

You can use this to test the user

function Test-ADUser{
param($user,$dom)
$root = [ADSI]"LDAP://$dom"
$filter = "(&(objectcategory=user)(sAMAccountName=$user))"
$user = (new-Object
System.DirectoryServices.DirectorySearcher($filter)).findone()
$user = $user.GetDirectoryEntry()
$user.psbase.invokeget('accountdisabled')
}

Returns $true if disabled $false if not

"Rey Perez" <Rey Perez@xxxxxx> wrote in message
news:E68997A1-872D-44FB-9DD7-DF4C56907503@xxxxxx

>I have a list of several users in an excel spreadsheet. Is it possible to
> retrieve the users in the excel spreadsheet and parse active directory to
> ensure that those users are disabled?

It's a plain excel.

Thanks for the help!


"Brandon Shell" wrote:

> Is it a CSV file or plain Excel?
>
> You can use this to test the user
>
> function Test-ADUser{
> param($user,$dom)
> $root = [ADSI]"LDAP://$dom"
> $filter = "(&(objectcategory=user)(sAMAccountName=$user))"
> $user = (new-Object
> System.DirectoryServices.DirectorySearcher($filter)).findone()
> $user = $user.GetDirectoryEntry()
> $user.psbase.invokeget('accountdisabled')
> }
>
> Returns $true if disabled $false if not
>
> "Rey Perez" <Rey Perez@xxxxxx> wrote in message
> news:E68997A1-872D-44FB-9DD7-DF4C56907503@xxxxxx

> >I have a list of several users in an excel spreadsheet. Is it possible to
> > retrieve the users in the excel spreadsheet and parse active directory to
> > ensure that those users are disabled?

>
>

colum and row one would expect the User name to be in?

"Rey Perez" <ReyPerez@xxxxxx> wrote in message
news:401E7742-57A2-4F40-88A8-407DA840B21D@xxxxxx

> It's a plain excel.
>
> Thanks for the help!
>
>
> "Brandon Shell" wrote:
>

>> Is it a CSV file or plain Excel?
>>
>> You can use this to test the user
>>
>> function Test-ADUser{
>> param($user,$dom)
>> $root = [ADSI]"LDAP://$dom"
>> $filter = "(&(objectcategory=user)(sAMAccountName=$user))"
>> $user = (new-Object
>> System.DirectoryServices.DirectorySearcher($filter)).findone()
>> $user = $user.GetDirectoryEntry()
>> $user.psbase.invokeget('accountdisabled')
>> }
>>
>> Returns $true if disabled $false if not
>>
>> "Rey Perez" <Rey Perez@xxxxxx> wrote in message
>> news:E68997A1-872D-44FB-9DD7-DF4C56907503@xxxxxx

>> >I have a list of several users in an excel spreadsheet. Is it possible
>> >to
>> > retrieve the users in the excel spreadsheet and parse active directory
>> > to
>> > ensure that those users are disabled?

>>
>>

Column A Row 1.


"Brandon Shell" wrote:

> colum and row one would expect the User name to be in?
>
> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
> news:401E7742-57A2-4F40-88A8-407DA840B21D@xxxxxx

> > It's a plain excel.
> >
> > Thanks for the help!
> >
> >
> > "Brandon Shell" wrote:
> >

> >> Is it a CSV file or plain Excel?
> >>
> >> You can use this to test the user
> >>
> >> function Test-ADUser{
> >> param($user,$dom)
> >> $root = [ADSI]"LDAP://$dom"
> >> $filter = "(&(objectcategory=user)(sAMAccountName=$user))"
> >> $user = (new-Object
> >> System.DirectoryServices.DirectorySearcher($filter)).findone()
> >> $user = $user.GetDirectoryEntry()
> >> $user.psbase.invokeget('accountdisabled')
> >> }
> >>
> >> Returns $true if disabled $false if not
> >>
> >> "Rey Perez" <Rey Perez@xxxxxx> wrote in message
> >> news:E68997A1-872D-44FB-9DD7-DF4C56907503@xxxxxx
> >> >I have a list of several users in an excel spreadsheet. Is it possible
> >> >to
> >> > retrieve the users in the excel spreadsheet and parse active directory
> >> > to
> >> > ensure that those users are disabled?
> >>
> >>

>
>

It would be much simpler if you could save as a CSV...

I assume you just have one sheet in the work book.

"Rey Perez" <ReyPerez@xxxxxx> wrote in message
news:6100B29D-E903-473B-A30C-474E79B3E174@xxxxxx

> Column A Row 1.
>
>
> "Brandon Shell" wrote:
>

>> colum and row one would expect the User name to be in?
>>
>> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
>> news:401E7742-57A2-4F40-88A8-407DA840B21D@xxxxxx

>> > It's a plain excel.
>> >
>> > Thanks for the help!
>> >
>> >
>> > "Brandon Shell" wrote:
>> >
>> >> Is it a CSV file or plain Excel?
>> >>
>> >> You can use this to test the user
>> >>
>> >> function Test-ADUser{
>> >> param($user,$dom)
>> >> $root = [ADSI]"LDAP://$dom"
>> >> $filter = "(&(objectcategory=user)(sAMAccountName=$user))"
>> >> $user = (new-Object
>> >> System.DirectoryServices.DirectorySearcher($filter)).findone()
>> >> $user = $user.GetDirectoryEntry()
>> >> $user.psbase.invokeget('accountdisabled')
>> >> }
>> >>
>> >> Returns $true if disabled $false if not
>> >>
>> >> "Rey Perez" <Rey Perez@xxxxxx> wrote in message
>> >> news:E68997A1-872D-44FB-9DD7-DF4C56907503@xxxxxx
>> >> >I have a list of several users in an excel spreadsheet. Is it
>> >> >possible
>> >> >to
>> >> > retrieve the users in the excel spreadsheet and parse active
>> >> > directory
>> >> > to
>> >> > ensure that those users are disabled?
>> >>
>> >>

>>
>>

I'm all about simple. I just changed it to CSV.

"Brandon Shell" wrote:

> It would be much simpler if you could save as a CSV...
>
> I assume you just have one sheet in the work book.
>
> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
> news:6100B29D-E903-473B-A30C-474E79B3E174@xxxxxx

> > Column A Row 1.
> >
> >
> > "Brandon Shell" wrote:
> >

> >> colum and row one would expect the User name to be in?
> >>
> >> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
> >> news:401E7742-57A2-4F40-88A8-407DA840B21D@xxxxxx
> >> > It's a plain excel.
> >> >
> >> > Thanks for the help!
> >> >
> >> >
> >> > "Brandon Shell" wrote:
> >> >
> >> >> Is it a CSV file or plain Excel?
> >> >>
> >> >> You can use this to test the user
> >> >>
> >> >> function Test-ADUser{
> >> >> param($user,$dom)
> >> >> $root = [ADSI]"LDAP://$dom"
> >> >> $filter = "(&(objectcategory=user)(sAMAccountName=$user))"
> >> >> $user = (new-Object
> >> >> System.DirectoryServices.DirectorySearcher($filter)).findone()
> >> >> $user = $user.GetDirectoryEntry()
> >> >> $user.psbase.invokeget('accountdisabled')
> >> >> }
> >> >>
> >> >> Returns $true if disabled $false if not
> >> >>
> >> >> "Rey Perez" <Rey Perez@xxxxxx> wrote in message
> >> >> news:E68997A1-872D-44FB-9DD7-DF4C56907503@xxxxxx
> >> >> >I have a list of several users in an excel spreadsheet. Is it
> >> >> >possible
> >> >> >to
> >> >> > retrieve the users in the excel spreadsheet and parse active
> >> >> > directory
> >> >> > to
> >> >> > ensure that those users are disabled?
> >> >>
> >> >>
> >>
> >>

>
>

Try this... If you can add a Header Row called LogonName

function Test-ADUser{
param($user,$dom)
$root = [ADSI]"LDAP://$dom"
$filter = "(&(objectcategory=user)(sAMAccountName=$user))"
$user = (new-Object
System.DirectoryServices.DirectorySearcher($filter)).findone()
$user = $user.GetDirectoryEntry()
$user.psbase.invokeget('accountdisabled')
}

$users = import-Csv c:\mycsvfile.csv
foreach($user in $users)
{
$result = Test-ADUser $user.LogonName
if($results){"{0} is Disabled" -f $user.LogonName}
else{"{0} is NOT Disabled" -f $user.LogonName}
}

"Rey Perez" <ReyPerez@xxxxxx> wrote in message
news:B61106CB-F5FF-48F2-A033-1DE34B572D5F@xxxxxx

> I'm all about simple. I just changed it to CSV.
>
> "Brandon Shell" wrote:
>

>> It would be much simpler if you could save as a CSV...
>>
>> I assume you just have one sheet in the work book.
>>
>> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
>> news:6100B29D-E903-473B-A30C-474E79B3E174@xxxxxx

>> > Column A Row 1.
>> >
>> >
>> > "Brandon Shell" wrote:
>> >
>> >> colum and row one would expect the User name to be in?
>> >>
>> >> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
>> >> news:401E7742-57A2-4F40-88A8-407DA840B21D@xxxxxx
>> >> > It's a plain excel.
>> >> >
>> >> > Thanks for the help!
>> >> >
>> >> >
>> >> > "Brandon Shell" wrote:
>> >> >
>> >> >> Is it a CSV file or plain Excel?
>> >> >>
>> >> >> You can use this to test the user
>> >> >>
>> >> >> function Test-ADUser{
>> >> >> param($user,$dom)
>> >> >> $root = [ADSI]"LDAP://$dom"
>> >> >> $filter = "(&(objectcategory=user)(sAMAccountName=$user))"
>> >> >> $user = (new-Object
>> >> >> System.DirectoryServices.DirectorySearcher($filter)).findone()
>> >> >> $user = $user.GetDirectoryEntry()
>> >> >> $user.psbase.invokeget('accountdisabled')
>> >> >> }
>> >> >>
>> >> >> Returns $true if disabled $false if not
>> >> >>
>> >> >> "Rey Perez" <Rey Perez@xxxxxx> wrote in message
>> >> >> news:E68997A1-872D-44FB-9DD7-DF4C56907503@xxxxxx
>> >> >> >I have a list of several users in an excel spreadsheet. Is it
>> >> >> >possible
>> >> >> >to
>> >> >> > retrieve the users in the excel spreadsheet and parse active
>> >> >> > directory
>> >> >> > to
>> >> >> > ensure that those users are disabled?
>> >> >>
>> >> >>
>> >>
>> >>

>>
>>

Hi Rey,

This might be simpler if you use the Quest AD cmdlets (which are free).

To get the users identified in the csv file and then specifically look them
up by LogonName (assuming that it has a header called LogonName and contains
logon names), you could use this one-liner:

Import-Csv C:\MyCsvFile.csv | Add-Member -Name VerifiedDisabled -MemberType
ScriptProperty -Value {$user = Get-QADUser -LogonName $this.LogonName;
($user -ne $null) -and ($user.UserAccountControl -band 2) } -PassThru

This will output a table of LogonName and VerifiedDisabled values. If you
only want those that are not disabled, just append | Where-Object {
$_.VerifiedDisabled -eq $false } to the end of the one-liner

Brandon's script below will also work, but I wanted to present another
option. Either one will do the trick.

If you want to get the Quest AD cmdlets, you can download them anonymously
here:
http://www.quest.com/activeroles-server/arms.aspx

-
Kirk Munro
Poshoholic
http://poshoholic.com

"Brandon Shell" <tshell.mask@xxxxxx> wrote in message
news:O1S%231UTBIHA.5652@xxxxxx

> Try this... If you can add a Header Row called LogonName
>
> function Test-ADUser{
> param($user,$dom)
> $root = [ADSI]"LDAP://$dom"
> $filter = "(&(objectcategory=user)(sAMAccountName=$user))"
> $user = (new-Object
> System.DirectoryServices.DirectorySearcher($filter)).findone()
> $user = $user.GetDirectoryEntry()
> $user.psbase.invokeget('accountdisabled')
> }
>
> $users = import-Csv c:\mycsvfile.csv
> foreach($user in $users)
> {
> $result = Test-ADUser $user.LogonName
> if($results){"{0} is Disabled" -f $user.LogonName}
> else{"{0} is NOT Disabled" -f $user.LogonName}
> }
>
> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
> news:B61106CB-F5FF-48F2-A033-1DE34B572D5F@xxxxxx

>> I'm all about simple. I just changed it to CSV.
>>
>> "Brandon Shell" wrote:
>>

>>> It would be much simpler if you could save as a CSV...
>>>
>>> I assume you just have one sheet in the work book.
>>>
>>> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
>>> news:6100B29D-E903-473B-A30C-474E79B3E174@xxxxxx
>>> > Column A Row 1.
>>> >
>>> >
>>> > "Brandon Shell" wrote:
>>> >
>>> >> colum and row one would expect the User name to be in?
>>> >>
>>> >> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
>>> >> news:401E7742-57A2-4F40-88A8-407DA840B21D@xxxxxx
>>> >> > It's a plain excel.
>>> >> >
>>> >> > Thanks for the help!
>>> >> >
>>> >> >
>>> >> > "Brandon Shell" wrote:
>>> >> >
>>> >> >> Is it a CSV file or plain Excel?
>>> >> >>
>>> >> >> You can use this to test the user
>>> >> >>
>>> >> >> function Test-ADUser{
>>> >> >> param($user,$dom)
>>> >> >> $root = [ADSI]"LDAP://$dom"
>>> >> >> $filter = "(&(objectcategory=user)(sAMAccountName=$user))"
>>> >> >> $user = (new-Object
>>> >> >> System.DirectoryServices.DirectorySearcher($filter)).findone()
>>> >> >> $user = $user.GetDirectoryEntry()
>>> >> >> $user.psbase.invokeget('accountdisabled')
>>> >> >> }
>>> >> >>
>>> >> >> Returns $true if disabled $false if not
>>> >> >>
>>> >> >> "Rey Perez" <Rey Perez@xxxxxx> wrote in message
>>> >> >> news:E68997A1-872D-44FB-9DD7-DF4C56907503@xxxxxx
>>> >> >> >I have a list of several users in an excel spreadsheet. Is it
>>> >> >> >possible
>>> >> >> >to
>>> >> >> > retrieve the users in the excel spreadsheet and parse active
>>> >> >> > directory
>>> >> >> > to
>>> >> >> > ensure that those users are disabled?
>>> >> >>
>>> >> >>
>>> >>
>>> >>
>>>
>>>

>

Hi Brandon,

I've been testing this script and it says all my accounts are NOT disabled
when some of them actually are.

Thanks,
Rey


"Brandon Shell" wrote:

> Try this... If you can add a Header Row called LogonName
>
> function Test-ADUser{
> param($user,$dom)
> $root = [ADSI]"LDAP://$dom"
> $filter = "(&(objectcategory=user)(sAMAccountName=$user))"
> $user = (new-Object
> System.DirectoryServices.DirectorySearcher($filter)).findone()
> $user = $user.GetDirectoryEntry()
> $user.psbase.invokeget('accountdisabled')
> }
>
> $users = import-Csv c:\mycsvfile.csv
> foreach($user in $users)
> {
> $result = Test-ADUser $user.LogonName
> if($results){"{0} is Disabled" -f $user.LogonName}
> else{"{0} is NOT Disabled" -f $user.LogonName}
> }
>
> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
> news:B61106CB-F5FF-48F2-A033-1DE34B572D5F@xxxxxx

> > I'm all about simple. I just changed it to CSV.
> >
> > "Brandon Shell" wrote:
> >

> >> It would be much simpler if you could save as a CSV...
> >>
> >> I assume you just have one sheet in the work book.
> >>
> >> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
> >> news:6100B29D-E903-473B-A30C-474E79B3E174@xxxxxx
> >> > Column A Row 1.
> >> >
> >> >
> >> > "Brandon Shell" wrote:
> >> >
> >> >> colum and row one would expect the User name to be in?
> >> >>
> >> >> "Rey Perez" <ReyPerez@xxxxxx> wrote in message
> >> >> news:401E7742-57A2-4F40-88A8-407DA840B21D@xxxxxx
> >> >> > It's a plain excel.
> >> >> >
> >> >> > Thanks for the help!
> >> >> >
> >> >> >
> >> >> > "Brandon Shell" wrote:
> >> >> >
> >> >> >> Is it a CSV file or plain Excel?
> >> >> >>
> >> >> >> You can use this to test the user
> >> >> >>
> >> >> >> function Test-ADUser{
> >> >> >> param($user,$dom)
> >> >> >> $root = [ADSI]"LDAP://$dom"
> >> >> >> $filter = "(&(objectcategory=user)(sAMAccountName=$user))"
> >> >> >> $user = (new-Object
> >> >> >> System.DirectoryServices.DirectorySearcher($filter)).findone()
> >> >> >> $user = $user.GetDirectoryEntry()
> >> >> >> $user.psbase.invokeget('accountdisabled')
> >> >> >> }
> >> >> >>
> >> >> >> Returns $true if disabled $false if not
> >> >> >>
> >> >> >> "Rey Perez" <Rey Perez@xxxxxx> wrote in message
> >> >> >> news:E68997A1-872D-44FB-9DD7-DF4C56907503@xxxxxx
> >> >> >> >I have a list of several users in an excel spreadsheet. Is it
> >> >> >> >possible
> >> >> >> >to
> >> >> >> > retrieve the users in the excel spreadsheet and parse active
> >> >> >> > directory
> >> >> >> > to
> >> >> >> > ensure that those users are disabled?
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>

>
>