PC Encryption Dashboard – Part 1

Problem: We have a report system that provides encryption information of all PCs in the company, for example which PCs are encrypted and which are not, how and when they are encrypted. We use that system to see how many percent of the PC population are encrypted because there’s a KPO for that. Then we export the daily data to an Excel file and use it for the team to follow up the not-encrypted PCs, to complete the KPO. Using Excel, we can have pivot tables to see the quantity of not-encrypted PCs in each location, but we can’t see which PCs have been fixed in each location, which haven’t been fixed and which are reported as new not-encrypted PCs. I mean we can do that but it takes a lot of manual queries and macros in Excel.

So now, we’ll create a central PowerBI dashboard for the team to see the unencrypted PC status in each location, which should provide:

  • Total unencrypted PCs in each country
  • Details of Fixed PCs from the last report
  • Details of Not fixed PCs from the last report
  • Details of New unencrypted PCs from the current report

Why PowerBI: It’s the tool we have in hand, included in O365 suit (the Pro license needs to be purchased separately). It provides handy tools to work with data set like create relationship and joining tables. It provides excellent dashboard building features. And as a O365 app, it allows sharing the dashboard so much easily.

1. Get Data

Data source: Excel files exported from the local system database.

Open PowerBI Desktop and select Get data from the Wizard dialog box.

1_Get data

Select From File, Excel, and browse to the data file location, here I placed 2 days into 1 file as 2 sheets.

In Navigator dialog, check 2 sheets and click Load. No need to Edit the data at this time.

4_Load File

Then we got the data loaded. Those are PC encryption data for 2 days: 12 Aug and 4 Aug.

5_Data Loaded

2. Clean up Data

In this steps, we’ll select only what we want from a big bucket of data. From Data View, select Edit Queries, then in the Edit Queries window, focus on 12Aug tab and select Choose Columns.

6_Edit Query                           7_Choose Column

There are many Columns in the file, uncheck the (Select All Columns) to deselect all and only choose which Columns required for the information we need to know:

  1. DDay: We need it to make comparison of the statistics between days.
  2. Asset ID: This will be the index column
  3. Service Tag: Need to show in computer details
  4. Encrypted: Need to filter the encryption status
  5. slbitbuilding: Need to show in computer details
  6. Country Code: Need to show in computer details

8_ChooseRequiredCol Then click OK. Next, we only need information about Un-Encrypted PCs, so we’ll filter out the Encrypted column, leave only No values there. The filter may not load all the values, just click Load more to show all up.

So far we have the data we want for 12Aug table:

11_Filtered 12Aug

Perform similar steps to clean up the 4Aug table:

11_Filtered 4Aug

Don’t forget to click Close and Apply to exit the Edit Queries window.


Now the data clean up job is done, we’ll continue with Extract information step in the next part.

PC Encryption Dashboard – Part 2

PC Encryption Dashboard – Part 3

About dongthao

"Man does not simply exist but always decides what his existence will be, what he will become the next moment"
This entry was posted in Data Science, Software Tutorial and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s