1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
<#
.SYNOPSIS
This PowerShell script retrieves Power BI tenant settings from the specified API 
endpoint and stores the information in a SQL Server database. It utilizes functions 
to install or update required PowerShell modules, execute SQL commands, add tables 
to the database, and insert data into tables.

.DESCRIPTION
The script connects to the Power BI service using the MicrosoftPowerBIMgmt module, 
retrieves tenant settings from the API endpoint "https://api.fabric.microsoft.com/
v1/admin/tenantsettings," and stores the data in a SQL Server database. 
The script includes functions for module management, database interaction, 
and table operations.

#>

param (
    [string]$sqlUsername = "SuperAdmin",
    [string]$sqlPassword = "**********",
    [string]$serverName = "DESKTOP-*******",
    [string]$databaseName = "PBI_Inventory",
    [bool]$clearOldData = $true
)

# Function to install or update a PowerShell module
function Install-OrUpdate-Module {
    param (
        [string]$ModuleName
    )

    $module = Get-Module $ModuleName -ListAvailable -ErrorAction SilentlyContinue
    if (!$module -or ($module.Version -ne '1.0.0' -and $module.Version -le '1.0.410')) {
        Write-Host "Installing or updating module $ModuleName ..."
        Install-Module -Name $ModuleName -Force -Scope CurrentUser
        Write-Host "Module $ModuleName installation or update complete"
    }
}

# Function to execute a SQL command on the provided connection
function Execute-DB-Command {
    param (
        [System.Data.SqlClient.SqlConnection]$connection,
        [String]$command
    )

    $cmd = $connection.CreateCommand()
    $cmd.CommandText = $command
    $cmd.ExecuteNonQuery()
}

# Function to add a table to the database
function Add-Table {
    param (
        [System.Data.SqlClient.SqlConnection]$connection,
        [String]$tableName,
        [String[]]$feature
    )

    $createTableScript = @"
        IF OBJECT_ID('$tableName', 'U') IS NOT NULL
            DROP TABLE $tableName;
        CREATE TABLE $tableName (
            $($feature -join " VARCHAR(MAX),`n")
            VARCHAR(MAX)
        );
"@

    Execute-DB-Command $connection $createTableScript
}

# Function to insert data into a table
function Insert-Data {
    param (
        [System.Data.SqlClient.SqlConnection]$connection,
        [String]$tableName,
        [String[]]$feature,
        [String[]]$insertData
    )

    $columns = $feature -join " VARCHAR(MAX),`n"

    $insertQuery = @"
    INSERT INTO $tableName ($($feature -join ', '))
    VALUES ('$($insertData -join "', '")');
"@

    Execute-DB-Command $connection $insertQuery
}

# Install or update the required PowerShell module for Power BI management
Install-OrUpdate-Module -ModuleName "MicrosoftPowerBIMgmt"
Login-PowerBI

# Set up the SQL Server connection
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Data Source=$serverName;Initial Catalog=$databaseName;User Id=$sqlUsername;Password=$sqlPassword;"
$connection.Open()

# Define tables and their features
$tables = @(
    @{
        Name = "dbo.PBI_TenantSettings"
        Features = @("settingName", "title", "enabled", "canSpecifySecurityGroups", "tenantSettingGroup")
    },
    @{
        Name = "dbo.PBI_TenantSettingEnabledSecurityGroups"
        Features = @("settingName", "enabledSecurityGroups_graphId", "enabledSecurityGroups_name")
    },
    @{
        Name = "dbo.PBI_TenantSettingProperties"
        Features = @("settingName", "name", "value", "type")
    }
)

# If specified, clear old data by dropping and recreating tables
if ($clearOldData) {
    foreach ($table in $tables) {
        Add-Table -connection $connection -tableName $table.Name -feature $table.Features
    }
}

# Retrieve Power BI tenant settings from the specified API endpoint
$tenantsettings = Invoke-PowerBIRestMethod -Url https://api.fabric.microsoft.com/v1/admin/tenantsettings -Method GET | ConvertFrom-Json

# Iterate through each tenant setting and insert data into corresponding tables
foreach ($tenantsetting in $tenantsettings.tenantSettings) {
    $settingName = $tenantsetting.settingName
    Write-Host "Loading $($settingName)"

    # Insert data into PBI_TenantSettings table
    $data_tenantSetting = @($tenantsetting.settingName, $tenantsetting.title, $tenantsetting.enabled, $tenantsetting.canSpecifySecurityGroups, $tenantsetting.tenantSettingGroup)
    Insert-Data -connection $connection -tableName $tables[0].Name -feature $tables[0].Features -insertData $data_tenantSetting

    # Insert data into PBI_TenantSettingEnabledSecurityGroups table for each enabled security group
    foreach ($enabledSecurityGroup in $tenantsetting.enabledSecurityGroups) {
        $data_enabledSecurityGroups = @($settingName, $enabledSecurityGroup.graphId, $enabledSecurityGroup.name)
        Insert-Data -connection $connection -tableName $tables[1].Name -feature $tables[1].Features -insertData $data_enabledSecurityGroups
    }

    # Insert data into PBI_TenantSettingProperties table for each property
    foreach ($property in $tenantsetting.properties) {
        $data_properties = @($settingName, $property.name, $property.value, $property.type)
        Insert-Data -connection $connection -tableName $tables[2].Name -feature $tables[2].Features -insertData $data_properties
    }
}

# Close the database connection
$connection.Close()

# Display completion message
Write-Host "Complete"