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 a 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

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

Dedicated to using PowerShell to administer AD. As a starting point, the author decided to take 10 common AD administration tasks and look at how they can be simplified using PowerShell:

1. Reset user password
2. Activate and deactivate accounts
3. Unlock user account
4. Delete your account
5. Find empty groups
6. Add users to a group
7. List group members
8. Find outdated computer accounts
9. Deactivate a computer account
10. Find computers by type

In addition, the author maintains a blog (using PowerShell, of course), we recommend taking a look - jdhitsolutions.com/blog. And you can get the most up-to-date information from his Twitter twitter.com/jeffhicks.
So, below is the translation of the article “Top 10 Active Directory Tasks Solved with PowerShell”.

Active Directory (AD) management with using Windows PowerShell is easier than you think, and I want to prove it to you. You can simply take the scripts below and use them to solve a number of AD management tasks.

Requirements

To use PowerShell to manage AD, you need to meet several requirements. I'm going to demonstrate how the AD cmdlets work using a Windows 7 computer as an example.
To use the cmdlets, you must have a Windows Server 2008 R2 domain controller, or you can download and install Active Directory Management Gateway Service on legacy DCs. Please read the documentation carefully before installation; CD reboot required.
On the client side, download and install (RSAT) for either Windows 7 or Windows 8. On Windows 7, you will need to open in Control Panels chapter Programs and choose Turn on or off Windows features(Turn Windows Features On or Off). Find Remote Server Administration Tools and expand the section Role Administration Tools. Select the appropriate items for AD DS and AD LDS Tools, especially note that the item must be selected Active Directory Module for Windows PowerShell, as shown in Figure 1. (In Windows 8, all tools are selected by default). Now we are ready to work.

Fig.1 Enabling AD DS and AD LDS Tools

I am logged in with an account with domain administrator rights. Most of the cmdlets I'll show will allow you to specify alternative credentials. In any case, I recommend reading the help ( Get-Help) and examples that I will demonstrate below.
Start a PowerShell session and import the module:

PS C:\> Import-Module ActiveDirectory

The import creates a new PSDrive, but we won't be using it. However, you can see what commands are available in the imported module.

PS C:\> get-command -module ActiveDirectory

The beauty of these commands is that if I can use a command on one AD object, then it can be used on 10, 100, and even 1000. Let's see how some of these cmdlets work.

Task 1: Reset the user password

Let's start with a typical task: resetting a user's password. You can do this easily and simply using a cmdlet Set-ADAccountPassword. The tricky part is that New Password must be qualified as a protected string: a piece of text that is encrypted and stored in memory for the duration of a PowerShell session. First, let's create a variable with the new password:
PS C:\> $new=Read-Host "Enter the new password" -AsSecureString

Then, enter a new password:

Now we can extract the account (using samAccountname– the best option) and set a new password. Here's an example for user Jack Frost:

PS C:\> Set-ADAccountPassword jfrost -NewPassword $new

Unfortunately, there is a bug with this cmdlet: -Passthru, -Whatif, And –Confirm does not work. If you prefer a shortcut, try this:

PS C:\> Set-ADAccountPassword jfrost -NewPassword (ConvertTo-SecureString -AsPlainText -String "P@ssw0rd1z3" -force)

As a result, I need Jack to change his password the next time he logs in, so I modify the account using Set-ADUser.

PS C:\> Set-ADUser jfrost -ChangePasswordAtLogon $True

The results of running the cmdlet are not written to the console. If this needs to be done, use –True. But I can find out whether the operation was successful or not by retrieving the username using the cmdlet Get-ADUser and specifying the property Password Expired, as shown in Figure 2.

Rice. 2. Results of the Get-ADUser Cmdlet with the PasswordExpired property

Bottom line: Resetting a user's password using PowerShell is not difficult at all. I admit that resetting the password is also easy through the snap Active Directory Users and Computers consoles Microsoft Management Console (MMC). But using PowerShell is appropriate if you need to delegate a task, don't want to deploy the above-mentioned snap-in, or are resetting a password as part of a large automated IT process.

Task 2: Activate and deactivate accounts

Now let's deactivate the account. Let's continue working with Jack Frost. This code uses the parameter –Whatif, which you can find in other comadlets that make changes to test my command without running it.

