Power BI Connector

On this page

Introduction

The Service Desk Power BI Connector lets you incrementally import incidents and different asset records from Service Desk to Microsoft Power BI. The import provides raw data on the topics most relevant to your organization. You can then review and analyze the data by viewing it in a variety of formats, for example, in tables, graphs, and charts.Some customers will also find the Power BI dashboard useful.

SolarWinds owns the connector and is a member of the Microsoft Power Query Connector Certification Program. If you have questions regarding the content of this article or want to request a change to it, provide feedback at the bottom of the page.

Connect to Service Desk

Current version of Power BI Connector: V113

Instructions

Prerequisites: You must have (1) the latest version of Power BI Desktop and (2) a Service Desk user who has set up token authentication for API integration.

In Power BI Desktop, select Get Data from Home. Select SolarWinds Service Desk, and then select Connect.
Sign in with the JSON Web Token you generated as described in the Prerequisites, and then click Connect to verify your access to Service Desk.

If you have an ESM account, the JSON token you use for the connector must be generated by a Service Provider Administrator.
In the Navigator dialog box, select the table you want to import, and then click Load.

After completion of the steps above, you can see your chosen models load under Data view. The records you just imported were updated or created in Service Desk during last month.

Additional steps for previous users of the BETA connector

If you previously used the BETA Service Desk Power BI Connector to create reports, and you already have the model name Incidents and reports based on it, and you wish to connect those reports with the new out-of-the-box version:

Go to the Incidents model, and right-click Edit query. A Power Query Editor opens.
Select the Incidents query and open Advanced Editor.

You see the following lines:

Source = SolarWindsServiceDesk.Contents(),

Incidents1 = Source{[Name="Incidents"]}[Data]
Replace those lines with the following lines:

Source = SolarWindsServiceDesk.ContentsV113(null, null, null),

#"All Objects" = Source{[Name="All Objects"]}[Data],

Incidents1 = #"All Objects"{[Name="Incidents"]}[Data]
Click Done.
Click Close & Apply.

The reports display data from the past month instead of the last two years. Going forward, those reports are connected to the new Service Desk Power BI Connector.

Set the Date range by using RangeStart and RangeEnd parameters

On the Home menu bar, click Transform Data. A Power Query Editor opens.
From the Power Query Editor toolbar, select Home > Manage Parameters > New Parameter.

Create two new DateTime parameters named RangeEnd and RangeStart.

From the DateTime parameters, the connector ignores the Time, and uses only the Date.

The new parameters set the filtering range of the pulled data, based on the updated_at column. This reduces download time and filters your data based on the range you set.

The updated_at column contains the last time the record was updated in Service Desk in the Server TimeZone. This is not the same as what is shown in the UI; there it is based on the Account TimeZone setting. Because of this difference, when filtering the index, you might see a gap between the information there and what you see in a Power BI report.

The filtering range provided by adding the parameters is record.updated_at >= RangeStart(Date) and record.updated_at < RangeEnd(Date).

Notice the difference between less than RangeEnd and larger than and equals RangeStart.

Set enough time between the parameters to allow you to pull sufficient records for your reports, but not too large. Select a range that won't cause you to wait a long time for every refresh. See Microsoft's instructions: create-parameters.

Example

To pull records that were updated during a one-month period between Nov 10, 2023 and Dec 10, 2023 (not included), you would use:

RangeStart = 11/10/2023 12:00 AM

RangeEnd = 12/10/2023 12:00 AM

The filtering range provided by adding those parameters is record.updated_at >= Nov 10, 2023 and record.updated_at < Dec 10, 2023.

In this example, records that were updated by Dec 10, 2023 will not be pulled.

For each query you wish to set with Date range, open Advanced Editor.

The following line displays:

Source = SolarWindsServiceDesk.ContentsV113(null, null, null),
Replace the line with the following:

Source = SolarWindsServiceDesk.ContentsV113(RangeStart, RangeEnd, null),

The third parameter relates to custom fields.
Click Done.
Click Close & Apply.
Go to Data view.

Incremental Refresh

Incremental Refresh saves download time after your initial download is performed.

Prerequisite: Set the Date range by using RangeStart and RangeEnd parameters.

For each model you’ve downloaded, define the policy for Incremental refresh and real-time data as described below.

Set the option for archiving data to meet your reporting needs. Enter a number in the first field and use the dropdown to select a time frame.
Incrementally refresh data starting 1 Days before the refresh date. See define-policy for instructions.

Optional: Add custom fields to Power BI models

To include custom fields from Service Desk in your Power BI models:

