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.

 

RESOLUTION

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. 

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.  

  3. 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.
  4.  

  5. Now go back to “”View” > “Macros” > “Stop Recording”.
  6.  

  7. Go back again to “View” > “Macros” this time you want to select “View Macros”.
  8. Select the macros we have just created and click “Edit”.
  9.  

  10. 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.
  11.  

  12. 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
    
  13.  

  14. Your editor should now look like the image below. Click the “Save” icon to save this.
  15.  

  16. When saving, please make sure to save it as an Excel Macro-Enabled Workbook.
  17.  

    Now that you’ve saved the Macros, follow these four steps to clean the User list using that Macros in Excel:

    1. Select your entire User list in the spreadsheet. Anything selected will be affected by the Macros.
    2. Go to “View” > “Macros” > “View Macros”.
    3. Select the Macros we just saved.
    4. Click “Run” to execute your Macros.

     

    Your list will now be stripped of the unhelpful HTML tags. Proceed to format your list to your liking. Convert it into a table perhaps.

    Notes:
    Look to add a filter to aid you in further finding certain users in your PWA Security Excel columns.


     

    It’s really as simple as repeating this process for every PWA Security Group you wish to extract contained User data. You’ll have a helpful list in no time.

    Mission complete.

    As promised above, keep reading for the Scenic Route Approach and get a better understanding of utilising your browser’s Developer Tools to help with extracting our Security Group Users.

    Scenic Route – Making use of your browsers Developer Tools
    This method can be used if you wish to learn a little more about what goes on behind the scenes in HTML source code. The browser’s developer tool is easily accessible via an F12 keyboard input. For this exercise we will be using Google Chrome.

    Pressing F12 will open a side window. Make sure to select Elements.


     

    Please note that you can either dig your way down to find the source code or you use the element inspector to navigate to the source code you wish to focus on. See instruction below:

    Click on Element Inspector (red arrow) > Select User with cursor (green arrow) to open up HTML source code area (blue arrow) where user list is located.


     

    The longer method requires drilling down till you find this line of HTML code:
     <select name=”users_Beta” id=”users_Beta” size=”7″ style=”width:15em;”>

    If you’re interested in looking at the “behind the scenes” of a web page in terms of HTML code, using Designer Tool provides a guided tour to exploring this in an interactive approach. You will be guided by a selection panel that helps you navigate and locate exactly what it is you’re looking for through the use of blue highlighting on the page.

    Below are some points of interest:

    1. Clicking on the arrow will open up nested HTML content specific to the area you’re digging in.
    2. Blue shaded selection panel allows you to determine which section of the web page you’re looking at.
    3. 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


     

    Please note you can also copy all users contained in the nested HTML code section by selecting the top line and clicking on the ellipse on the far left.


     

    Select Copy > Copy Element.


     

    Paste into your Notepad or Notepad + +


     

    You are now ready for Using Macros to eliminate HTML tags section of the blog.