Tag Archives: PowerShell

ByHariharan Rajendran

Power BI Inventory using Power BI REST API

This blog post explains how the REST API can be used to prepare the Power BI Inventory Report. On this inventory report, we can show below items which almost covered all the contents from Power BI.

  1. List of Workspaces
  2. List of Datasets
  3. List of Reports
  4. List of Dashboards
  5. List of Tiles
  6. List of Data Gateways
  7. List of Data sources on the gateway
  8. List of users and their access.

 

Those REST APIs are wrapped into PowerShell script and exporting the result as csv files. You can schedule this PowerShell to get the latest contents from Power BI on a daily basis.

Pre-Requisite:

  1. Register an app and get the Client ID and Use it on the below script

 


# Parameters - fill these in before running the script!

# ======================================================

 

# AAD Client ID

# To get this, go to the following page and follow the steps to provision an app

# https://dev.powerbi.com/apps

# Level of access: check all boxes

 

$clientId = "xxxxxx-xxxx-xxx-xxx-xxx"

 # End Parameters =======================================

 function GetAuthToken

{

    if(-not (Get-Module AzureRm.Profile)) {

      Import-Module AzureRm.Profile

    }

     $redirectUri = "urn:ietf:wg:oauth:2.0:oob"

     $resourceAppIdURI = "https://analysis.windows.net/powerbi/api"

     $authority = "https://login.microsoftonline.com/common/oauth2/authorize";

     $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList$authority

     $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")

     return $authResult

}

 function get_groups_path($group_id) {

    if ($group_id -eq "me") {

        return "myorg"

    } else {

        return "myorg/groups/$group_ID"

    }

}

 # PART 1: Authentication

# ==================================================================

$token = GetAuthToken

 # Building Rest API header with authorization token

$auth_header = @{

   'Content-Type'='application/json'

   'Authorization'=$token.CreateAuthorizationHeader()

}

 # ==================================================================

# Get the list of groups

$uri = "https://api.powerbi.com/v1.0/myorg/groups/"

$all_groups = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

 $filePath = ("C:\Users\Hari\Documents\Allgroups.csv")

$all_groups | Export-csv $filePath

 # ==================================================================

# Get the list of gateways

$uri = "https://api.powerbi.com/v1.0/myorg/gateways/"

$all_gateways = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

 $filePath = ("C:\Users\Hari\Documents\AllGateways.csv")

$all_gateways | Export-csv $filePath

 # ==================================================================

# Get the list of gateways data sources

$uri = "https://api.powerbi.com/v1.0/myorg/gateways/"

$all_gateways = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

$all_gateways_id=$all_gateways.id

 foreach ($gateway in $all_gateways_id)

{

     $uri = "https://api.powerbi.com/v1.0/myorg/gateways/$gateway/datasources"

   $all_gateway_DS = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

  }

$filePath = ("C:\Users\Hari\Documents\AllGatewaysDS.csv")

$all_gateway_DS | Export-csv $filePath

 # ==================================================================

# Get the list of datasets from Groups

$all_datasets=@()

foreach ($group in $all_groups)

   $group_id = $group.id

     $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/datasets"

   $datasets = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   $all_datasets+=$datasets

  }

$filePath = ("C:\Users\Hari\Documents\alldatasets.csv")

$all_datasets| Export-csv $filePath

 # ==================================================================

# Get the list of reports from Groups

$all_reports=@()

foreach ($group in $all_groups)

   $group_id = $group.id

     $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/reports"

   $reports = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   $all_reports+=$reports

  }

$filePath = ("C:\Users\Hari\Documents\allreports.csv")

$all_reports| Export-csv $filePath

 # ==================================================================

# Get the list of dashboards from Groups

 $output_dashboard = foreach ($group in $all_groups)

   $group_id = $group.id

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards"

   $all_dashboards = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   New-Object -Typename PSObject -Property @{

   Group_Id = $group.id

   GroupName = $group.name

   displayName = ($all_dashboards.displayName -join ',')

     } | Select-Object Group_Id,GroupName, DisplayName

     }

$filePath = ("C:\Users\Hari\Documents\alldashboards.csv")

$output_dashboard| Export-csv $filePath

 # ==================================================================

# Get the list of tiles

$all_tiles=@()

foreach ($group in $all_groups)

   $group_id = $group.id

    $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards"

   $all_dashboards = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   foreach ($dashboard in $all_dashboards)

   {

     $dashboard_id = $dashboard.id

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards/$dashboard_id/tiles"

   $tiles = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   }

   $all_tiles+=$tiles

  }

$filePath = ("C:\Users\Hari\Documents\alltiles.csv")

$all_tiles| Export-csv $filePath

 # ==================================================================

# Get the list of users from Groups

 $output = foreach ($group in $all_groups)

   $group_id = $group.id

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/users"

   $all_group_user = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   New-Object -Typename PSObject -Property @{

   GroupName = $group.name

   Access = ($all_group_user.groupUserAccessRight -join ',')

   DisplayName = ($all_group_user.displayName -join ',')

   PricipalType = ($all_group_user.principalType -join ',')

   } | Select-Object GroupName,Access, DisplayName, PricipalType

     }

    

$filePath = ("C:\Users\Hari\Documents\AllUsers.csv")

