Category Archives: Office365

Power BI and Data Security – Audit Logs, Powershell, Power BI and @AngryAnalytics

The following has been reposted with permission from Steve Howard a.k.a. @AngryAnalytics. I have made some formatting changes but the content is unchanged. Thanks again to Steve for allowing me to repost this content. You can find the original post and the rest of Steve’s great work on his blog.

Power BI Audit Log Analytics Solution

As Power BI adoption in your organization grows, it becomes more and more important to be able to track the activity in the environment.

When you start to think about deploying a Power BI Audit Log solution that is repeatable there are a few challenges that you will face.

  • Going to the O365 Audit Logs portal each time you want to extract log events is a manual process and doesn’t scale
  • When automating this process through API or PowerShell, there is a limit to how much data you can pull, therefore examples that are currently available also don’t scale very well
  • The AuditData field is a JSON format by default and although Power BI can parse JSON beautifully, when doing this over several thousand record entries may result in data load errors

Based on these factors, i have put together a PowerShell script that can be scheduled on a nightly basis that can iterate MORE than 5000 records so that no data is lost. Also, the screenshot below is of an initial template that you can use to start with to analyze your audit logs for your organization.

TL;DR

  • The required files can be found on my GitHub
  • Update the PowerShell script with a UserID and Password that has O365 audit log privileges
  • Use Task Scheduler to schedule the PowerShell script to run each night at midnight (run as admin).
  • At the end of the script, specify the directory you would like the script to generate CSV files in
  • In the PBIX file, it was challenging to get a parameter to work for the file location that the CSVs are in, so in the Query Editor the script for the AuditLog table needs to be manually modified to include your file path.
  • Enjoy

Quick look at the PowerShell

First, there is a PowerShell script.

Set-ExecutionPolicy RemoteSigned

#This is better for scheduled jobs
$User = "<<enter o365 admin user email here>>"
$PWord = ConvertTo-SecureString -String "<<enter password here>>" -AsPlainText -Force
$UserCredential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $User, $PWord

#This will prompt the user for credential
#$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session

$startDate=(get-date).AddDays(-1)
$endDate=(get-date)
$scriptStart=(get-date)

