Help with current command in powershell

This topic contains 6 replies, has 4 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 1 year, 9 months ago.

  • Author
    Posts
  • #26220
    Profile photo of Qais Fawad
    Qais Fawad
    Participant

    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

  • #26222
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

  • #26231
    Profile photo of Matt McNabb
    Matt McNabb
    Participant

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

     Search-ADAccount -UsersOnly -PasswordNeverExpires 
  • #26339
    Profile photo of Qais Fawad
    Qais Fawad
    Participant

    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

  • #26341
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

  • #26342
    Profile photo of Don Jones
    Don Jones
    Keymaster

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

  • #26343
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    

You must be logged in to reply to this topic.