none
Custom application containment relationship to SQL database RRS feed

  • Question

  • Hello,

    I am trying to figure out, how to create containment relationship to SQL database. I have a custom management pack for inhouse application which use SQL DB. I have discovered applications and one of their property is SQL DB. Application class looks like this:

    <ClassType ID="Custom.Application.Class" Base="Windows!Microsoft.Windows.LocalApplication" Accessibility="Public" Abstract="false" Hosted="true" Singleton="false">
      <Property ID="Name" Type="string" Key="true"/>
      <Property ID="Version" Type="string"/>
      <Property ID="Path" Type="string"/>
      <Property ID="Environment" Type="string"/>
      <Property ID="CountryCode" Type="string"/>
      <Property ID="DatabaseServer" Type="string"/>
      <Property ID="DatabaseProvider" Type="string"/>
      <Property ID="DatabaseName" Type="string"/>
    </ClassType>
            

    DatabaseName property is taken from application INI file. And I would like to containment SQL DB to Custom.Application.Class according DatabaseName property. Tried to create relationship, but no luck.

    <RelationshipType ID="Custom.Application.Class.Contains.Microsoft.SQLServer.Database" Base="System!System.Containment" Abstract="false" Accessibility="Public">
              <Source ID="Source" Type="Custom.Application.Class"/>
              <Target ID="Target" Type="MSL!Microsoft.SQLServer.Database"/>
    </RelationshipType>

    Also, tried to add SQL DB property to Custom.Application.Class during discovery. Idea was that SCOM will find existing DB with name discovered and create relationship. Like with Widows.Computer and PrincipalName property.

    $instance.AddProperty("$MPElement[Name='Custom.Application.Class']/DatabaseName$", $convertedString.Database)
    $instance.AddProperty("$MPElement[Name='MSL!Microsoft.SQLServer.Database']/DatabaseName$", $convertedString.Database)

    I found only articles, how to create new SQL DB instance, but I don`t have all the properties to populate it correctly. I have only DB name, all other properties needs to be populated from SQL discovery. That`s why I want to create relationship from existing instance.

    I was wondering, if it would be possible to found existing MSL!Microsoft.SQLServer.Database within Custom.Application.Class discovery, and create relationship.

    Tuesday, November 19, 2019 11:13 AM