Navigate to the Home tab.
On the Home menu bar, click Transform Data. A Power Query Editor opens.
From the Power Query Editor toolbar, select Home > Manage Parameters > New Parameter.
Create new parameter with the following:
- Name: CustomFieldName
- Type: Select Text from the dropdown menu
- Suggested Values: Any value
- Current value: Specify the custom fields you want to add to model.
  - Enter the custom field names exactly as they appear in Service Desk (be sure to use the custom field name; not its ID).
  - To add multiple custom fields, separate them with commas.
Click OK in the lower right.
In Power Query Editor select the Incidents query, then navigate to Home > Advanced Editor.
- For those who set the RangeStart and RangeEnd parameters , the first line may appear as follows:
  
  Source = SolarWindsServiceDesk.ContentsV113(RangeStart, RangeEnd, null),
- For those who have not set the RangeStart and RangeEnd parameters, the first line should appear as follows:
  
  Source = SolarWindsServiceDesk.ContentsV113(null, null, null),
Replace the third null parameter with parameter name you set in Step 4 of this process.

Source = SolarWindsServiceDesk.ContentsV113(null, null, CustomFieldNames),
Click Done in the lower right.
From the Power Query Editor, navigate to File and select Close & Apply.
Using the example above, in the Data View expand Incidents model. See that in addition to the standard incident fields, you can also see the custom fields specified in the CustomFieldNames parameter. This allows you to integrate custom fields in your reports much more easily.
To add custom fields on other models (for example, Hardwares, Softwares, Other Assets, and others) repeat the entire process for each model individually.

For models containing different custom fields, you should create a new parameter for each model (for example, for the model named mobiles, create a new parameter name MobileCustomFieldNames).

Publish reports

All customers publishing to the Power BI Service need to perform the steps below. This applies to both first-time publishers and those who have published in the past.

Publish the report to Power BI Service Workspace, based on the out-of-the-box connector.
Establish settings for each published semantic model in the Power BI Service.
For each semantic model, open the Data Source Credentials section and edit the credentials with the key and token you used when you established the connection with the connector in Desktop Power BI.

Your reports in Power BI Desktop and Power BI Service are now connected to and using the latest version of Service Desk Connector.

You can refresh each semantic model for the first time. The refresh downloads the archived data you defined in the incremental refresh policy (records from a maximum of 2 years ago).

SolarWinds suggests setting the first large download with a scheduled refresh. Optionally, you can run it manually by clicking Refresh the semantic model.

For instructions on configuring a scheduled refresh see scheduled-refresh.

After the first refresh, future refreshes go much more quickly and download only the new records from Service Desk.

Limitations and issues

Users should be aware of the following limitations and issues associated with accessing Service Desk:

There is no limit on the number of users who can pull data, but if they are not using Incremental Refresh and try to import data from a range larger than one year, each user can refresh only once every 24 hours.
The connector is not a live connector. To get the most updated data, you need to refresh (scheduled or manually).
The only way to pull data beyond two years is by using Incremental Refresh.
The fields that are imported are limited. For additional fields please submit a feature request in THWACK. For feedback or support please reach SolarWinds support.

Credential error in the Navigator

If a credential error occurs in the Navigator, clear your recent data source settings.

In Power BI Desktop, select File > Data source settings.
Select the Service Desk data source, and then select Clear permissions.
Establish the connection to the Navigator again.

Fields available in Power BI Connector

Many new fields became available on February 12, 2024. If you were previously a user of the BETA version of the Power BI Connector, we recommend that you look through the fields to see all the different information you can collect using the Connector now.

Incident fields

Field name	UI field name	Type/size	Description	Format
Id	API Only	Integer	Incident's unique ID in the system	int
number	Number	Integer	Incident's unique number in the system	int
name	Title	String	The name of the incident	string
description_no_html	Description	String	The description of the incident	string or null
state	State	String	The state of the incident	string
priority	Priority	String	The priority of the incident. The default is High/Medium/Critical, Low, and None	string
category_name	Category	String	The category the incident is related to	string
subcategory_name	Subcategory	String	The subcategory the incident is related to	string
assignee_name	Assigned To	String	The name of the assignee for the incident	string
assignee_email	Assigned To	String	The email of the assignee for the incident	string <email>
requester_name	Requester	String	The name of the requester for the incident	string
requester_email	Requester	String	The email of the requester for the incident	string <email>
created_by_name	Requester	String	The name of the creator of the incident	string
created_by_email	Requester	String	The email of the creator of the incident	string <email>
created_at	Created At	Date time	The date and time the incident was created	date time
updated_at	Updated At	Date time	The date and time the incident was last updated	date time
resolved_by_name	Resolution	String	The name of the person who resolved the incident	string
resolved_by_email	Resolution	String	The email of the person who resolved the incident	string <email>
resolved_at	Resolution	Date time	The date and time the incident was resolved	date time
is_service_request	Icon in the title of the incident	Boolean	Identifies whether or not the incident was created from a service request	boolean
currency	Currency	String	Currency of the service request	string
resolution_code	Resolution	String	Resolution type provided when the incident is resolved	string
resolution_description		String	Description provided when an incident is resolved	string
origin	Incident origin	String	Where the incident was created from: Web/Email/Portal/API etc	string
cc	CC	String	Identifies whether a person is included in the CC in each action on an incident	strings array
			Custom fields related to the incident