$output | Export-csv $filePath

 ## ==================================================================

## Get the list of datasets from Groups with Group Names

 

$alldatasets_GP = foreach ($group in $all_groups)

   $group_id = $group.id

  $group_name = $group.name

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/datasets"

   $alldatasetsGP = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

       New-Object -Typename psobject -Property @{

   GroupId = $group.id

   GroupName = $group.name

   Dataset_Id = ($alldatasetsGP.id -join ',')

   DisplayName = ($alldatasetsGP.name -join ',')

     } | Select-Object GroupName,Dataset_Id, DisplayName

 }

$filePath = ("C:\Users\Hari\Documents\alldatasets_Groups.csv")

$alldatasets_GP | Export-csv $filePath

[/]
ByHariharan Rajendran

Always Encrypted–PowerShell Scripts Part-2

Applying the encryption to the table column is easy with the PowerShell. Please make sure to follow the below steps before run the below scripts.

1. Connect to SQL Server from PowerShell

2. Create column master key & column encryption key   – You can create by using wizard or PowerShell Scripts

Wizard method

PowerShell Script

3. Choose the column which you want to apply the encryption.

4. Defined the encryption type

 

Run the below script just continue with this script

$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.SSN” -EncryptionType “Deterministic” -EncryptionKey “CEK1”

Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces

You can append multiple encryption by adding + symbol, like below

$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.SSN” -EncryptionType “Deterministic” -EncryptionKey “CEK1”
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.BirthDate” -EncryptionType “Randomized” -EncryptionKey “CEK1”
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces

ByHariharan Rajendran

Always Encrypted–PowerShell Scripts

We have discussed that how to create a Always encrypted columns and steps to create custom column master key & column encryption key.

This article explains about the creation of keys with the help of PowerShell scripts.

Latest PowerShell Cmdelt’s update from Microsoft, https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/

Pre-Requisites:

Since Always Encrypted feature introduced in SQL Server 2016, we need to upgrade our SQLPS.exe. If you use the older version of SQLPS then that will not recognize the some of the below cmdlets. So make sure to upgrade SQLPS.exe first.

You can upgrade by installing the latest version of SSMS (SQL Server management studio) 2016.

Check for the below modules installed in PowerShell,

#List directory wise installed modules

Get-Module –ListAvailable

image

Check for SQLASCMDLETS & SQLPS. If there is no modules then make sure you have SQLPS.exe file available in below location

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules

# Import the SQL Server Module.
Import-Module Sqlps -DisableNameChecking;

 

Steps to Create a Column Master Key & Column Encryption Key.

In the Below script, we need to specify following information,

  • Server Name
  • Database Name
  • Column Master Key Name
  • Column Encryption Key Name

First Connect to the Server and Database using PowerShell Script

# Connect to your database.
$serverName = “<server name>”
$databaseName = “<database name>”

$connStr = “Server = ” + $serverName + “; Database = ” + $databaseName + “; Integrated Security = True”
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

As we know, we have different key stores available. This below script using “Windows Certification Store – Current User”.

# Create a column master key in Windows Certificate Store.
$cert1 = New-SelfSignedCertificate -Subject “AlwaysEncryptedCert” -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

In the above script, you can check the “CertStoreLocation”. To identify the location, you can check in T-SQL Statement to create master key. I just highlighted in below script for understanding purpose.

USE [Sample_Encrypt]
CREATE COLUMN MASTER KEY [test1]
WITH
(
    KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE’,
    KEY_PATH = N’CurrentUser/My/55A4DDAF58D274547B9C3BA5ABA154662CDF62D0′
)

GO

# Create a SqlColumnMasterKeySettings object for your column master key.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation “CurrentUser” -Thumbprint $cert1.Thumbprint

# Create column master key metadata in the database.
$cmkName = “<cmk name>”
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

# Generate a column encryption key, encrypt it with the column master key and create column encryption key metadata in the database.
$cekName = “<cek name>”
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName

Verify the keys in SQL Server Database Security folder.

ByHariharan Rajendran

Azure PowerShell Commandlets Issues

As usual, I was trying to use PowerShell Command line tool to manage my azure services. I used to prefer PowerShell as we have more control on it. This time, I got an error message, I was not able to run any Azure commandlets but I was able to add my azure account.

The error message is bit tricky and to solve the issue, I just follow closed and re-opened but still I got the same error message. I found that I got something issue with my saved credentials (token).

Error message,

Get-AzureSubscription : Failed to refresh token. AADSTS90002 : No Service namespace named ‘ ‘ was not found in the data store.
Trace ID:XXXXXXXXXX
Correlation ID: XXXXXXXXXXXXXXXXXX
TimeStamp: 2015-09-12 13:24:477 : The remote server returned an error : (400) Bad Request.

image

I followed the below steps to solve the issue,

Step 1: Explored the following folder in your system,  C:\Users\{User Name}\AppData\Roaming\Windows Azure Powershell.

Note: AppData folder will be in hidden by default and replace {User Name} into your system name.

Step 2: I saw the below files inside the folder and Just deleted all the files.

image

Step 3: Closed the PowerShell tool and reopen again.

Step 4: Added my azure account, this time, I was able to run all commands successfully.

1