locked
How to get SQL Performance Data from DWH? RRS feed

  • Question

  • Hey

    I wanna create a report with the performance data of a SQL Server, now I saw, that there are only one report in the new SQL Management Pack (Database Files Space Usage Forecast). How can I get reports of the remaining performance counters? I want check the User Connections of the last 2 Weeks, but in the performance View I can only see the data of one week.

    Or did I forgot to import a MP, and there should be more than only one Report in the new MP?

    Kind Regards 
    Stately 

    Wednesday, March 25, 2020 12:30 PM

All replies

  • Hi,

    SCOM only keeps data in the OperationsManager database for one week (7 days), this is what you will see in the Operations Console, this is due to performance reasons. To view older data you need to use Operations Manager reporting.

    You should have the following SQL Server reports from the SQL Server management packs:

    If you're using the following management pack: Microsoft System Center Management Pack for SQL Server, the management pack will include the following files:

    • Microsoft.SQLServer.Core.Library.mpb
    • Microsoft.SQLServer.Core.Views.mp
    • Microsoft.SQLServer.Core.WebDashboards.mp
    • Microsoft.SQLServer.IS.Windows.mpb
    • Microsoft.SQLServer.IS.Windows.Views.mp
    • Microsoft.SQLServer.Visualization.Library.mpb
    • Microsoft.SQLServer.Linux.Views.mp
    • Microsoft.SQLServer.Linux.Discovery.mpb
    • Microsoft.SQLServer.Linux.Monitoring.mpb
    • Microsoft.SQLServer.Windows.Views.mpb
    • Microsoft.SQLServer.Windows.Discovery.mpb
    • Microsoft.SQLServer.Windows.Monitoring.mpb

    The report templates are included in the bolded management pack above.

    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    Wednesday, March 25, 2020 12:54 PM
  • Hi Leon

    The Microsoft.SQLServer.Windows.Monitoring.mpb is imported in my SCOM envoirement. 

    

    But I dont see the Reports, I have only the following SQL report-folders:

    

    In the SQL  Server 2008 (Monitoring i have the Reports for User Connections. But in the Micrsofot SQL Server on Windows (Views), I have only the following Report:


    • Edited by StatelyElf Wednesday, March 25, 2020 1:14 PM
    Wednesday, March 25, 2020 1:14 PM
  • It might take a little while before the reports are populated, please also try clearing the cache of the Operations Console (How and When to Clear the Cache).

    If it still doesn't help after clearing the cache and waiting for about 10-15 minutes, check the Operations Manager event log for any warnings / errors that may have appeared after that you've imported the SQL Server management pack.


    Blog: https://thesystemcenterblog.com LinkedIn:

    Wednesday, March 25, 2020 1:24 PM
  • I imported the Management 2 months ago, and i cleaned the cache two times since then. I don't have the logs from then. 

    I have two SCOM Envoirments, and in both i can't find the reports. Is there a way to reimport the MP?

    Wednesday, March 25, 2020 2:25 PM
  • Remove the management pack, and re-import it.

    Blog: https://thesystemcenterblog.com LinkedIn:

    Wednesday, March 25, 2020 2:27 PM
  • I tired it, and got two Errors and one Warning.

    Event 31400:

    An exception occured processing a group membership rule. The rule will be unloaded.
     Subscription ID: 9b01fde0-8c2f-490b-8a12-5806c4c5c8e4
     Rule ID: a0210cc1-e9a0-7e43-9697-02102dc093e9
     Group ID: f774e8ae-3ed5-849b-204a-23f6b1e8bc40
     Group type name: UINameSpacecfb9e1dff3434e708f669c7735071fbb.Group
     Exception: Microsoft.EnterpriseManagement.Common.DataAccessLayerException: Invalid property name: _SCOMSystemStatus_60943BE9_FB06_150C_B271_2ADEDF5FC5E4
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.GetColumnDefinitionForProperty(String propertyName, QueryDefinition queryDefinition, IList`1 projectedColumnDefinitions)
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.ResolveColumnForProperty(ICriteriaContext criteriaContext, XmlReader criteriaReader, Nullable`1 typeContextId, IList`1 projectedColumnDefinitions)
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.ParsePredicateWithProperty(ICriteriaContext criteriaContext, Nullable`1 typeContextId, XmlReader criteriaReader)
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.ParsePredicate(ICriteriaContext criteriaContext, Nullable`1 typeContextId, XmlReader criteriaReader)
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.ParseCriteria(ICriteriaContext criteriaContext, Nullable`1 managedTypeIdContext, XmlReader criteriaReader)
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.CreateCriteriaTextAndParameters(ICriteriaContext criteriaContext, XmlReader criteriaReader, XmlNamespaceManager xmlNamespaceManager)
       at Microsoft.EnterpriseManagement.DataAccessLayer.SqlCommandBuilder.CreateWhereClauseFromCriteria(ICriteriaContext criteriaResolver, String criteriaXml, IList`1 internalColumnDefinitions)
       at Microsoft.EnterpriseManagement.DataAccessLayer.QueryRequest.CreateSqlCommandForSelectType()
       at Microsoft.EnterpriseManagement.DataAccessLayer.QueryRequest.Execute(SqlNotificationRequest sqlNotificationRequest)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.QueryGenerator.ExecuteSnapshotQuery(MembershipRule membershipRule, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.ExpressionEvaluatorForSnapshot.EligibleBySnapshotResults(MembershipRule membershipRule, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.ExpressionEvaluatorForSnapshot.GetRelationshipChangesForSnapshot(MembershipRule membershipRule, Guid groupInstanceId, Guid groupTypeId, Guid relationshipId, IList`1 groupKeyNameValuePairs, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.MembershipRuleEvaluator.EvaluateSnapshot(MembershipSubscription subscription, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.MembershipRuleEvaluator.EvaluateDifference(MembershipSubscription membershipSubscription, List`1 membershipChanges, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.MembershipRuleEvaluator.EvaluateDifference(MembershipSubscription subscription, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.MembershipCalculationManager.SnapshotCalculation(MembershipSubscription membershipSubscription, DatabaseConnection databaseConnection)

    Event 4502:

    A module of type "Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.GroupCalculationModule" reported an exception Microsoft.EnterpriseManagement.Common.DataAccessLayerException: Invalid property name: _SCOMSystemStatus_60943BE9_FB06_150C_B271_2ADEDF5FC5E4
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.GetColumnDefinitionForProperty(String propertyName, QueryDefinition queryDefinition, IList`1 projectedColumnDefinitions)
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.ResolveColumnForProperty(ICriteriaContext criteriaContext, XmlReader criteriaReader, Nullable`1 typeContextId, IList`1 projectedColumnDefinitions)
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.ParsePredicateWithProperty(ICriteriaContext criteriaContext, Nullable`1 typeContextId, XmlReader criteriaReader)
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.ParsePredicate(ICriteriaContext criteriaContext, Nullable`1 typeContextId, XmlReader criteriaReader)
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.ParseCriteria(ICriteriaContext criteriaContext, Nullable`1 managedTypeIdContext, XmlReader criteriaReader)
       at Microsoft.EnterpriseManagement.DataAccessLayer.ParameterizedCriteriaBuilder.CreateCriteriaTextAndParameters(ICriteriaContext criteriaContext, XmlReader criteriaReader, XmlNamespaceManager xmlNamespaceManager)
       at Microsoft.EnterpriseManagement.DataAccessLayer.SqlCommandBuilder.CreateWhereClauseFromCriteria(ICriteriaContext criteriaResolver, String criteriaXml, IList`1 internalColumnDefinitions)
       at Microsoft.EnterpriseManagement.DataAccessLayer.QueryRequest.CreateSqlCommandForSelectType()
       at Microsoft.EnterpriseManagement.DataAccessLayer.QueryRequest.Execute(SqlNotificationRequest sqlNotificationRequest)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.QueryGenerator.ExecuteSnapshotQuery(MembershipRule membershipRule, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.ExpressionEvaluatorForSnapshot.EligibleBySnapshotResults(MembershipRule membershipRule, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.ExpressionEvaluatorForSnapshot.GetRelationshipChangesForSnapshot(MembershipRule membershipRule, Guid groupInstanceId, Guid groupTypeId, Guid relationshipId, IList`1 groupKeyNameValuePairs, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.MembershipRuleEvaluator.EvaluateSnapshot(MembershipSubscription subscription, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.MembershipRuleEvaluator.EvaluateDifference(MembershipSubscription membershipSubscription, List`1 membershipChanges, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.MembershipRuleEvaluator.EvaluateDifference(MembershipSubscription subscription, DatabaseConnection databaseConnection)
       at Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.MembershipCalculationManager.SnapshotCalculation(MembershipSubscription membershipSubscription, DatabaseConnection databaseConnection) which was running as part of rule "UINameSpacecfb9e1dff3434e708f669c7735071fbb.Group.DiscoveryRule" running for instance "_Group-Windows-Prod" with id:"{F774E8AE-3ED5-849B-204A-23F6B1E8BC40}" in management group "XXXXXXXX".

    Warning 1103:

    Summary: 1 rule(s)/monitor(s) failed and got unloaded, 1 of them reached the failure limit that prevents automatic reload. Management group "XXXXXX". This is summary only event, please see other events with descriptions of unloaded rule(s)/monitor(s).

    Wednesday, March 25, 2020 2:42 PM
  • But I dont see the Reports, I have only the following SQL report-folders:

    

    In the SQL  Server 2008 (Monitoring i have the Reports for User Connections. But in the Micrsofot SQL Server on Windows (Views), I have only the following Report:

    This is expected, the Microsoft SQL Server on Windows (Views) only contains the "Database Files Space Usage Forecast" report template.

    What you're looking for is the User connections by day report template which is located under one of the SQL Server 20XX (Monitoring).

    Example:

    Not sure about the errors, they may be related to something else.

    If you open the Reporting Services Configuration Manager and browse to the Web Service URL & Web Portal URL, can you see the "Microsoft.Windows.Server.20XX.Monitoring" objects there?

    Web Service URL

    Web Portal URL


    Blog: https://thesystemcenterblog.com LinkedIn:

    Wednesday, March 25, 2020 3:01 PM
  • No, the objects aren't there. I only have the views, and the SQL 2008 Reports. 

    Thursday, March 26, 2020 7:47 AM
  • Looks like the management pack isn't successful in importing the reports, did the events you posted earlier appear upon importing the SQL Server management pack? There should be some indications in the event log.

    Also which SCOM version and build are you using?


    Blog: https://thesystemcenterblog.com LinkedIn:

    Thursday, March 26, 2020 8:32 AM
  • Yes the Errors appeard upon importing the MP.

    I have SCOM 2019 with the Update Rollup 1.

    Friday, March 27, 2020 3:30 PM
  • Can you check which rule is being unloaded and which group is it referring to?

    Get-SCOMRule -Id a0210cc1-e9a0-7e43-9697-02102dc093e9
    Get-SCOMGroup -Id f774e8ae-3ed5-849b-204a-23f6b1e8bc40


    Blog: https://thesystemcenterblog.com LinkedIn:

    Saturday, March 28, 2020 11:35 AM
  • I don't get a result when i try to get the Rule. The group is a self-created Group with Windows-Server Objects.

    

    Monday, March 30, 2020 6:22 AM
  • Might try reinstalling the SCOM reporting feature, it's a rather fast process.

    Make sure the user installing has:

    • SysAdmin to the SCOM databases.
    • Local Administrator on the SCOM & SQL servers.
    • Member of the SCOM Administrators group.

    Blog: https://thesystemcenterblog.com LinkedIn:

    Monday, March 30, 2020 6:38 AM
  • Hi Leon

    I opened a case at Microsoft. The expert recreated my envoirement, and he has exactly the same issue. He also has only the (Views) Report. 

    https://kevinholman.com/2019/06/12/how-to-transition-to-the-sql-version-agnostic-mp/ <-- Here have the users "Damian" and "David L" the same issue. Kevin Holmann explains that it isn't a Problem, it's more a change.

    The expert from my case looks now with product group team to find a solution. 
    I will post it, when I have a solution.

    Kind Regards
    Stately

    • Edited by StatelyElf Tuesday, March 31, 2020 3:15 PM
    Tuesday, March 31, 2020 3:10 PM