due_at	Due at	Date time	Due date and time for the incident	date time
site_name	Site	String	The site name	string
department_name	Department	String	The department name	string
price	Price	Integer	The price in the service request	int
sla_violations			Statistics that represent SLA (service-level agreement) rules that failed for the incident.	array of objects
	Tags	String	List of tags related to the incident	array of strings
to_assignment		String	Time passed until the incident was assigned to
to_first_response_business_time		String	Time passed until the incident has it first response
to_resolve_business_time		String	Time passed until the incident was resolved
Closed At	Closed At	Datetime	The date and time the incident was closed	datetime
Customer Satisfaction Feedback	Customer Satisfaction Feedback	string	Customer satisfaction survey string	string
Customer Satisfied	Customer Satisfied	Satisfied or not? / string?	The subcategory the incident is related to	string
changes	Related changes	array of integers	changes related IDs list	array of integers
Incident origin	Incident source	string	Incident origin: Portal/ System/Web/ Email/API/Mobile/ Salesforce/Scheduled/ Chat/Slack	string
Tasks	Tasks related	Array of integers	Tasks related IDs list	array of integers
Mobiles	Mobile related	List of integers	Mobiles related IDs list	array of integers
Other_assets	Other assets related	List of integers	Other assets related IDs list	array of integers
Configuration_items	Configuration items related	List of integers	Configuration items related IDs list	array of integers
Discovery_hardwares	Discovery hardware IDs related	List of integers	Discovery hardwares IDs	array of integers
Purchase_orders	Purchase order IDs	List of integers	Purchase orders related IDs list	array of integers
Problems	Problem IDs list	List of integers	Problem IDs list	array of integers
Time_tracks		List of integers	Incident time tracks	array of integers

Assets fields by type

Computer fields

Field name	UI field name	Type/size	Description	Format
id	API Only	Integer	Asset's unique ID in the system	integer
name	Name	String	Computer host name	string
description	Description	String	Computer description	string
IP address		String	Computer IP address	string
external IP	External IP address (Hardware tab)		Computer external IP	string
MAC_addrress	MAC address	String	Computer MAC address	string
category	Category	String	Category name of the computer	string
status	Status	String	Status of the computer	string
operating_system	Operating system	String	Operating system installed on the computer	string
operating_system_version	Operating system version	Integer	Operating system number	integer
service_pack	Service pack (assets tab, computer details)	String	Service pack number	string
created_at	Created At (index table)	Date Time	The time the computer record was created	date time
updated_at	Last update (index table)	Date Time	The time the computer was last updated manually or by the agent/scanner	date time
detected_at	Last update	Date time	When the computer was detected	date time
serial_number	Serial number (Hardware tab)	String	Serial number of the computer	string
model	Model (Hardware tab)	String	Computer model	string
CPU	CPU	Processor type	Computer CPU	string
tag	Tag	String	Computer tag	string
processor_speed	CPU speed (Hardware tab)	Double		Double MHz
number_of_processors	Number of CPUs (Hardware tab)	Integer		Integer
domain				string
site	Site	String	Site name	string
department	Department	String	Department name	string
username	User	String
asset_tag				string
memory	Physical memory			Int (MB)
swap	Swap (Hardware tab)			String (MB)
origin	Scan origin			array string
active_directory	Active Directory		Active directory field	string
Warranty data {Status service provider start_date end_date last_updated}	Warranty information (Lifecycle tab)		Warranty API
custom fields {name value Type}			The list of custom fields name, value, & Type	strings array [{ key(string): value(string), key(string): value(string) }] String String String

Network device fields