All replies

  • Hi user64bit

    I think the automatic relationship discovery like you're hoping for only works for a Hosted relationship, not for Containment like you're using. You'll need to do something like this in your discovery script:

    $CustomClass = $DiscoveryData.CreateClassInstance("$MPElement[Name='Custom.Application.Class']$")
    $CustomClass.AddProperty("$MPElement[Name='Custom.Application.Class']/Name$","SomeName")
    
    $Database = $DiscoveryData.CreateClassInstance("$MPElement[Name='MSL!Microsoft.SQLServer.Database']$")
    $Database.AddProperty("$MPElement[Name='MSL!Microsoft.SQLServer.Database']/DatabaseName$",$DBName) # You'll need your script to get this however is appropriate
    
    $Relationship = $DiscoveryData.CreateRelationshipInstance("$MPElement[Name='Custom.Application.Class.Contains.Microsoft.SQLServer.Database']$")
    $Relationship.Source = $CustomClass
    $Relationship.Target = $Database
    $DiscoveryData.AddInstance($Relationship)

    Edit: incidentally you can't use a Hosted relationship here as the Microsoft.SQLServer.Database class already has one.

    Hope this helps.

    Steve

    Wednesday, November 20, 2019 1:11 AM
  • Hello Steve,

    thanks for answer. I read somewhere about this solution, but was afraid that I will create new Microsoft.SQLServer.Database instance instead of using existing one. But then I realized, that $Database is not inserted into $DiscoveryData, so it should be fine. Tried that and there is a problem. As only DatabaseName is available in config file, I`ve put this code into MP.

    $dbInstance = $DiscoveryData.CreateClassInstance("$MPElement[Name='MSL!Microsoft.SQLServer.Database']$")
    $dbInstance.AddProperty("$MPElement[Name='MSL!Microsoft.SQLServer.Database']/DatabaseName$", $convertedString.Database)
    $Relationship = $DiscoveryData.CreateRelationshipInstance("$MPElement[Name='Custom.Application.Class.Contains.Microsoft.SQLServer.Database']$")
    $Relationship.Source = $instance
    $Relationship.Target = $dbInstance
    $DiscoveryData.AddInstance($Relationship)
    Then I got error on management server.

    Discovery data couldn't be inserted to the database. This could have happened because  of one of the following reasons:

      - Discovery data is stale. The discovery data is generated by an MP recently deleted.
      - Database connectivity problems or database running out of space.
      - Discovery data received is not valid.

     The following details should help to further diagnose:

     DiscoveryId: d9bfa3e9-d1ac-baca-6ef5-40e3f427ee68
     HealthServiceId: ffa3aaff-8bfc-8173-5803-7067085bac5d
     Microsoft.EnterpriseManagement.Common.DiscoveryDataMissingKeyException,Missing key in the discovery data item.
    Key property name: Microsoft.Windows.Computer.PrincipalName
    Rule ID: d9bfa3e9-d1ac-baca-6ef5-40e3f427ee68
    Instance:
    <?xml version="1.0" encoding="utf-16"?><ClassInstance TypeId="{10c1c7f7-ba0f-5f9b-c74a-79a891170934}"><Settings><Setting><Name>c815da4f-5c36-40ee-e39a-de3532ccdf3e</Name><Value> ApplicationDatabaseName</Value></Setting></Settings></ClassInstance>.

    I assume that this should be PrincipalName of SQL server, but I have only application server name. Anyway, tried that and of course second error arrived.

    Discovery data couldn't be inserted to the database. This could have happened because  of one of the following reasons:

      - Discovery data is stale. The discovery data is generated by an MP recently deleted.
      - Database connectivity problems or database running out of space.
      - Discovery data received is not valid.

     The following details should help to further diagnose:

     DiscoveryId: d9bfa3e9-d1ac-baca-6ef5-40e3f427ee68
     HealthServiceId: 90e29676-d7c7-a23f-7a79-6cfb71296922
     Microsoft.EnterpriseManagement.Common.DiscoveryDataMissingKeyException,Missing key in the discovery data item.
    Key property name: Microsoft.SQLServer.ServerRole.InstanceName
    Rule ID: d9bfa3e9-d1ac-baca-6ef5-40e3f427ee68
    Instance:
    <?xml version="1.0" encoding="utf-16"?><ClassInstance TypeId="{10c1c7f7-ba0f-5f9b-c74a-79a891170934}"><Settings><Setting><Name>5c324096-d928-76db-e9e7-e629dcc261b1</Name><Value>AppServer.domain.com</Value></Setting><Setting><Name>c815da4f-5c36-40ee-e39a-de3532ccdf3e</Name><Value> ApplicationDatabaseName</Value></Setting></Settings></ClassInstance>.

    In the end it looks, that all the key properties from all hosting classes has to be defined during creation of Microsoft.SQLServer.Database class instance. It makes sense in the end, as database with same name can be hosted on more servers and on different SQL instances. It looks that relationship needs to have all the key properties defined, so it can count instance Path and then identify the correct database instance. Please, let me know, if I am right.
    Thursday, November 21, 2019 4:47 PM
  • Hi user64bit

    The first error I suspect is coming from the discovery of your custom class, not the relationship. You've specified a base of Microsoft.Windows.LocalApplication. This is hosted by Microsoft.Windows.Computer so in your discovery you need to add a reference to the key property of the computer that's hosting your custom application. The key of Microsoft.Windows.Computer is PrincipalName which is mentioned in the error. So when adding the properties of your custom class you need to also add something like this:

    $Instance.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $ComputerName)

    It's common practice to pass this property into a discovery script so you don't need to find it programatically. Depending upon what class your discovery is targeting exactly it would be something along these lines:

    $Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$

    To be honest at this point I'm not sure why in the 2nd error it's wanting a reference to Microsoft.SQLServer.ServerRole. Is that what your discovery is targeting? It might help if you post your entire discovery code.

    Steve

    Thursday, November 21, 2019 11:49 PM
  • Hello Steve,

    the first error is related to $dbInstance as well. Application instance has property PrincipalName and it is computer name received as script parameter. This is not a problem.

    Anyway, will try to find some solution how to deal with that.

    Friday, November 22, 2019 6:17 AM
  • Here's a code I had for exactly this with the SQL 2008 MP.  The link was done using info from ODBC.  Should be close enough to your requirements.  You need to determine which properties are required on the SQL MP side then you can just populate accordingly.

    $RegPaths = @("HKLM:\SOFTWARE\Wow6432Node\ODBC\ODBC.INI", "HKLM:\SOFTWARE\ODBC\ODBC.INI")
    
    ForEach ($SubInstanceData in $(Get-ChildItem $RegPaths | Where-Object{ $_.PSIsContainer -eq $true } | Get-ItemProperty | Where-Object{ $_.Server } | Select-Object -Unique | Select @{n='ComputerName';e={(([System.Net.Dns]::Resolve(($_.Server -split '\\')[0])).HostName).ToLower()}},@{n='InstanceName';e={$(if ($_.Server -match '\\') { ($_.Server -split '\\')[1] } else { 'MSSQLSERVER' })}} )){
    			$SubInstance = $discoveryData.CreateClassInstance("$MPElement[Name='SQL!Microsoft.SQLServer.DBEngine']$")
    			$SubInstance.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $SubInstanceData.ComputerName)
    			$SubInstance.AddProperty("$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", $SubInstanceData.InstanceName)
    			$discoveryData.AddInstance($SubInstance)
    		
    			#Add Relationship
    			$Relation = $discoveryData.CreateRelationshipInstance("$MPElement[Name='MyCompany.Solution.DAContainsSQL']$")
    			$Relation.Source = $DAInstance
    			$Relation.Target = $SubInstance
    			$discoveryData.AddInstance($Relation)
    		}

    Wednesday, November 27, 2019 8:59 PM