How-to configure PowerShell Report

Introduction

In some cases it’s not possible to use the existing reports as the data needed is not accessible by the reporting Engine or there are no data providers for it. For example you might want to load additional data for a ressource from Active Directory or similar. In this case, it is possible to configure a customer specific CSV report via a PowerShell script. The report can have certain input parameters that can be used in the PowerShell script and can be executed localy or via remote PowerShell.

The report can be executed in the Admin UI under Reports in the menu item Reporting. Upon execution, the configured PowerShell script will be executed and the result will be presented to the user as a downloadable CSV file.

Step 1 - Create a PowerShell Script

First of all a suitable script must be created that loads the necessary data (from various sources) and outputs it in the correct format. You can use input variables, which are replaced during execution. The name of this input variable must be registered in the database in the corresponding table (see Step 2).

Output

The script must output a string in CSV format.

Example A

$output = "val11,val12,REPLACEMENT_TEST_VARIABLE
val21,val22,val23"
return $output

In this example the variable REPLACEMENT_TEST_VARIABLE is replaced according to the input when the report is executed.

The CSV would look like this:

val11,val12,10
val21,val22,val23

Example B

$output = "Rolename;ID`r`nEmployee Default Rights;1`r`nSupplier Default Rights;2"
Write-Output $output

The CSV would look like this:

Step 2 - Register the PowerShell Script

In order for the PowerShell report to be visible in the Admin UI, it must be registered in the database. Depending on whether input variables are used or not it must be registered in 3 tables or in 1 table.

PowerShell Report without input variables

servicedmcore_reporting_powershell_report

PowerShell Report with input variables

servicedmcore_reporting_powershell_report
servicedmcore_reporting_powershell_input_type
servicedmcore_reporting_powershell_input_parameter;

Properties

servicedmcore_reporting_powershell_report

Property	Mandatory	Type	Description

Property	Mandatory	Type	Description
`name`		varchar(255)	Name of the report (is not used in the Admin UI as display name of the report, but the name_key is used instead)
`description`	-	mediumtext	Description of the report
`name_key`	()	varchar(255)	Name key of the report for translation (is used in the Admin UI as display name of the report)
`description_key`	-	varchar(255)	Description key of the report for translation
`powershell_modules`	-	mediumtext
`powershell_snapins`	-	mediumtext
`powershell_shell_uri`	-	varchar(255)	RemotePowershell ShellUri
`powershell_application_name`	-	varchar(255)	RemotePowershell ApplicationName
`powershell_use_ssl`	-	bit(1)	RemotePowershell UseSsl
`powershell_port`	-	int(11)	RemotePowershell Port
`powershell_computer_name`	-	varchar(255)	RemotePowershell Computername
`powershell_username`	-	varchar(255)	RemotePowershell Username
`powershell_user_password`	-	varchar(255)	RemotePowershell Password
`powershell_script_file_path`		mediumtext	Path of the script file
`script_timeout_in_minutes`	-	int(10) unsigned
`id`		int(10) unsigned	Id of the entry
`target_network_id`		int(10) unsigned	Id of the target network where the script is located
`powershell_authentication_mechanism_id`	-	int(10) unsigned

servicedmcore_reporting_powershell_input_type

Property	Mandatory	Type	Description

Property	Mandatory	Type	Description
id		int(10) unsigned	Id of the entry
name		varchar(255)	Name of the input parameter (is used in the Admin UI, when selecting the corresponding report)
input_type		varchar(255)	Entity type of the input parameter For example: If you want a role as input parameter for the report you can put `Role` in this column. In the admin ui you can than choose a role in the drop down and the id of the role will be used as replacement of the input variable defined in the table `moving_appcustomer_900000.servicedmcore_reporting_powershell_input_parameter`. Other examples: CoreIdentity → Core Identity ID ResourceIdentifier → Resource identifier Application → Application ID
ui_selector	-	varchar(255)	?

servicedmcore_reporting_powershell_input_parameter

Property	Mandatory	Type	Description

Property	Mandatory	Type	Description
id		int(10) unsigned	Id of the entry
report_id		int(10) unsigned	Reference id of the PowerShell report (`servicedmcore_reporting_powershell_report`)
input_type_id		int(10) unsigned	Reference id of the input type (`servicedmcore_reporting_powershell_input_type`)
replacement_name	-	varchar(255)	Name of the replacement variable in the PowerShell script