Where to find Connector Status using SQL?

Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
edited September 2018 in SQL Server
In the SCSM Console > Administration > Connectors... it is possible to see Start Time, Finish Time, and Status of a Connector which helps when determining Finished Success vs Finished With Error.

I wish to accomplish the same thing through SQL.... however I've been unable to find where the Status is....

I have doubts I'm looking for a GUID even though Microsoft.SystemCenter.LinkingFramework.SyncStatusEnum contains the Enums I want to find..... I have a feeling the "synchronization request" is some instance referencing the connector but I don't know what to look for....

Here's what I've got so far but nothing from it gives me the correct Start Time, Finish Time, Status, Percentage
USE [ServiceManager];

SELECT BME.BaseManagedEntityId
	  ,BME.DisplayName
	  ,BME.FullName
	  ,Conn.Id
	  ,dataConn.Enabled_2B488464_2BCA_D1E4_B438_D6DE9759E808 AS 'Enabled'
	  ,MT.TypeName AS 'Targeted Class Type'
	  ,MT_TypeProjection.TypeProjectionName AS 'Targeted Type Projection'
  FROM [dbo].[MT_Cireson$AssetManagement$Import$Connector] CiresonConn (nolock)

  JOIN [dbo].[MT_Connector] Conn (nolock)
  ON Conn.[BaseManagedEntityId] = CiresonConn.[BaseManagedEntityId]

  JOIN [dbo].[BaseManagedEntity] BME (nolock)
  ON BME.BaseManagedEntityId = CiresonConn.BaseManagedEntityId

  JOIN [dbo].[MT_System$LinkingFramework$DataSource] dataConn (nolock)
  ON dataConn.BaseManagedEntityId = CiresonConn.BaseManagedEntityId

  JOIN [dbo].[ManagedType] MT (nolock)
  ON CiresonConn.[ClassId_FB1E8845_D54A_CAF4_9F4D_8E961DE336E5] = MT.[ManagedTypeId]

  JOIN [dbo].[TypeProjection] MT_TypeProjection (nolock)
  ON CiresonConn.[TypeId_D6645BE6_B284_3894_EC29_804D0DEDE3FC] = MT_TypeProjection.[TypeProjectionId]

  WHERE BME.DisplayName = 'Printer.Import.New'

Answers

  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    Aha, I found something at least.... filtering on the [dbo].[WindowsWorkflowTaskJobStatus] table using the Connectors BaseManagedEntityId .....

    And it looks like I can use the BatchId and match it to the [dbo].[JobStatus] BatchId ... and I am seeing a Status Column, and while it's just a number, I can figure it out.....

    But I don't know where it's storing Status and Percentage that it presents in the console, some connectors haven't been run in years, so if it relied on that data it wouldn't be able to be found if purged.
  • James_AtanceJames_Atance Cireson Support Advanced IT Monkey ✭✭✭
    Not sure where it is in SQL but pretty sure you can get it via PowerShell - 
    Get-SCSMConnector
    or
    Get-SCSMConnector | fl

  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭

    While I was unsuccessful finding the connector workflow status...

    I was able to find workflow subscriptions like email subscriptions is [ServiceManager].[dbo].[CmdbInstanceSubscriptionState]

    But yeah, I have a feeling this data is stored elsewhere, probably on the primary workflow server in its own temporary file.

Sign In or Register to comment.