Home Analyst Portal

Accessing ServiceManager data base with Dashboard query

Olena_PrychynaOlena_Prychyna Customer IT Monkey ✭

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

Best Answer

Answers

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    Does the same query produce results in SQL Management Studio?
  • Olena_PrychynaOlena_Prychyna Customer IT Monkey ✭

    Yes, in SQL Management Studio we see the results.

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

  • Jeff_LangJeff_Lang Customer Advanced IT Monkey ✭✭✭
    @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_PrychynaOlena_Prychyna Customer IT Monkey ✭
    @Jeff_Lang We need the query to work. Want to use GetDashboardDataById API
  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    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_PrychynaOlena_Prychyna Customer IT Monkey ✭

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

    No results returned.

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    So weird...
    If you do a query for "select workitemid, title from workitem" against ServiceManagement does that work?
  • Olena_PrychynaOlena_Prychyna Customer IT Monkey ✭
    Yes. It works. Any query on table in ServiceManagement against ServiceManagement datasource - works.
  • Jeff_LangJeff_Lang Customer Advanced IT Monkey ✭✭✭
    @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_PrychynaOlena_Prychyna Customer IT Monkey ✭

    @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.

  • Olena_PrychynaOlena_Prychyna Customer IT Monkey ✭
    edited January 2019

    @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_RossGeoff_Ross Cireson Consultant Super IT Monkey ✭✭✭✭✭
    @Olena_Prychyna
    Great news. Happy Dashboarding!
Sign In or Register to comment.