Add data into SQL table using PowerShell scripts

[aakif_aslam]

Hello to all,

There is a request from the client from an audit point of view, to get all the

1. Workspace details
2. Report name 
3. Users with their email ID and Access shared

Currently, I'm able to get Workspace Name, Workspace ID, and User details. Unable to get the Report name within the same script I'm using a different script and getting an extract in Excel.

Is there any way to add the data directly into a SQL table from PowerShell and also automate this process to run daily?

PLEASE HELP.

@amitchandak 

@lbendlin 

@Greg_Deckler 

@Sahir_Maharaj 

[Adamboer]

Yes, it is possible to add the data directly into a SQL table from PowerShell and automate the process to run daily. Here are the steps you can follow:

1. Install the SQL Server PowerShell module on your computer. This module provides cmdlets for working with SQL Server databases, including the ability to insert data into a table.

2. Create a SQL Server table with columns for Workspace Name, Workspace ID, Report Name, User Email, and Access. You can use SQL Server Management Studio to create the table and set up the appropriate columns and data types.

3. Modify your PowerShell script to include a SQL insert statement that adds the Workspace details, Report name, User email, and Access to the table. You can use the Invoke-Sqlcmd cmdlet from the SQL Server PowerShell module to execute the insert statement.

4. Test the PowerShell script to ensure that it successfully inserts the data into the SQL table.

5. Set up a task scheduler or other automation tool to run the PowerShell script daily. This will ensure that the data is updated in the SQL table on a regular basis.

By following these steps, you can automate the process of adding Workspace details, Report name, User email, and Access to a SQL table from PowerShell, and ensure that the data is up to date for auditing purposes.

[aakif_aslam]

HI @Adamboer,

Thanks for your reply,

Here's the Powershell script -

Get-PowerBIWorkspace -Scope Organization -All |
ForEach-Object {
$Workspace = $_.name
$WorkspaceId = $_.Id
foreach ($User in $_.Users) {
[PSCustomObject]@{
Workspace = $Workspace
WorkspaceId = $WorkspaceId
Access = $User.accessright
Identifier =$user.Identifier}}} 

Right now I don't have the expertise to add result as an output in the SQL table, so I thought of getting the data in a CSV file.

But here the issue is after using the task scheduler the script runs successfully but it does not generate the output CSV file and if I execute the script in Powershell manually it works fine.

Can you share some inputs.