PS C:\> Disable-ADAccount jfrost -whatif What if: Performing operation "Set" on Target "CN=Jack Frost, OU=staff,OU=Testing,DC=GLOBOMANTICS,DC=local".

Now let’s deactivate it for real:

PS C:\> Disable-ADAAccount jfrost

And when the time comes to activate the account, which cmdlet will help us?

PS C:\> Enable-ADAccount jfrost

These cmdlets can be used in a pipelined expression, allowing you to activate or deactivate as many accounts as you like. For example, this code will deactivate all accounts in the Sales department

PS C:\> get-aduser -filter "department -eq "sales"" | disable-adaccount

Of course, write a filter for Get-ADUser quite complicated, but this is where the use of the parameter –Whatif along with the cmdlet Disable-ADAccount comes to the rescue.

Task 3: Unlock the user account

Consider a situation where Jack locked out his account while trying to enter a new password. Instead of trying to find his account through the GUI, the unlocking procedure can be done using a simple command.

PS C:\> Unlock-ADAAccount jfrost

The cmdlet also supports parameters -Whatif And -Confirm.

Task 4: Delete account

It doesn't matter how many users you remove - it's easy to do using the cmdlet Remove-ADUser. I don't want to remove Jack Frost, but if I wanted to, I would use code like this:

PS C:\> Remove-ADUser jfrost -whatif What if: Performing operation "Remove" on Target "CN=Jack Frost,OU=staff,OU=Testing,DC=GLOBOMANTICS,DC=local".

Or I can enter multiple users and delete them with one simple command:

PS C:\> get-aduser -filter "enabled -eq "false"" -property WhenChanged -SearchBase "OU=Employees, DC=Globomantics,DC=Local" | where ($_.WhenChanged -le (Get-Date).AddDays(-180)) | Remove-ADuser -whatif

This command will find and delete any disabled Employees OU accounts that have not been modified for 180 days or more.

Task 5: Finding empty groups

Managing groups is an endless and thankless task. There are many ways to find empty groups. Some expressions may work better than others, depending on your organization. The code below will find all groups in the domain, including built-in ones.

PS C:\> get-adgroup -filter * | where (-Not ($_ | get-adgroupmember)) | Select Name

If you have groups with hundreds of members, then using this command can take a long time; Get-ADGroupMember checks each group. If you can limit or customize it will be better.
Here's another approach:

PS C:\> get-adgroup -filter "members -notlike "*" -AND GroupScope -eq "Universal"" -SearchBase "OU=Groups,OU=Employees,DC=Globomantics, DC=local" | Select Name,Group*

This command finds all Universal groups that do not have membership in OU Groups and displays some of the properties. The result is shown in Figure 3.


Rice. 3. Search and filter universal groups

Task 6: Adding users to a group

Let's add Jack Frost to the Chicago IT group:

PS C:\> add-adgroupmember "chicago IT" -Members jfrost

Yes, it's that simple. You can also easily add hundreds of users to groups, although I find this a little awkward:

PS C:\> Add-ADGroupMember "Chicago Employees" -member (get-aduser -filter "city -eq "Chicago"")

I used the parenthetical pipelined expression to find all users who have the City property in Chicago. The code in parentheses is executed and the resulting objects are passed to the –Member parameter. Each user object is added to the Chicago Employees group. It doesn't matter whether we are dealing with 5 or 5000 users, updating group memberships takes just a few seconds. This expression can also be written using ForEach-Object what might be more convenient:

PS C:\> Get-ADUser -filter "city -eq "Chicago"" | foreach (Add-ADGroupMember "Chicago Employees" -Member $_)

Task 7: List group members

You might want to know who is in a certain group. For example, you should periodically find out who is a member of the Domain Admins group:

PS C:\> Get-ADGroupMember "Domain Admins"

Figure 4 shows the result.

Rice. 4. Members of the Domain Admins group

The cmdlet displays the AD object for each group member. What to do with nested groups? My group Chicago All Users is a collection of nested groups. To get a list of all accounts, I just have to use the parameter –Recursive.

PS C:\> Get-ADGroupMember "Chicago All Users" -Recursive | Select DistinguishedName

If you want to go the other way - find which groups a user is in - use the user property MemberOf:

