Excel heals defects of Powershell

Powershell is just the Hell. I realized it especally when a customer asked me to add its new servers to the list of the users’ allowed machines (logon workstations).

You can add to this list easily, using the Active Directory Users and Computers console. When you click “Account” tab on a user, you’ll see “Logon to” button and when you click the button you can easily add individual machines to the allowed computers list.

But, using Active Directory Users and Computers console was not an option because of the number of the users. I had to find some automatic methods to accomplish this task.

One command I could use to set users’ info is DSMOD. This command allows us to modify the settings of an object in Active and it is handy. It is just there. Unfortunately, you can’t set logon machines with DSMOD.

Another option is using set-aduser cmdlet in PowerShell. This command has an option called –logonworkstations. This options allow us to specify logon machines. But, it has big defect; it does not “add” new machine info to the list. It rather replaces the list and it was what I wanted to do last.

So, I decided to use Excel to generate individual set-aduser commands for each of the users.

First, I obtained, SAM account name and allowed logon machines info of the users. I used get-aduser command to do it. But, this command’s output is a mess so, I piped the output of the command and formatted the output. The command was as follows:

get-aduser -filter * -properties userworktations | select samaccountname,userworkstations | ft samaccountname,userworkstations -autosize | out-file kliste.txt -width 300

The first part of the above command gets info about every user in the organization. Normally, “userworkstations” info is not included so, I used “-properties userworkstations” switch to indicate that logon machine info should also be retrieved.

Second part of the command gets info from the first part, and rips only samccountname and userworkstations info out of it.

Third part tabulates the output so that user and its machines info resides in separate lines.

Fourth and the last part of the command writes the resultant data to text file.

I opened the text file with Excel and placed samaccountname and userworkstations info to A and B columns. I organize C column to be “=concatenate(B1;”,server1,server2”). This formula makes a string consisting of the existing userwokstations info and “server1,server2” info.

Now, I had to prepare the D column. Its formula was “=concatenate(“set-aduser –identity “;a1;” –logonworkstations “;c1)”. A sample result was “set-aduser –identity jale –logonworkstations jalepc,tsserver,server1,server2”. After I checked the content of the D column, I selected D column and copied and pasted to a text file. You can name this file’s extension as “.bat” or “.ps1”. Either one works.

I fired a PowerShell environment, switched to the file’s location. By default, you can not run scripts from the Powershell so I issued “set-executionpolicy remotesigned” command just to be able to run the script.

After that command I run “.\kliste.ps1” command. Why do we have to add “.\” before the command; it just another quirk of PowerShell.

But the result was satisfactory; I added the, not replaced, to the logon workstations list of the user.

Last Word: Every good systems engineer must learn Excel somewhat.

Murat Yildirimoglu


Istanbul, Turkey


Bir Yanıt to “Excel heals defects of Powershell”

  1. A new Powershell command structure is necessary | Muratyildirimoglu's Blog Says:

    […] https://muratyildirimoglu.wordpress.com/2013/03/15/excel-heals-defects-of-powershell/ […]

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Google fotoğrafı

Google hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Connecting to %s

%d blogcu bunu beğendi: