Efficiently Extracting PWA Security Group User names In Bulk

BACKGROUND

Our mission is to find a simple and effective way to have a spreadsheet list of PWA Security Group users. Reasons for needing this list are bound by imagination. We recently had a client who wish to have an Excel list showing precise exact data of users list contained in multiple PWA Security Groups.

This exercise can easily be performed if you have a handful of users in each security group. But what if you have a plethora of users? You may have noticed there is no nifty “Export to Excel” button in the ribbon to save you this time. We could spend a couple of hours developing a PowerShell script, but if you’re in a hurry, it wouldn’t be an ideal solution. This blog simply aims to give you an alternative option. Whoever in their right mind turns down options, especially helpful ones.

The secret to extracting our users from PWA Security Groups is like any good treasure… buried, quite literally in the HTML source code. There are two ways we can go about locating the User list in  HTML source code after accessing your PWA Security Group. 

RESOLUTION

Firstly, we’ll need to access your PWA Security Group and navigate to Manage Groups.

Go into your PWA environment by right-clicking on the cog wheel located at the top right-hand corner.

Select “PWA settings” > Under Security section, click on “Manage Groups”

In the “Users” section, on the Group Name, select the PWA Security Group you would like to extract User name data from (in this example it’s “Portfolio Managers”), this data is located in a container labelled “Selected Users”.

I will first address the Time Saver Approach. And then the Scenic Route Approach (longer approach) which involves making use of your browsers Developer Tools.

Time Saver approach
If time is of the essence, and it generally is, here is the fast approach. Once inside your desired PWA Security Group, right click on the web page to get a pop-up window. Please select and click on “View page source”

This will open a new browser window with all the HTML source code at your disposal. To find the part we are interested in, you can use the Find command (Control F key) and type into the search bar:
<select name=”users_Beta” id=”users_Beta” size=”7″ style=”width:15em;”>
 

Notice we have found our Users. This time around we don’t have many. Your instance might be different. You could potentially have over a hundred people in a single PWA group.

Now, it’s as easy as selecting the users you wish to grab and pasting them into an excel spread sheet like so:

Of course you can copy just the names without the HTML tags but you would have to perform this multiple times for each user and then paste into your spreadsheet (this could become tedious).

Save some time by copying the whole HTML line for each user and pasting it in your spreadsheet. Some minimal tidy up might be required, essentially, we want to make sure we have all our users under one excel column.

This is the part where we introduce a little Macros script that will strip away all our HTML code for us in a very elegant and timely manner.

Using Macros to eliminate HTML tags
An Excel Macros is a piece of programming code which will run in an Excel environment. Very handy to automate routine tasks. A macro is an excel task recording and playback tool. This tool will allow you to record your excel inputs and then play it back to save the hassle of repeating mundane tasks constantly.

For the purpose of this blog, here is the set of instruction to use Macros to strip away your HTML code:

1. To enter Macros in Excel, go to “View” > “Macros” > “Record Macro…”
   
2. You will be presented with a pop-up window. Name the macro appropriately (avoid spaces in Macro name), see below for suggestion. Click “OK” once done.
   
3. Now go back to “”View” > “Macros” > “Stop Recording”.
   
4. Go back again to “View” > “Macros” this time you want to select “View Macros”.
5. Select the macros we have just created and click “Edit”. 
6. This will open a Visual Basic window. Visual Basic for Applications (VBA) window is a programming language that Excel utilises to record your steps as you perform routine tasks in an Excel environment.
   
7. Clear preexisting text in the editor window and replace with the following:Sub HTML_Removal() Dim Cell As Range With CreateObject(“vbscript.regexp”) .Pattern = “\<.*?\>” .Global = True For Each Cell In Selection Cell.Value = .Replace(Cell.Value, “”) Next End With End Sub
8. Your editor should now look like the image below. Click the “Save” icon to save this.
   
9. When saving, please make sure to save it as an Excel Macro-Enabled Workbook.
10. Select your entire User list in the spreadsheet. Anything selected will be affected by the Macros.
11. Go to “View” > “Macros” > “View Macros”.
12. Select the Macros we just saved.
13. Click “Run” to execute your Macros.
14. Clicking on the arrow will open up nested HTML content specific to the area you’re digging in.
15. Blue shaded selection panel allows you to determine which section of the web page you’re looking at.
16. As an added aid, Chrome’s Designer Tool tags the selected area with the corresponding line of HTML code you’re currently selecting. In the example below, that is Div#DeltaPlaceHolderMain