PS C:\> get-aduser jfrost -property Memberof | Select -ExpandProperty memberOf CN=NewTest,OU=Groups,OU=Employees, DC=GLOBOMANTICS,DC=local CN=Chicago Test,OU=Groups,OU=Employees, DC=GLOBOMANTICS,DC=local CN=Chicago IT,OU= Groups,OU=Employees, DC=GLOBOMANTICS,DC=local CN=Chicago Sales Users,OU=Groups,OU=Employees, DC=GLOBOMANTICS,DC=local

I used the parameter -ExpandProperty to display names MemberOf like lines.

Task 8: Find outdated computer accounts

I get asked this question a lot: “How do I find outdated computer accounts?” And I always answer: “What is outdated for you?” Companies have different definitions of when a computer account (or user account, it doesn't matter) is considered obsolete and can no longer be used. As for me, I pay attention to those accounts whose passwords have not been changed for certain period time. This period for me is 90 days - if the computer has not changed the password along with the domain during this period, most likely it is offline and outdated. Cmdlet used Get-ADComputer:

PS C:\> get-adcomputer -filter "Passwordlastset -lt "1/1/2012"" -properties *| Select name,passwordlastset

The filter works great with a hard value, but this code will update for all computer accounts that have not changed their passwords since January 1, 2012. The results are shown in Figure 5.

Rice. 5. Find outdated computer accounts

Another option: suppose you are at least functional level Windows domain 2003. Filter by property LastLogontimeStamp. This value is the number of 100 nanosecond intervals since January 1, 1601, and is stored in GMT, so working with this value is a little tricky:

PS C:\> get-adcomputer -filter "LastlogonTimestamp -gt 0" -properties * | select name,lastlogontimestamp, @(Name="LastLogon";Expression=(::FromFileTime ($_.Lastlogontimestamp))),passwordlastset | Sort LastLogonTimeStamp

Rice. 6. Convert the LastLogonTimeStamp value to a familiar format

To create a filter, I need to convert the date, for example January 1, 2012, into the correct format. Conversion is carried out in FileTime:

PS C:\> $cutoff=(Get-Date "1/1/2012").ToFileTime() PS C:\> $cutoff 129698676000000000

Now I can use this variable in the filter to Get-ADComputer:

PS C:\> Get-ADComputer -Filter "(lastlogontimestamp -lt $cutoff) -or (lastlogontimestamp -notlike "*")" -property * | Select Name,LastlogonTimestamp,PasswordLastSet

The above code finds the same computers that were shown in Figure 5.

Task 9: Deactivate the computer account

Perhaps when you find inactive or outdated accounts, you will want to deactivate them. This is quite easy to do. We will use the same cmdlet that we used to work with user accounts. You can clarify it by using samAccountnameaccount.

PS C:\> Disable-ADAccount -Identity "chi-srv01$" -whatif What if: Performing operation "Set" on Target "CN=CHI-SRV01, CN=Computers,DC=GLOBOMANTICS,DC=local".

Or using a pipeline expression:

PS C:\> get-adcomputer "chi-srv01" | Disable-ADAccount

I can also use my code to find outdated accounts and deactivate them all:

PS C:\> get-adcomputer -filter "Passwordlastset -lt "1/1/2012"" -properties *| Disable-ADAccount

Task 10: Find computers by type

I also often get asked how to find computer accounts by type, such as servers or workstations. This requires some creativity on your part. There is nothing in AD that distinguishes a server from a client, except perhaps the OS. If your computer is running Windows Server 2008, you will have to do a few extra steps.
First, you need to get a list of operating systems, and then we filter accounts by available operating systems.

PS C:\> Get-ADComputer -Filter * -Properties OperatingSystem | Select OperatingSystem -unique | Sort OperatingSystem

The results are shown in Figure 7.

Rice. 7. Retrieving the OS list

I want to find all computers running a server OS:

PS C:\> Get-ADComputer -Filter "OperatingSystem -like "*Server*"" -properties OperatingSystem,OperatingSystem ServicePack | Select Name,Op* | format-list

The results are shown in Figure 8.

Like other AD Get cmdlets, you can customize search parameters and limit the request to specific OUs if necessary. All the expressions I've shown can be integrated into larger PowerShell expressions. For example, you can sort, group, apply filters, export to CSV, or create and email HTML reports - all from PowerShell! In this case, you won’t have to write a single script.
Here's a bonus: a user password-age report, saved in an HTML file:

PS C:\> Get-ADUser -Filter "Enabled -eq "True" -AND PasswordNeverExpires -eq "False"" -Properties PasswordLastSet,PasswordNeverExpires,PasswordExpired | Select DistinguishedName,Name,pass*,@(Name="PasswordAge"; Expression=((Get-Date)-$_.PasswordLastSet)) |sort PasswordAge -Descending | ConvertTo-Html -Title "Password Age Report" | Out-File c:\Work\pwage.htm !}

