Good afternoon, dear readers and subscribers, we continue to study the capabilities of Powershell and Active Directory. How do you remember everything about her? Accounts users and computers are in the NTDS.dit database, everything is cool and centralized. When a company has more than one system administrator, a situation may arise where garbage and unnecessary credentials accumulate. We are all human and we can forget some things, and at some moments we can be distracted, which will also lead to forgetting important information. And we come to the conclusion that inactive users (fired or forgotten) accumulate in the Actvie Directory, in any case, good System Administrator must identify them, disable them and then delete them if desired, which is what we will do.

Via ADUC snap-in

Last time I already gave you an example of using the Active Directory Users and Computers snap-in, through which we looked for missing computers on the local network that had not appeared for a month. Now we will do the same with user accounts. I have AD on Windows Server 2012 R2, open ADUC, to do this press WIN+R and enter dsa.msc.

In the request form that opens, enter:

• Request name > for me these are lost users
• Description if necessary
• Request root > here you can leave the entire domain, or specify it on the desired OU

Then click the request button.

On the users tab we see the item “Number of days since last login”; for example, I set it to 60 days.

As a result, you will receive the list you need of inactive employee accounts.

Via powershell snap-in

The same thing can be done through Powershell. I’ll immediately give you the code whose task is search is underway inactive users, for this I chose a period of 45 days, disabling user data and moving to a specially designated OU.

$date_with_offset= (Get-Date).AddDays(-45)
$users = Get-ADUser -Properties LastLogonDate -Filter (LastLogonDate -lt $date_with_offset ) | Sort LastLogonDate
foreach ($user in $users) (set-aduser $user -enabled $false; move-adobject -identity $user -targetpath "ou=Fired,ou=Moscow L. users,ou=Location,dc=msk,dc= contoso,dc=com")
Get-ADUser -Properties LastLogonDate -Filter (LastLogonDate -lt $date_with_offset ) | Sort LastLogonDate | FT Name, LastLogonDate -AutoSize | Out-File c:\Script\users.txt

• In the first line you declare a variable in which you set the search term
• Create a variable and make a selection based on the last login time
• Moving users

• Making a report to a file

More useful things about working with the user. Before using below commands, you need to load the Active Directory module via the command

Get-Help Get-ADUser

Scripts for unloading all users from MS Active Directory (ITGC)

One of the standard audit procedures ITGC for the catalog Active Directory is to get a download of all domain users. Based on the data obtained, testing procedures are then formed, for example, studying the list of administrators or identifying users with an expired password. The most effective way to create such an upload would be to use a standard interface PowerShell , examples of which we will consider in this article

Below is PowerShell script, as one of the simplest and quick ways get a list of all AD domain users in CSV format, which can be opened without any problems in Excel.

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher $objSearcher.SearchRoot = "LDAP://ou=Users,ou=Departmets,dc=test,dc=ru" $objSearcher.Filter = "(&(objectCategory=person) (!userAccountControl:1.2.840.113556.1.4.803:=2))" $users = $objSearcher.FindAll() # Number of accounts $users.Count $users | ForEach-Object ( $user = $_.Properties New-Object PsObject -Property @( Position = $user.description Department = $user.department Login = $user.userprincipalname Phone = $user.telephonenumber Room = $user.physicaldeliveryofficename Full name = $user.cn ) ) | Export-Csv -NoClobber -Encoding utf8 -Path C: list_domain_users.csv

In order for the script to work on your system, you need to slightly correct it, namely enter the necessary parameters, i.e. how in in this example these are the parameters Users in the department Departments in the domain Test.ru. And also indicate the path to where the file is saved list_domain_users.csv

After unloading, if you open it immediately list_domain_users.csv , will look unreadable, however, using standard means we can easily bring it into the format we need. Open in Excel list_domain_users.csv , select the first column, then go to the “Data” tab and click “Text by Columns”. Select "delimited" and click "Next". Ready!

!It is necessary to note that this script will not display more than 1000 users. It’s quite suitable for a small company, but for those with a domain great amount users should resort to the methods described below.

2. Advanced PowerShell cmdlet for getting Active Directory user uploads

The Active Directory Module for Windows PowerShell tool (introduced in Windows Server 2008 R2 and higher) allows you to create cmdlets that perform various manipulations with AD directory objects. The cmdlet is used to obtain information about users and their properties Get-ADUser.

To start launch a Powershell window with administrator rights and import the Active Directory module for further action:
Import-Module activedirectory

To list all domain accounts and, let's run the command:

Get-ADUser -filter *

To withdraw full of information about all available attributes user tuser, run the command

Get-ADUser -identity tuser -properties *

For example, we are interested in information about date of password change and time when it expires . The result of the command can be exported to a text file:

Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | ft Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires > C:tempusers.txt

Or right away upload to CSV , which in the future will be convenient to export to Excel (in addition, using sort-object we will sort the table by the PasswordLastSet column, and also add a where condition - the user name must contain the string “Dmitry”)

Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | where ($_.name –like “*Dmitry*”) | sort-object PasswordLastSet | select-object Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires | Export-csv -path c:tempuser-password-expires-2015.csv

In the comments to the previous article, we remembered about accounting in Excel instead of 1C. Well, let's check how much you know Excel. Today I will show you how to get data from Active Directory and work with it without macros and PowerShell - only with standard Office mechanisms. For example, you can easily get analytics on the use of operating systems in your organization if you don't already have something like Microsoft SCOM. Well, or just warm up and take your mind off the scripts.

Of course, you can get the data as in the examples below literally with one line in PowerShell. But, firstly, PowerShell is too boring, and secondly, Excel can dynamically update data - the resulting documents can be published online and forgotten about updating them.

To work with data, I will use the Power Query mechanism. For Office 2010 and 2013 you will have to install the plugin, in Microsoft Office 2016 this module is already built-in. Unfortunately, the standard edition is not enough for us; we will need Professional.

The mechanism itself is designed to receive and process data from a variety of sources - from the old ODBC and text files, to Exchange, Oracle and Facebook. More details about the mechanism and built-in script language“M” has already been written on Habré, but I’ll look at a couple of examples of use Power Query to retrieve data from Active Directory.

Warm-up: Let's see when our users logged in

The request to the domain database itself is created on the “Data - New request― From other sources ― From Active Directory.”

Specify the data source.

You will need to select a domain name and provide the necessary connection information. Next, select the type of objects, in this example - user. On the right in the preview window, the query is already running, showing a preview of the data.

We prepare a request and admire the preview.

You should prepare the request in advance by clicking the “edit” button and selecting the required columns. Essentially, these columns are classes. Each of them contains a set of specific attributes of an Active Directory object, except for the main column displayName, which itself is an attribute. I'll focus on classes user, person, top And securityPrincipal. Now you need to select required attributes from each class using the “extension” - an icon with two arrows at the column header:

• Class user expand by choosing lastLogonTimestamp And userAccountControl;
• V person let's choose telephoneNumber;
• V top ― whenCreated;
• and in securityPrincipal ― SamAccountName.

We expand the request.

Now let's set up the filter: in particular, in order not to get blocked accounts, the userAccountControl attribute must have a value of 512 or 66048. The filter may be different in your environment. You can read more about the attribute in the Microsoft documentation.

Applying a filter.

Sometimes Excel incorrectly detects the data format, especially the value of the lastLogonTimestamp attribute. If such a misfortune suddenly befalls you, you can set the correct format on the “Convert” tab.

Now the userAccountControl column should be deleted - it is not needed at all in the display. And click “Download and close”.

The result is a plate that just needs a little finishing touches. For example, rename the columns to something more readable. And customize automatic update data.

Automatic updating when opening a table or by timeout is configured in the “Data” tab in “Properties”.

Setting up data update.

After setting up the update is completed, you can safely give the table to the personnel department or security service - let them know who logged into the system and when.

The request code in the “M” language is under the spoiler.

