Dangers of distribution lists

Distribution list are amazing things, they allow us to send mail to our team without having to remember who exactly is in our team, this is especial true in this modern age where we are part of many different teams, sometimes without even realising. These lists can contain hundreds or even thousands of members and the latest NHS IT bug has left IT departments maybe wanting to double-check their distribution lists – hopefully before the security team comes a knocking.

Luckily, this is really easy in Excel 2016. Built into Excel 2016 is PowerQuery and one of the out-of-box connectors is Active Directory. With a bit of PowerQuery magic, you can easily pull this data out.

So we want to:

  • Connect to our Active Directory
  • List out all our Groups
  • Filter to email-able Groups
  • Include
    • the group owner
    • the members
    • any security \ restrictions on who can email the list

Note: If your unluckily enough to be running an old version of Excel, it possible to do the same thing in Power BI Desktop, which is a free download.

Below is the M code to do this:

For more help on how to enter the M code into Excel see my previous post.

UPDATE: Please note that this only works with Active Directory and doesn’t work with Azure Active Directory. Others have said this is possible via odata or such however I haven’t tried… yet.

Comments

One of the annoying things about working with multiple languages is they each have their differences – obviously, otherwise there would only be 1 language! Although there is good reason to have these differences they still have elements that are a pain. One of the most annoying things, other then “do you require a semicolon at the end of the statement?” is comments. Comments aren’t executed or complied but they help developers read code. Remember, the code is a interpretive language, its designed to be gateway between the machines and humans, if you write something complex your more then likely need to be include more comments explain what it does or look at refactoring so other developers can continue your work.

The few options are:

Double dash (–)

— Single line quote

Single Hash (#)

# Single line quote

Double forward-slash (//)

// string domain = “matt40k.uk”;

All of these are single line quotes, as in each line needs the require comment and the comment continues till the end of the line \ carriage break.

You also got:

Forward-slash and asterisk (/*)

/* Multi-Line
quote */

Forward-slash and asterisk marks (/*) the start of the comment, the comment continues until the asterisk marks and Backward-slash (*/). The code must close any open Forward-slash and asterisk marks (/*) comments in order to compile.

With Forward-slash and asterisk, you can comment in-line, for example

Select * from /* Change the code to the right */ sys.tables

This is valid T-SQL that will return the all the columns for meta data about tables on the selected database

You can also use this across multiple lines, for example

select

TableSchema = schema_name(schema_id)

,TableName = name

/*
You could also add

other tables

 

*/
from

sys.tables

 

Power Query Formula (M) Language

Double dash (–) No
Single hash (#) Yes
Double forward-slash (//) No
Forward-slash and asterisk (/*) Yes

Transact-SQL (TSQL)

Double dash (–) Yes
Single hash (#) No
Double forward-slash (//) No
Forward-slash and asterisk (/*) Yes

C sharp (C#)

Double dash (–) No
Single hash (#) No
Double forward-slash (//) Yes
Forward-slash and asterisk (/*) Yes

 

Fun fact, my most commonly used comment?

To-Do

Incorrect x-axis dates

So yesterday I was playing with PowerBI and I hit a problem.

XAxis-Error

As you can see, the x-axis is wrong. For some reason it wasn’t creating the correct range.

After a little while I thought I’d try out Microsoft’s feedback option – the smiley faces – in the top menu you have a smiley face. Click it, then you can send feedback.

Feedback

Sure enough after a little while, Justin Schneider from the Power BI team replied asking a few more questions – basically asking me to check the data was correct, which was in this case and even offered a solution.

The problem was the auto options for the X-Axis range was wrong and he recommended setting it manually.

To this is simple, click on the visual with the faulty X-Axis, then on the left hand menu, click the paint brush, then expand the X-Axis and manually enter the start and end

XAxis-fix

And voilà its fixed!

XAxis-fixed

Getting a list of AD Groups and their members using PowerQuery

The Power Query Formula Language (informally known as “M”) is a powerful mashup query language optimized for building queries that mashup data. It is a functional, case sensitive language similar to F#,  which can be used with Power Query in Excel and Power BI Desktop.

A few days ago I got asked to produce a list of users (and their email address) in a number of AD Groups. I already had a SSIS package that had a script task to pull this data from Active Directory and push it into a SQL database and we have a PowerShell script to get the same data in our code repo. After work I set about repeating it but using PowerQuery.

Apart from one quirk the process was pretty straightforward. PowerQuery has built in support for Active Directory. Just click the Data tab, then select New Query > From Other Sources > From Active Directory

PowerQuery - Excel > Menu

This will then pop up with a windows asking for you to select your domain, assuming your machine is joined to the domain and your logged on as a domain user, this should be prefilled in with the correct domain.

PowerQuery - Excel > Connection

The next window just asks you confirm your credentials, you’ll most likely want to use your current windows user.

After you’ve connected to your Active Directory, you’ll be able to navigator, your be able to select your domain from your forest and then select the object you want to query. For this example, select your domain then select group, then click Edit.

PowerQuery - Excel > Navigator

This will then open up the Query Editor

PowerQuery - Excel > Query Editor

This will then list out the groups, or at least a sample. Click on the left\right arrow on securityPrincipal column header, this then bring up a filter window, select sAMAccountName and click on OK. This will give you the friendly name your more then likely to know the groups by.

Now its a question of filtering the list – if you right-click on the sAMAccountName and select Text Filters > Begin With. You can select one of the other options, if you make a mistake or you want to refine it, on the right-hand side, you have Query Settings > Applied Steps, if you click the cog next to Filtered Rows your get

PowerQuery - Filter

this hopefully doesn’t need any explanation. You can of course filter on other columns if required, such as OU group.

Adding the users involves expanding the member

And the final M script looks like field like we did with securityPrincipal until we get to the individual user object.

The last point is where I hit a few snags. Firstly, I couldn’t expand into the user object, luckily, clicking on Advanced Editor on the Home tab revealed the M code. I’ve included my code below which I hope will help.

I’ve personally found this a useful dive into PowerQuery, hopefully you have too 🙂

Power BI – custom visuals

Power BI is Microsoft new cloud based Business Intelligence platform, one of the cool things about it is it built atop internet standards and leveraging open source libraries like D3.js for its visuals. Microsoft has continued on the path of open source-ness by open sourcing some of its codes allowing Joe Public to submit custom visuals to Power-BI, all via GitHub.

Yesterday saw the introduction of 4 new visuals, one in particular looked very interesting – Hierarchy Slicer by Jan Pieter Posthuma.
Hierarchy SlicerThis brings in some more missing features into Power-BI. For more information on Hierarchy Slicer visit his blog.

The other one worth a mention – as it adds some comedy to any report is the Meme visual by Sachin Patney.

Meme

If  your interesting in learning more about Custom Visuals, Marco Russo is speaking at the London Power BI User Group where is is going to talk about Using Custom Visuals in Power BI.