Author Posts

June 9, 2015 at 12:47 pm

First off I am a newbie at powershell but I am very resourceful and was able to compile the command below to get what I want for the most part it runs great Give me the output i want... Except certain users as myself are not listed because some of our accounts password are set to never expire. As you can see in the command below the useraccountcontrol is set to 512 which is an enabled account if I recall. I also tried 66048 which is 512+65536 where 65536 is = to accounts set to not expire. but then it only displays us with those attributes.

Is there a way in one command like below where it can search for all users with the 512 and the 65536 useraccount control all in one command? Any other recommendations? Thank you

get-aduser -ldapfilter "(&(&(objectCategory=person)(objectclass=user)(mail=*)(givenname=*)(sn=*)(useraccountcontrol=512)))" -Properties givenName, sn,distinguishedname,samaccountname,mail, enabled,employeeid | Select givenName, sn,distinguishedname,samaccountname,mail,division,employeeid | sort-object -property givenname | Export-Csv -Path c:\output1\ldapfilter00000000.csv -NoTypeInformation

June 9, 2015 at 1:26 pm

Wow.

UserAccountControl is a bit mask. Each bit in the byte stands for something different. 512 means that the 9th bit (it's technically two bytes) is turned on. It's tough to just specify numbers like you're doing. "512" means "the 9th bit, and only the 9th bit, turned on." If the 8th bits turned on – some other flag – then the value would be 768, and wouldn't fit your query criteria.

65536 is the 16th bit.

So what is it you want? Users where the 9th AND 16th are BOTH flipped? Or users where one or the other are flipped?

First, I don't think you need the mail, given name, and sn portions of your LdapQuery. You're specifying *, which isn't a filter at all. It seems like you just need to re-do your query to include either a Boolean AND or a Boolean OR, which I believe the LdapFilter syntax supports.

June 9, 2015 at 6:27 pm

Rather than all that LDAP filtering, couldn't you just do this?

 Search-ADAccount -UsersOnly -PasswordNeverExpires 

June 11, 2015 at 9:26 am

Hi I actually got the code i needed with some help.

New question so with the output I have now which is an excel file, I need to put the data in a sql table format, is there an easy way to just run something to look at the filename it outputted from the code and format it as a sql table based on columns is reported back?

Thanks

June 11, 2015 at 9:32 am

No, there's no built-in, easy way to read an Excel file and turn it into a SQL table. You would need to manually extract the data from Excel and create SQL statements, and then run those SQL statements against a SQL database located on a SQL Server. "SQL table" isn't a file format.

If you've got an Excel file, it might be easier just to use SSIS. Set up an integration package on SQL Server that just imports the Excel file. It'd be a ton easier than using PowerShell for that.

June 11, 2015 at 9:32 am

Also, if you could please post the code that solved your problem, it could be very helpful to someone else in the future.

June 11, 2015 at 9:42 am

First, Get-ADUser already has Boolean values for Enabled,PasswordExpired,PasswordLastSet, PasswordNeverExpires,PasswordNotRequired etc. to perform 'non-cryptic' AD queries:

Get-ADUser -Filter {Enabled -eq $true} -Properties * | Select *

Second, what do you mean by SQL data format? There are functions to convert to DataTable, but if you just want to simply format as table, then use Format-Table:

Get-ADUser -Filter {Enabled -eq $true} -Properties Enabled, Name, Description | Select Name, Enabled, Description | Format-Table -AutoSize