$sessionName = (get-date -Format 'u')+'pbiauditlog'
# Reset user audit accumulator
$aggregateResults = @()
$i = 0 # Loop counter
Do { 
 $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate `
 -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -RecordType PowerBI
 if ($currentResults.Count -gt 0) {
 Write-Host (" Finished {3} search #{1}, {2} records: {0} min" -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $currentResults.Count, $user.UserPrincipalName )
 # Accumulate the data
 $aggregateResults += $currentResults
 # No need to do another query if the # recs returned <1k - should save around 5-10 sec per user
 if ($currentResults.Count -lt 1000) {
 $currentResults = @()
 } else {
 $i++
 }
 }
} Until ($currentResults.Count -eq 0) # --- End of Session Search Loop --- #

$data=@()
foreach ($auditlogitem in $aggregateResults) {
 $datum = New-Object –TypeName PSObject
 $d=convertfrom-json $auditlogitem.AuditData
 $datum | Add-Member –MemberType NoteProperty –Name Id –Value $d.Id
 $datum | Add-Member –MemberType NoteProperty –Name CreationTime –Value $auditlogitem.CreationDate
 $datum | Add-Member –MemberType NoteProperty –Name CreationTimeUTC –Value $d.CreationTime
 $datum | Add-Member –MemberType NoteProperty –Name RecordType –Value $d.RecordType
 $datum | Add-Member –MemberType NoteProperty –Name Operation –Value $d.Operation
 $datum | Add-Member –MemberType NoteProperty –Name OrganizationId –Value $d.OrganizationId
 $datum | Add-Member –MemberType NoteProperty –Name UserType –Value $d.UserType
 $datum | Add-Member –MemberType NoteProperty –Name UserKey –Value $d.UserKey
 $datum | Add-Member –MemberType NoteProperty –Name Workload –Value $d.Workload
 $datum | Add-Member –MemberType NoteProperty –Name UserId –Value $d.UserId
 $datum | Add-Member –MemberType NoteProperty –Name ClientIP –Value $d.ClientIP
 $datum | Add-Member –MemberType NoteProperty –Name UserAgent –Value $d.UserAgent
 $datum | Add-Member –MemberType NoteProperty –Name Activity –Value $d.Activity
 $datum | Add-Member –MemberType NoteProperty –Name ItemName –Value $d.ItemName
 $datum | Add-Member –MemberType NoteProperty –Name WorkSpaceName –Value $d.WorkSpaceName
 $datum | Add-Member –MemberType NoteProperty –Name DashboardName –Value $d.DashboardName
 $datum | Add-Member –MemberType NoteProperty –Name DatasetName –Value $d.DatasetName
 $datum | Add-Member –MemberType NoteProperty –Name ReportName –Value $d.ReportName
 $datum | Add-Member –MemberType NoteProperty –Name WorkspaceId –Value $d.WorkspaceId
 $datum | Add-Member –MemberType NoteProperty –Name ObjectId –Value $d.ObjectId
 $datum | Add-Member –MemberType NoteProperty –Name DashboardId –Value $d.DashboardId
 $datum | Add-Member –MemberType NoteProperty –Name DatasetId –Value $d.DatasetId
 $datum | Add-Member –MemberType NoteProperty –Name ReportId –Value $d.ReportId
 $datum | Add-Member –MemberType NoteProperty –Name OrgAppPermission –Value $d.OrgAppPermission

#option to include the below JSON column however for large amounts of data it may be difficult for PBI to parse
 #$datum | Add-Member –MemberType NoteProperty –Name Datasets –Value (ConvertTo-Json $d.Datasets)

#below is a poorly constructed PowerShell statemnt to grab one of the entries and place in the DatasetName if any exist
 foreach ($dataset in $d.datasets) {
 $datum.DatasetName = $dataset.DatasetName
 $datum.DatasetId = $dataset.DatasetId
 }
 $data+=$datum
}

$datestring = $startDate.ToString("yyyyMMdd")
$fileName = ("c:\PBIAuditLogs\" + $datestring + ".csv")
Write-Host (" writing to file {0}" -f $fileName)
$data | Export-csv $fileName

Remove-PSSession -Id $Session.Id
  • Notice that you need to enter O365 audit log privileged credentials at the top so that this can be ran automatically. If you have more clever ways to pass these credentials in so they are not exposed in the file by all means, do that
  • The Do/Until loop handles if there are more than 5000 records in the result set which would easily be the case for a large Power BI community.
  • The foreach loop extracts the AuditData column JSON format and creates an individual record for each entry. This makes the Query Editor in Power BI less complex and easier to accomplish retrieving several hundred thousand records without import errors
  • finally we create a CSV for the data with the date of the file entries (yesterdays info if this is ran at midnight every day). This dumps each file in c:\PBIAuditLogs. You can obviously change this file location to wherever you want to store your CSV extracts

You can use Task Scheduler to run the above PowerShell script every night at midnight.

The PBIX file

In the Power BI file, we are connecting to the content of the entire folder shown above. I went ahead and included the PBIX file WITH the sample data so you could get an idea of what your data may look like.

This is where i have to admit that i tried to use a parameter for this but ran into some Query Editor challenges with how Power BI creates a Sample File transform to import multiple files from a single folder. If you can see what i did wrong here I would love your feedback, but for now, you can ignore the file directory parameter in the Query Editor and need to go to “Advanced Editor” on the “AuditLog” query and modify the file location to be the location you are dumping files from the PowerShell script.

Change the below file location as needed.

Once you have made this change, you should be able to “Close and Apply” and your data will now be populated in this basic audit log analytics view.

Using the file

I created a couple basic pages to get this blog post shipped and so you can start taking advantage of the solution, but it is nowhere near as complete as you can eventually make it. I have a simple overview page that was screenshotted above. It can help you determine number of active users, reports, dashboards, and datasets being used for any time period your audit log data covers.

The second page is a user activity view I created from a calculated table. It helps you determine which users in the system may be inactive so you can re-assign power bi licenses and also shows detailed activity for an individual user that you can select from the slicer.

Other things you can mine from this data:

  • Who has signed up for Free Power BI trials
  • What “Apps” are being created
  • what embed tokens are being generated and used
  • many other possibilities

The PowerShell script and the PBIX file are located on my GitHub here

Link to the original post:  http://angryanalyticsblog.azurewebsites.net/index.php/2018/02/16/power-bi-audit-log-analytics-solution/

Thanks again, Steve

Advertisements

SQL Saturday – Dallas – May 2018

sqlsat-dallas-2018I was able to present at SQL Saturday Dallas this year. Thanks to those of you who were able to attend. As I noted in the meeting you can find details related to Power BI Data Security in the following posts on my site.

Power BI Is Finally in the Azure Trust Center

Power BI Data Security – Sharing in Email

Power BI Data Security – Sharing

Power BI and Data Security – App Workspaces and Power BI AppsPower BI Security Logo

Power BI and Data Security – Free User’s Cannot Share, Read Only in Premium

Power BI and Data Security – Row Level Security (RLS)

Power BI and Data Security – Data Classification and Privacy Levels

Power BI and Data Security – On-premises Data Gateway

Power BI and Data Security – Sharing Data

Power BI and Data Security – Compliance and Encryption

I have also added the presentation here if you want to review it as well.

Thanks again for joining me in Dallas.

Power BI Data Security – Sharing in Email

 

Power BI Security LogoMicrosoft has expanded sharing by allowing users to share Power BI content via email. In a previous post, I discussed how sharing content within your organization should be handled carefully. However, the new process opens up the opportunity to share outside your organization by sending an email. In particular, you can now share with users who have a personal email address such as @outlook.com and @gmail.com. Let’s dig into the implications of this capability.

Sharing Using Email

First, you need to be aware that this functionality is as simple as the original methods of sharing. You click the Share button on your report or dashboard to open the Share dialog.

The Share report dialog in this case accepts email addresses which is not a significant change. However, as shown below, you can add personal emails and emails outside your organization. You be warned, but users do not always pay attention to this or understand the implications.

Share report - outside

You will also notice that consumers need to still have a Power BI Pro account assigned to them or you need to be using Power BI Premium for this to work.

Following the Email Process

When you share, you usually will need to send an email to the recipient. Here is the email content.

Report Share EmailTime to click the report link. This opens a series of dialogs which determine how much you have access. It is important to note that this is all made possible with Azure B2B. More about that in a moment. Let’s trace the story through. The link opens the following page.

Report Share Email - Welcome Link

As you can see, the next step is to log in. I am using an outlook.com account so it prompts me to authenticate. Once I have authenticated, I get the following notice.

Report Share Email - Opened Report

My account does not have Power BI Pro, but now I can try it for free for 60 days and get access to the data while I am on the trial. I clicked both options, because I can. The Upgrade account option would require me to pay for Pro. However, Try Pro for free works and I was able to access the report fully. I have successfully shared my corporate content with a personal user.

Preventing Sharing Outside Your Organization

While in some cases, you need to share outside your organization, we will assume here you need to disable this functionality. There are a few places you can make this happen.

Power BI Admin Portal

First, in Power BI go to the Admin portal and disable sharing outside your organization. If you have followed my previous advice, this will already be disabled.

 

PBI Admin Portal - Disable Sharing

As you can see, this will disable content for users who have been shared with previously. If you need to share, you can specify groups that have that permission.

Office 365 Admin Center

Next, this can be turned off in the Office 365 Admin Center in the Security and privacy area.

PBI O365 Admin Center - Disable Sharing

This prevents the ability to add guest users to the organization. This will disable this capability across Office 365. There is no option to allow some users this access. Once this is disabled, sharing outside the organization which requires a guest user will not be possible.

Azure Active Directory

Finally, you can shut this down from Azure Active Directory. Guest users are ultimately managed through Azure Active Directory and this is the best place to turn this off corporately if you do not need this functionality.

PBI AAD - Disable Sharing

In AAD you have four options.

  1. Guest users permissions are limited. This limits guest user capabilities with regard to the directory. Yes is the default and recommended.
  2. Admins and users in the guest inviter role can invite. This would be a typical option we can understand. However, it is important to note that Admin users in Power BI workspaces will have the ability to create guest users and share reports externally with this permission on.
  3. Members can invite. Just like it sounds. Any member of a group can invite guest users in.
  4. Guests can invite. This allows guests to invite other guests. Seems dangerous to me.

As you can see from my tenant, the options are all on which is the default. Be sure to understand what capability you want to use and set it appropriately within your tenant.

Tracking Sharing

In the Office 365 logging, you can see who and what has been shared. This log covers internal and external shares and should be monitored for auditing and compliance purposes.

Azure B2B

Azure B2B and the sharing capabilities in Power BI go hand in hand. This allows organizations to share content in a controlled fashion to consumers outside their organization. While this is required for certain scenarios, be mindful of who has the capability to share, and track sharing to make sure the data is being handled as you require.

Final Thoughts and References

You need to remember that sharing is at the heart of Power BI and you need to manage how and who can share. If you need to do more extensive sharing, by all means, use these features. For those, who need to lock it down tighter, you can follow the steps above to prevent sharing until you have a process and pattern. Power BI continues to improve and grow and as that happens we can expect more security options to support the new functionality. Enjoy Power BI, it is a great tool and will only continue to get better.

References

Using Azure AD B2B with Power BI

Auditing Power BI

Share your Power BI content with anyone by email

 

 

Power BI Data Security – Sharing

Power BI Security LogoMicrosoft recently added more sharing capabilities that may change my view on sharing within the enterprise. As with all things Power BI, change is inevitable.

Up to this point, I recommended that customers did not use sharing as an enterprise solution due to the inability to manage it and the potential to share data within the organization that violates compliance or internal rules.

Sharing Within Your Organization

When you share a dashboard or a report within your organization, you share the data with it. Here is the issue from my perspective. If you allow users to share content, they are responsible to share responsibly. That is correct. The content creators are now responsible to manage security as well. So, let’s walk through the basics of using sharing effectively and securely within your organization.

Why Share?

The primary reason to use share is to distribute content outside the context of a Power BI App. Power BI Apps should be your first mechanism for sharing content within your organization. It requires more thought and planning which is typically a good idea with your companies data. However, there are times when sharing makes sense. With the ability to share reports, you can limit sharing to specific areas. Also, you may want to create a “one-off” report for use in decision making but not something to be deployed in the long term.

Sharing is very different from deploying Apps. App deployment is not that difficult to do, but prevents sharing and is much easier to manage access.

The Process of Sharing

Sharing capabilities are readily available on any content that you create.

At this point, there is no way to prevent sharing within your organization. Content can be shared from My Workspace as well.

The first step to sharing is to click the Share button on the report or dashboard you want to share.

PBI Share Button

This will launch a dialog for sharing the report or dashboard as shown here:

PBI Share Dialog

I have highlighted a couple of key parts to the dialog. The first is that you can share with individuals, distribution lists, and security groups. This is similar to the permissions you can apply to an App during deployment. As a content creator, I can distribute in this fashion. Typically power users who create content will use individual names or distribution lists as they are the most common methods of working with teams.

The next part to understand is the Allow recipients to share your report option. I have a couple of issues with this option. First, it is on by default. This means if someone shares with a peer in their department that individual can then share outside their department. The original content creator no longer has control of who this is shared to when this option is turned on which is my second issue. While the content creator will be able to see everyone they share with in the Access panel of the dialog when they review it later, they have potentially released data “into the wild” without controls if they do not set this up properly.

Click Share. You have successfully shared your report. Next, let’s have a look at the Access panel after the share is done. This panel is used view and manage sharing within the workspace.

PBI Share - Access Dialog

When in this dialog you can see who has what level of access to the report or dashboard you are currently in. You will see all reshares here as well. This will allow the content creator to remove access if needed.

The Manage permissions link opens up a dialog that lets you view and manage permissions for the entire workspace.

PBI Share - Manage Access

As you can see, sharing is managed by content creators. It will be important for them to understand the process.

Monitoring Sharing

Your Power BI environment should have auditing turned on. This will allow you to run reports to understand who has shared reports and dashboards across the tenant. This will be required to manage auditing and compliance within your organization.

Sharing and Security Thoughts

As I worked through this capability, there are a couple of closing thoughts on security to keep in mind.

  1. You cannot prevent sharing. You must monitor it, so be sure you have auditing turned on in your subscription.
  2. This has a place when sharing on a smaller scale. I would not recommend it as the standard process, but it allows you to share content in smaller chunks.
  3. You must have a process and policy for sharing. This has to be understood by content creators.
  4. If you implement row-level security in Power BI or SSAS, it is honored in sharing. This will prevent unauthorized access to sensitive data. Use this when you have particularly sensitive data in use.

One other thought. If this is a significant concern, you should evaluate Power BI Premium as it will allow to manage which users have the capability to create and share content. Free users are effectively read only within the organization. This will be cost-prohibitive for smaller organizations unless security is the primary concern.

Properly planned for you will be able to share effectively with Apps as a deployment model.

Power BI and Data Security – App Workspaces and Power BI Apps

Power BI Security LogoShortly after I published the Power BI Security Sharing Data post in April, Power BI Premium, Power BI Apps, and Power BI App Workspaces were released. These changes impacted that post in many ways. As part of the follow up, I also did an updated webcast with Pragmatic Works. This is a follow up post with some of the changes called out.

We are in the process of restructuring our organization around Apps and App Workspaces. Here are some of the highlights and changes related to sharing data using these new features.

Information Architecture and the Importance of Planning

My company, Pragmatic Works, uses a number of collaborative features in Office 365 including Planner, Teams, SharePoint, and Power BI. With this level of usage, a number of Office 365 groups get created. As we begin the process of updating our reporting structure, we will be using the App Workspace model to manage content creation and the Power App model to deploy content to users.

Before we began, we had to understand who the content creators are and who the consumers would be. App Workspaces are currently managed as Office 365 groups. We have a lot groups that match to our consumers, however, they really don’t work for content creators. Why? As we began the research, consumers exist in the current groups and are excellent targets. App Workspaces already exist for these users and groups due to our use with Teams and SharePoint. But due to the current limitations within Power BI and Office 365 with regards to group management, we need to create new App Workspaces, which also create new Office 365 groups to manage content creators. Typically these groups will be small and easy to manage. By limiting the users in these workspaces, we are also able to keep the additional clutter that is required.

In our process, we treated the end result as the guidance for the required workspaces. Each workspace creates and app that we want to target a specific set of consumers. By starting there, we created the list of workspaces we need to create. Because the apps and workspaces have a 1:1 relationship with each other, the apps (collection of Power BI content with the same permissions) are the determining factor for whether a workspace will be required. Our goal was to have the appropriate level of security while still minimizing management of the additional workspaces.

App Workspaces

We created the App Workspaces based on our Information Architecture Plan. The workspaces were created with two admins and set with members who would be content creators. Part of our exercise was to understand the impact of changing roles in Office 365 and related products such as Teams. What we learned is that Admin and Owner roles are shared throughout and managed by the Office 365 group. If you make a user an Owner in Teams they become an Admin in the matching Power BI Workspace. THIS IS IMPORTANT! While creating additional workspaces for report creation adds complexity by creating Office 365 groups, we have different security and content management rules for Power BI groups.

The Admins have the ability to add users to the group. Members do not. Also a Team, for instance, may have 100s of members who are essentially consumers. We are using the same role, Member, to assign to content creators. Consumers will use the Power BI Apps to view and consume the data made available. Because of this distinction, we created new Power BI App Workspaces.

When creating Members in Power BI Workspaces, you have the option to make those members View Only. However, doing so means all content creators will need to be Workspace Admins. This may work well for your organization, but remember Admins have elevated permissions as they are also Owners in Office 365 groups.

Preventing App Workspace Creation

Currently the only way to prevent App Workspace creation in a Power BI subscription is to disable the ability to create Office 365 groups or limit that capability to a small group of people. (NOTE: This affects all Office 365 applications which use Groups to segment the app such as Teams.) This is done using PowerShell. You can find details here including what applications are affected by this change.

Power BI Apps

In order to use Power BI Apps, all users need to have a Power BI Pro license or the apps need to be deployed to Power BI Premium. Whether you choose to use Pro or Premium should be evaluated for your organization. With current retail pricing, around 500 consumers is the “break even point” when only considering licensing. I will be discussing non-license related reasons to choose Premium in a later post.

When publishing or updating an app as noted in the images below. You have the ability to assign permissions to the app. Unlike Workspaces, you are able to assign distribution lists, individuals, and security groups to an App. This allows you to manage consumers using Active Directory (AAS). PBI Workspace and App

PBI App Permissions

Once Apps are deployed, a link can be sent to the users or they can find it in their available apps from Microsoft Appsource and their organizations deployed apps. They should only see apps they have been given permissions to. Once they get the app and open it, they have read only access but full Power BI interactive capabilities.

Power BI Apps will honor Role Level Security (RLS). However, unlike content packs, users cannot modify or change any content in the app including dashboards. When using apps, you are essentially creating the entire experience for the user and it cannot be “personalized” with their own dashboard or other updates. For enterprise reporting scenarios, this makes sense. For self-service or configurable solutions, you should still consider Content Packs.

To finish our story, we will be deploying Apps based on reporting groups within our Active Directory structure. This will allow us to control access to reporting through standard processes. At the moment, a group of us will manage the Workspace groups to prevent unwanted exposure to data and to manage report “creep”. In some ways, this is contrary to the original purpose of Power BI as a self service product. We are not limiting our teams capability to do their own report creation, app deployment, or self service analytics. We are making a point that the Enterprise Reporting will be managed which most organizations need on some level. If you have a great report you want to include, the Power BI Desktop allows us portability. The only not portable portion is the Dashboard itself. Hopefully we will be able to transfer that between workspaces in the future.