Field name	UI field name	API/DB name	Type/size	Description	Format
id	API only		Integer	Asset's unique ID	integer
host_name	Name		String	Network device name	string
IP_address	IP Address		String		string
MAC_address	MAC Address		String		string
operating_system	Operating System		String		string
operating_system_version			Integer		integer
created_at	Created At		Date time	Time the mobile device record was created	date time
updated_at	Last Update		Date time	Time the mobile device was last updated manually or by the agent/scanner	date time
cpu	CPU	processor type	String		string
service_pack		OS service pack	String	OS service pack	string
domain	Domain		String		string
active_directory			String		string
number_of_processes			Integer		integer
processor_speed			Integer		integer
memory	Memory		Integer		integer
swap	Swap		Integer		integer
state			String		string
type	Device Type		String		string
origin	Scan origin		array of strings		array of strings
site	Site: name		String	Site name	string
Discovered Ports {number protocol usage availability}				List of discovered ports

Mobile fields

Field name	UI field name	API	Type/size	Description	Format
id	API Only		Integer	Asset's unique ID in the system	integer
created_at	Created At (index table)		Date Time	The time the computer record was created	date time
updated_at	Last update (index table)		Date Time	The time the computer was last updated manually or by the agent/scanner	date time
status	Status			Status of the mobile device	string
reporting_status	Reporting status	Reporting Status (in the index table)	String		string
device_type	Device Type		String	Category name of the mobile device	string
department_name	Department: name		String	Department name	string
manufacture	Manufacturer		String		string
model	Model		String		string
service_provider	Service provider		String	Android/IOS
company_issued	Company issued		Boolean		boolean
serial_number	Serial number		String	Serial number of mobile device	string
imei	IMEI		Double	IMEI for mobile device	double
technial_contant			String		string
site	Site		String	Site name	string
username	Owner		String	Mobile device owner	string
last_report	Last report		Date Time		date time
phone_number	Phone number		String	Phone number	string
tags	Tags	Tags: name	List of strings	Lists of Tags names	list of strings
custom_fields_values			Array (List) of strings	Custom fields that are related to this incident	strings array [{ key(string): value(string), key(string): value(string) }]

Other Assets fields

Field name	UI field name	API/DB	Type/size	Description	Format
id	API only	Asset_ID	Integer	Asset's unique ID in the system	integer
name	Name	asset type: name	String	Asset's name	string
owner_name		Owner: name	String	Owner name	string
username	User		String		string
status	Status	Status: name	String	Status of the mobile device? other asset?	string
category_name	Category	Category: name	String	Category name of the mobile device? other asset?	string
department_name	Department	Department: name	String	Department name	string
created_at	Created At		Date Time	Time that the other asset record was create	date time
Updated_at	Updated At		Date Time	Time the other assets was last updated	date time
ip_address	IP Address		String	IP address	string
serial_number	Serial number		String	Serial number	string
tags	Tags	Tags: name	Lists of strings	Lists of Tags names of the other asset	list of strings
lifecycle_type	Lifecycle Type		String	Options: Assigned / Spare - Returned / duplicate / Disposed	string
maitenance_contract	Maintenance contract		String		string
model	Model		String	Other assets model	string
site_name		Site: name	String	Site name	string
manufacturer	Manufacturer		String		string
custom_fields_values	Custom field Name Custome field Value Custome field Type		Array (List) of Objects [{ key(string): value(string), key(string): value(string) }]	Custom fields that are related to this incident	strings array [{ key(string): value(string), key(string): value(string) }]

Printer fields

Field name	UI field name	Type/size	Description	Format
id	API only	Integer	Printer's unique ID in the system	integer
created_at	Created At (index table)	Date Time	The time the printer record was created	date time
updated_at	Last update (index table)	Date time	The time the printer was last updated manually or by the agent/scanner	date time
driver		String
port		Integer	Printer port	integer
shared		String
address		String
site	Site	String	Site name	string
department	Department	String	Department name	string
technical_contact		String	Name of the technical contact
installations		String	List of the computers that the printer is installed in

Software fields

Field name	UI field name	Type/size	Description	Format
id	API only	Integer	Software's unique ID in the system	integer
name	Name	String	Software name	string
category		String	Category name	string
vendor_name		String	Vendor name	string
installs		Integer	Number of computers installed	integer
version		Integer		integer
tag		String		string
hidden
created_at	Created At (index table)	Date Time	The time the printer record was created	date time
updated_at	Last update (index table)	Date Tme	The time the printer was last updated manually or by the agent/scanner	date time
first_detected	First detected	Date Time		date time
windows_system_componetnt		Boolean		boolean

Search SolarWinds Support