Accessing ServiceManager data base with Dashboard query

Olena_Prychyna

We are trying to create a dashboard query based on ServiceManager datasource.

Simple query does not return any result:

 

SELECT wi.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as [Id]

From MTV_System$WorkItem$Incident  wi

where  wi.IsParent_8C8C5710_9D59_DE9D_7846_A5BAB8744F0E =1

 

Same (no results) if we base it on ServiceManagement data source:

 

SELECT wi.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as [Id]

From [ServiceManager].[dbo].[MTV_System$WorkItem$Incident]  wi

where  wi.IsParent_8C8C5710_9D59_DE9D_7846_A5BAB8744F0E =1

 

What do we do wrong?

 

Our environment:

Windows 10

System Center 2016

Version: 7.5.7487.0

Current Portal Version: 8.7.0.2016 (client - IE11)

Management Pack Version: 7.7.2016.185

Geoff_Ross

Does the AppPool account running the Cireson Portal have permissions to read from ServiceManager DB. This isn't needed for any other portal function so its quite possible this was missed. 
To test, launch Management Studio with the App Pool account and see if you can run the query.

Justin_Workman

Does the same query produce results in SQL Management Studio?

Olena_Prychyna

Yes, in SQL Management Studio we see the results.

We don't have Analytics installed. Do we need it?

Jeff_Lang

@Olena_Prychyna we've tried to do something similar before and never had it work, we got around it by creating a view in the ServiceManagement Database which performed the selects on the ServiceManager Database. then when calling these views from a dashboard query it did return the expected results

Olena_Prychyna

@Jeff_Lang We need the query to work. Want to use GetDashboardDataById API

Justin_Workman

When you select ServiceManager as the data source that should work with the initial query you've shown.  You might try adding a new datasource for your ServiceManager database and try the query against that.  Maybe the connection string is different.  The entries for the data sources are in the DataSourceConfiguration table, but the connection string is obfuscated so there's no way to know if maybe the connection string isn't quite right.  But you can add a new datasource in Admin Settings/Data Source Settings.  

Olena_Prychyna

@Justin_Workman : Created new datasource (by using .udl file on portal server). Tried query against it.

No results returned.

Justin_Workman

So weird...
If you do a query for "select workitemid, title from workitem" against ServiceManagement does that work?

Olena_Prychyna

Yes. It works. Any query on table in ServiceManagement against ServiceManagement datasource - works.

Jeff_Lang

@Olena_Prychyna we specifically added the views in the ServiceManagement Database so that we could use the GetDashboardDataById.

as an example on one query we needed to get the actual enums from Service Manager so that they were completely up to date instead of having to wait for them to be cached to the ServiceManagement Database and having an AppPool restart to use in an ARO.

We added the following View (Called DPTI_EnumList) to the ServiceManagement Database

SELECT        ServiceManager.dbo.EnumType.EnumTypeId, ServiceManager.dbo.EnumType.EnumTypeName, ServiceManager.dbo.EnumType.ParentEnumTypeId, ServiceManager.dbo.LocalizedText.LTValue AS DisplayString<br>FROM            ServiceManager.dbo.EnumType INNER JOIN<br>                         ServiceManager.dbo.LocalizedText ON ServiceManager.dbo.EnumType.EnumTypeId = ServiceManager.dbo.LocalizedText.LTStringId<br>WHERE        (ServiceManager.dbo.LocalizedText.LanguageCode = 'ENU') AND (ServiceManager.dbo.LocalizedText.LTStringType = 1) AND (ServiceManager.dbo.EnumType.Enabled = 1)

and for one of the dashboard queries  using the Service Management Database to get the Asset Management Manufacturer Enum list.

SELECT *<br>FROM DPTI_EnumList<br>-- MANUFACTURER<br>WHERE ParentEnumTypeId = '0e82dad7-5853-33f7-e4c0-c34c478fe70a'

we call GetDashboardDataById. in some custom code, the query calls the Created View from the ServiceManagement DB, and the view gets it's data from the ServiceManager DB.

The above is just one example of how we are doing it.

Olena_Prychyna

@Jeff_Lang : as you suggested, I created view in Service Management database and then created a dashboard query SELECT * FROM myview. Built Dashboard view in portal - no records showing.

I also tried to query system view -  no results.

When query any table - records are showing fine.

Geoff_Ross

Does the AppPool account running the Cireson Portal have permissions to read from ServiceManager DB. This isn't needed for any other portal function so its quite possible this was missed. 
To test, launch Management Studio with the App Pool account and see if you can run the query.

Olena_Prychyna

@Geoff_Ross: That's it! I added AppPool account as User to ServiceManager database and it fixed the problem

Thank you for your help.

Geoff_Ross

@Olena_Prychyna
Great news. Happy Dashboarding!