Although this expression may look a little intimidating, it is easy to use with minimal knowledge of PowerShell. And only one last piece of advice remains: how to define a custom property called PasswordAge. The value represents the gap between today and the PasswordLastSet property. Then I sort the results for my new property. Figure 9 shows the output for my small test domain.

Upd:
The post contains a translation of the article on the portal

Today we will try to download a list of all users into a separate file from Active Directory. Our main assistant in this matter will be PowerShell. The thing is that Microsoft initially planned the PowerShell command console as the main tool for managing server Windows components. And today, when we already have version 2.0, by and large, this is so.

Even in the recent past, in order to somehow interact with AD, administrators needed to have at their disposal either the dsquery utility or various kinds of scripts or utilities. Starting today Windows versions Server 2008 R2, we can work with AD via PowerShell. With the advent of PowerShell 2.0, a special module is used to interact with Active Directory Active Directory Module for Windows PowerShell, which contains required list cmdlets. For our tasks we will use the command Get-ADUser.

So, depending on which operating system we will be running the PowerShell console under, we will need to perform “preparatory steps”.

1) If we are working under Windows Server up to version 2012, then we need to run the command:

• Import-Module activedirectory – command to import a module into AD

For operating system versions from 2012 and higher, this module is already enabled by default.

2) If we work from any client Windows, then the package must be installed on it remote administration RSAT, with the Active Directory Module for Windows PowerShell component installed.

It is worth noting that the Get-ADUser cmdlet is recommended to be executed when the amount of data being uploaded is up to 1000 users.

Exporting AD users using PowerShell to a separate file

First, let's call the help for the Get-ADUser command. As a result, you will receive all the necessary commands for further administration.

• help Get-ADUser – command to call help

To get a list of all users with all properties in a PowerShell window, you need to run the following command:

• Get-ADUser -filter * – export a list of AD users

This download is not entirely informative and does not fit everything in the window. necessary information. Therefore, let's try to narrow the search and display the properties specific user with the name user1:

• Get-ADUser -identity user1 -properties * – export properties of a specific user

Now let’s try to export the list of all users with their properties to an external txt or csv file:

• Get-ADUser -filter * -properties * | Export-csv -path c:\users.csv -encoding Unicode – export users to a separate file

I would like to pay special attention to the key -encoding Unicode. It serves to ensure that the Russian Cyrillic alphabet, after export from AD, can be displayed correctly in the uploaded file. For example, through Microsoft Excel we will see question marks instead of Russian letters.

When viewing a file, the data is exported in one line and is therefore not readable. To change this we need to do the following:

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 a plugin; Microsoft Office 2016 already has this module 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 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” tab.

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 set up automatic data update.

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 will be up-to-date 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"

In this article, we'll look at PowerShell's ability to manage Active Directory domain groups. We'll look at how to create new group in AD, add users to it (or delete), display a list of group users and several other useful actions with domain groups that are extremely useful in day-to-day administration. To manage AD groups, the Active Directory PowerShell module provides the following basic cmdlets:

To use these cmdlets in your PowerShell session, a special AD interaction module must be loaded - Active Directory Module for Windows PowerShell. This module was first introduced in Windows Server 208 R2. In Windows Server 2012 and higher, this module is enabled by default. On client computers, it can be installed and enabled as one of the RSAT components. You can check if the module is loaded like this:

Get-Module -Listavailable

As you can see, the ActiveDirectory module is loaded. If not, import it with the command:

Import-Module activedirectory

The full list of module commands can be obtained as follows:

Get-Command -Module ActiveDirectory

There are a total of 147 cmdlets available in the module, of which 11 can work with groups.

Get-Command -Module ActiveDirectory -Name "*Group*"

Here is their list:

