Skip to content

Instantly share code, notes, and snippets.

Aaron Nelson SQLvariant

Block or report user

Report or block SQLvariant

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@SQLvariant
SQLvariant / GatewayClusters.ps1
Last active Oct 15, 2019
Get the Governance Data You Need Out of Your Power BI Gateways with PowerShell
View GatewayClusters.ps1
<# Make sure you have the modules installed.
You only need the ImportExcel module if you want to export the results to Excel files. #>
Install-Module DataGateway
Install-Module ImportExcel
<# Discovery #>
# All Gateway Clusters in your tenant.
Get-DataGatewayCluster -Scope Organization
# All nodes of all Gateway Clusters in your tenant.
@SQLvariant
SQLvariant / Import_HundredsOf_CSVs.PS1
Created Oct 10, 2019
Basic script to import every .CSV file from a directory and INSERT each one into it's own table in SQL Server. It will create the table if it doesn't exist.
View Import_HundredsOf_CSVs.PS1
dir -Filter *.csv |
foreach {
"$($_.Name)";
,(Import-Csv -Path $_.Name) |
Write-SqlTableData -ServerInstance localhost\SQL2017 -DatabaseName BlankDB -SchemaName dbo -TableName $_.BaseName -Force
}
@SQLvariant
SQLvariant / Export_PBIAuditLogs_toDailyJson.ps1
Last active Oct 3, 2019
Export Power BI Audit Logs to .JSON files
View Export_PBIAuditLogs_toDailyJson.ps1
90..1 |
foreach {
$Date = (((Get-Date).Date).AddDays(-$_))
$StartDate = (Get-Date -Date ($Date) -Format yyyy-MM-ddTHH:mm:ss)
$EndDate = (Get-Date -Date ((($Date).AddDays(1)).AddMilliseconds(-1)) -Format yyyy-MM-ddTHH:mm:ss)
Get-PowerBIActivityEvent -StartDateTime $StartDate -EndDateTime $EndDate -ResultType JsonString |
Out-File -FilePath "c:\temp\PowerBIAuditLogs\PowerBI_AudititLog_$(Get-Date -Date $Date -Format yyyyMMdd).json"
}
@SQLvariant
SQLvariant / Invoke-SqlNotebook.md
Last active Sep 23, 2019
3 different options for using the Invoke-SqlNotebook cmdlet
View Invoke-SqlNotebook.md

Use with Registered Servers

Use Registered Servers or Central Management Server to run Invoke-SqlNotebook against multiple servers.

$datetime = Get-Date -Format yyyyMMddhhmm

dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' |
WHERE { $_.Mode -ne 'd'} |            
foreach {
@SQLvariant
SQLvariant / ExportPBIServiceAssets-toCSV.PS1
Created Sep 20, 2019
Export information about Power BI tenant assets to Excel or CSV files
View ExportPBIServiceAssets-toCSV.PS1
<##################################################################################################>
<#
Export information about assets in the Power BI Tenant to individual CSV files.
#>
<##################################################################################################>
<#
NOTE: You have to be a Power BI Admin to use the -Scope Organization parameter option. #>
<# First, get all the capacities for the tenant, if they have any #>
Get-PowerBICapacity -Scope Organization |
@SQLvariant
SQLvariant / Export O365 PowerBI Audit Log.ps1
Created Sep 18, 2019
Exports the PowerBI events from the O365 Audit Log into separate .CSV files for each of the last 90 days.
View Export O365 PowerBI Audit Log.ps1
$UserCredential = Get-Credential 'your.email@somewhere.com'
90..1 |
foreach {
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session
$Start=((Get-Date).Date).AddDays(-$_);
Search-UnifiedAuditLog -StartDate $Start -EndDate $Start.AddDays(1) -RecordType PowerBI -ResultSize 5000 |
@SQLvariant
SQLvariant / PBI_ArgumentCompleters.PS1
Last active Sep 18, 2019
a few helpful Argument Completers for common Power BI commands, you can ad to your $profile
View PBI_ArgumentCompleters.PS1
<# We need an argument completer for -Workspace #>
Register-ArgumentCompleter -ParameterName Workspace -ScriptBlock {
Get-PowerBIWorkspace | ForEach-Object {
$CompletionText = $_.Name
New-Object System.Management.Automation.CompletionResult (
"(Get-PowerBIWorkspace -Name '$($CompletionText)')",
$_.Name,
'ParameterValue',
"$_ (PowerBIWorkspace)"
)
@SQLvariant
SQLvariant / ClonePowerBI_WorkspaceUsers.PS1
Last active Aug 31, 2019
Copy Users & Groups from one Power BI Workspace to another
View ClonePowerBI_WorkspaceUsers.PS1
<# Clone Users from one Workspace to another
Start by specifying the Target Workspace
Then use the Out-GridView cmdlet to choose the Source Workspce to copy the users & roles from.
https://powerbi.microsoft.com/en-us/blog/announcing-apis-and-powershell-cmdlets-for-power-bi-administrators/ #>
$TargetWorkspace = Get-PowerBIWorkspace -Name 'New QA Workspace';
(Get-PowerBIWorkspace |
Out-GridView -PassThru |
foreach { Get-PowerBIWorkspace -Id $_.Id -Scope Organization }).Users |
WHERE { $_.AccessRight -ne 'Viewer' } |
@SQLvariant
SQLvariant / CopyReport_PreserveDatasetConnection.ps1
Last active Sep 23, 2019
Promote a Power BI Report from a QA Workspace to a Prod Workspace, while preserving the connection to the Prod database when the updated report lands in the Prod Workspace.
View CopyReport_PreserveDatasetConnection.ps1
$source_workspace_ID = (Get-PowerBIWorkspace -Name 'QA-Workspace').Id
$report = Get-PowerBIReport -Name 'Regional Sales' -WorkspaceId $source_workspace_ID
$target_workspace_ID = (Get-PowerBIWorkspace -Name 'Prod-Workspace').Id
$targetReport = Get-PowerBIReport -Name 'Regional Sales' -WorkspaceId $target_workspace_ID
Copy-PowerBIReport -Report $report -WorkspaceId $source_workspace_ID -TargetWorkspaceId $target_workspace_ID -TargetDatasetId ($targetReport).DatasetId -OutVariable NewReport
<# Now to remove the old version of the report in the Prod workspace #>
#$url = 'https://api.powerbi.com/v1.0/myorg/groups/' + $target_workspace_ID + '/reports/' + $targetReport.Id
#Invoke-PowerBIRestMethod -Method Delete -Url $url
@SQLvariant
SQLvariant / Profile.PS1
Created Jun 25, 2019
The basic commands I always put in all of my PowerShell $Profile.CurrentUserAllHosts
View Profile.PS1
Import-Module SqlServer;
cd C:\temp;
<# First we need an argument completer for -ServerInstance #>
Register-ArgumentCompleter -ParameterName ServerInstance -ScriptBlock {
(dir -Recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ |
?{ $_.Mode -ne 'd'} |
Group-Object ServerName).Name | ForEach-Object {
$CompletionText = $_
New-Object System.Management.Automation.CompletionResult (
$CompletionText,
You can’t perform that action at this time.