let Source = ActiveDirectory.Domains("domain.ru"), domain.ru = Source()[#"Object Categories"], user1 = domain.ru(), #"Remote Columns" = Table.RemoveColumns(user1,( "organizationalPerson", "shadowAccount", "posixAccount", "msExchOmaUser", "msExchBaseClass", "msExchIMRecipient", "msExchCertificateInformation", "msExchMultiMediaUser", "msExchMailStorage", "msExchCustomAttributes", "mailRecipient", "distinguishedName")), #"Expanded element securityPrincipal" = Table.ExpandRecordColumn(#"Removed columns", "securityPrincipal", ("sAMAccountName"), ("sAMAccountName")), #"Expanded element top" = Table.ExpandRecordColumn(#"Expanded element securityPrincipal ", "top", ("whenCreated"), ("whenCreated")), #"Expanded element person" = Table.ExpandRecordColumn(#"Expanded element top", "person", ("telephoneNumber"), ("telephoneNumber ")), #"Expanded element user" = Table.ExpandRecordColumn(#"Expanded element person", "user", ("lastLogonTimestamp", "userAccountControl"), ("lastLogonTimestamp", "userAccountControl")), #"Rows with filter applied" = Table.SelectRows(#"Expanded user element", each ( = 512 or = 66048)), #"Changed type" = Table.TransformColumnTypes(#"Rows with filter applied",(("lastLogonTimestamp", type datetime))), #"Remoted columns1" = Table.RemoveColumns(#"Changed type",("userAccountControl")) in #"Remoted columns1"

Creating an address book, or what to do when corporate portal not friendly with AD

Another variant using Excel in conjunction with Active Directory - this is the formation of an address book based on AD data. It is clear that The address book It will be relevant only if the domain is in order.

Let's create a request for an object user, expand the class user V mail, and class person V telephoneNumber. Let's delete all columns except distinguishedName― the domain structure repeats the structure of the enterprise, so the names Organizational Units correspond to the names of the departments. Similarly, security groups can be used as the basis for department names.

Now from the line CN=Username, OU=Accounting Department, OU=Divisions, DC=domain, DC=ru you need to extract the department name directly. The easiest way to do this is to use the delimiters on the Transform tab.

Extracting the text.

As delimiters I use OU= And ,OU=. In principle, a comma is enough, but I'm playing it safe.

Enter delimiters.

Now using the filter you can cut off unnecessary OU, like blocked users and builtin, configure sorting and load data into the table.

View of the summary table.

Quick report on the composition of workstations, without introducing agents or other preparation

Now let's try to create a useful table by obtaining data on computers. We will make a report on the ones used by the company operating systems: to do this, we’ll create a request, but this time we’ll select computer.

We make a request for the computer object.

Let's leave the column classes computer And top and expand them:

• Class computer expand by choosing cn, operatingSystem, operatingSystemServicePack And operatingSystemVersion;
• in class top let's choose whenCreated.

Advanced request.

If desired, you can make a report only on server operating systems. For example, filter by operatingSystem or operatingSystemVersion attribute. I won’t do this, but I will correct the display of the creation time - I’m only interested in the year. To do this, on the “Conversion” tab, select the column we need and select “Year” in the “Date” menu.

We extract the year from the time the computer entered the domain.

Now all that remains is to delete the displayname column as unnecessary and load the result. The data is ready. Now you can work with them as with a regular table. First, let's create a pivot table on the "Insert" - "Pivot Table" tab. Let's agree to the choice of data source and configure its fields.

Pivot table field settings.

Now all that remains is to customize the design to your taste and admire the result:

Summary table for computers in AD.

If desired, you can add summary schedule, also on the Insert tab. In the “Category” (or in the “Rows”, to taste) add operatingSystem, to data ― cn. On the “Design” tab, you can choose the type of chart you like; I preferred the pie chart.

Pie chart.

Now it is clearly visible that, despite the ongoing update, the total number of workstations with Windows XP and servers with Windows 2003 is quite large. And there is something to strive for.

The request code is under the spoiler.

let Source = ActiveDirectory.Domains("domain.ru"), domain.ru = Source()[#"Object Categories"], computer1 = domain.ru(), #"Remote Columns" = Table.RemoveColumns(computer1,( "user", "organizationalPerson", "person")), #"Other removed columns" = Table.SelectColumns(#"Remoted columns",("displayName", "computer", "top")), #"Expand item computer" = Table.ExpandRecordColumn(#"Other remote columns", "computer", ("cn", "operatingSystem", "operatingSystemServicePack", "operatingSystemVersion"), ("cn", "operatingSystem", "operatingSystemServicePack", " operatingSystemVersion")), #"Extended top element" = Table.ExpandRecordColumn(#"Expanded computer element", "top", ("whenCreated"), ("whenCreated")), #"Extracted year" = Table.TransformColumns( #"Expanded element top",(("whenCreated", Date.Year))), #"Remoted columns1" = Table.RemoveColumns(#"Extracted year",("displayName")) in #"Remoted columns1"

0

I have the following working script that checks if a large list of users in a CSV file is a member of an AD group and writes the results to results.csv.

Not sure how to convert the script so I can change $group = "InfraLite" to $group = DC .\List_Of_AD_Groups.CSV .

So the script doesn't just return matches for one AD group, but so it returns matches for the 80 AD groups contained in List_of_AD_groups.csv. Writing YES/NO for each AD group in a new CSV column (or if this is not possible, creating a separate CSV file for each group with the results will do the same.

I could do this manually by changing the value from $group and the export file name and re-running the script 80 times, but would have to be quick with PS to do this

for example results.csv?:

NAME AD_GROUP1 AD_GROUP2 AD_GROUP80 etc etc. user1 yes no yes user2 no no yes user3 no yes no echo "UserName`InfraLite" >> results.csv $users = GC .\user_list.csv $group = "InfraLite" $members = Get-ADGroupMember -Identity $group -Recursive | Select -ExpandProperty SAMAccountName foreach ($user in $users) ( if ($members -contains $user) ( echo "$user $group`tYes" >> results.csv ) else ( echo "$user`tNo" >> results .csv) )

• 2 answers

Sorting:

Activity

0

a trivial solution to your problem would be to wrap your existing code in another loop and create an output file for each group:

$groups = Get-Content "C:\groups.txt" foreach ($group in $groups) ( $members = Get-ADGroupMember ... ... )

A more elegant approach would be to create a group mapping template, clone it for each user, and populate a copy with the user's group memberships. Something like this should work:

$template = @() Get-Content "C:\groups.txt" | ForEach-Object ( $template[$_] = $false ) $groups = @() Get-ADGroup -Filter * | ForEach-Object ( $groups[$_.DistinguishedName] = $_.Name ) Get-ADUser -Filter * -Properties MemberOf | ForEach-Object ( $groupmap = $template.Clone() $_.MemberOf | ForEach-Object ( $groups[$_] ) | Where-Object ( $groupmap.ContainsKey($_) ) | ForEach-Object ( $groupmap [$_] = $true ) New-Object -Type PSObject -Property $groupmap ) | Export-Csv "C:\user_group_mapping.csv" -NoType

0

I've been playing with this for a while and I think I found a way to get you exactly what you were after.

I think Ansgar was on the right track, but I couldn't get it to do what came after. He mentioned that at the time of writing he did not have access to the AD environment.

Here's what I came up with:

$UserArray = Get-Content "C:\Temp\Users.txt" $GroupArray = Get-Content "C:\Temp\Groups.txt" $OutputFile = "C:\Temp\Something.csv" # Setting up a hashtable for later use $UserHash = New-Object -TypeName System.Collections.Hashtable # Outer loop to add users and membership to UserHash $UserArray | ForEach-Object( $UserInfo = Get-ADUser $_ -Properties MemberOf # Strips the LPAP syntax to just the SAMAccountName of the group $Memberships = $UserInfo.MemberOf | ForEach-Object( ($_.Split(",")) .replace("CN=","") ) #Adding the User=Membership pair to the Hash $UserHash.Add($_,$Memberships) ) #Outer loop to create an object per user $Results = $UserArray | ForEach-Object( # First create a simple object $User = New-Object -TypeName PSCustomObject -Property @( Name = $_ ) # Dynamically add members to the object, based on the $GroupArray $GroupArray | ForEach-Object ( #Checking $UserHash to see if group shows up in user"s membership list $UserIsMember = $UserHash.($User.Name) -contains $_ #Adding property to object, and value $User | Add-Member -MemberType NoteProperty -Name $ _ -Value $UserIsMember ) #Returning the object to the variable Return $User ) #Convert the objects to a CSV, then output them $Results | ConvertTo-CSV -NoTypeInformation | Out-File $OutputFile

Let's hope everything makes sense. I commented as much as I could. It would be very easy to convert to ADSI if you didn't have RSAT installed on whatever machine you're running this on. If you need it, let me know and I'll make some quick changes.