• Add-ADPrincipalGroupMembership
• Get-ADAccountAuthorizationGroup
• Get-ADGroup
• Get-ADGroupMember
• Get-ADPrincipalGroupMembership
• New-ADGroup
• Remove-ADGroup
• Remove-ADPrincipalGroupMembership
• Set-ADGroup

Let's create a new group in the specified Active Directory container (OU) using the command New-ADGroup:

New-ADGroup "TestADGroup" -path "OU=Groups,OU=Moscow,DC=corp,dc=winitpro,DC=ru" -GroupScope Global -PassThru –Verbose

Using attribute Description you can specify a description of the group, and using DisplayName change display name.

Parameter GroupScope You can specify one of the following group types:

• 0 = DomainLocal
• 1 = Global
• 2 = Universal

You can create a distribution group like this:

New-ADGroup "TestADGroup-Distr" -path "OU=Groups,OU=Moscow,DC=corp,dc=winitpro,DC=ru" -GroupCategory Distribution -GroupScope Global -PassThru –Verbose

Add-AdGroupMember – add users to an AD group

You can add users to an Active Directory group using the Add- AdGroupMember. Let's add two users to the new group:

Add-AdGroupMember -Identity TestADGroup -Members user1, user2

If the list of users you need to add to the group is quite large, you can save the list of accounts to a CSV file, then import this file and add each user to the group.

The CSV file format is as follows (list of users one per line, column name – users)

Import-CSV .\users.csv -Header users | ForEach-Object (Add-AdGroupMember -Identity ‘TestADGroup’ -members $_.users)

To get all the members of one group (groupA) and add them to another group (groupB), use this command:

Get-ADGroupMember “GroupA” | Get-ADUser | ForEach-Object(Add-ADGroupMember -Identity “Group-B” -Members$_)

If you need to copy members of all nested groups to a new group (recursively), you need to use the following command:

Get-ADGroupMember -Identity “GroupA” -Recursive | Get-ADUser | ForEach-Object(Add-ADGroupMember -Identity “GroupB” -Members$_)

Remove-ADGroupMember – remove users from a group

To remove users from an AD group, you must use the Remove-ADGroupMember command. Let's remove two users from the group:

Remove-ADGroupMember -Identity TestADGroup -Members user1, user2

Confirm removing users from the group:

If you need to remove users from a group according to a list from a CSV file, use this command:

Import-CSV .\users.csv -Header users | ForEach-Object (Remove-ADGroupMember -Identity ‘TestADGroup’ -members $_.users)

Get-ADGroup – get information about an AD group

The cmdlet will help you get information about the group Get-ADGroup:

Get-ADGroup "TestADGroup"

This command displays information about the main attributes of the group (DN, group type, name, SID). To display the value of all AD domain group attributes, run the following command:

Get-ADGroup "TestADGroup" -properties *

As you can see, attributes such as the time of creation and modification of the group, description, etc. are now displayed.

Using the Get-ADGroup cmdlet, you can find all the groups you are interested in using a specific pattern. For example, you need to find all AD groups whose names contain the phrase admins :

Get-ADGroup -LDAPFilter “(name=*admins*)” | Format-Table

Get-ADGroupMember – display a list of AD group users

Display a list of group users:

Get-ADGroupMember "TestADGroup"

To leave only usernames in the results, run:

Get-ADGroupMember "TestADGroup"| ft name

If this group includes other domain groups, to display full list members, including all nested groups, use the parameter Recursive.

Get-ADGroupMember ‘server-admins" -recursive| ft name

To upload a list of accounts belonging to a specific group to a CSV file (for further use in Excel), run the following command:

Get-ADGroupMember ‘server-admins" -recursive| ft samaccountname| Out-File c:\ps\admins.csv

To add user account data in AD to a text file, we will use the cmdlet. For example, in addition to the account, you need to display the position and phone number of the group user:

Get-ADGroupMember -Identity ’server-admins’ -recursive| foreach ( Get-ADUser $_ -properties title, OfficePhone|Select-Object title, OfficePhone )

(Get-ADGroupMember -Identity "domain admins").Count

It turned out that in the “domain admins” group we have 7 administrator accounts.

To find a list of empty groups in a specific OU, use this command:

Get-ADGroup -Filter * -Properties Members -searchbase “OU=Moscow,DC=corp,dc=winitpro,DC=ru” | where (-not $_.members) | select Name