Where to find Connector Status using SQL?

Conner_Wood

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'

Conner_Wood

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_Atance

Not sure where it is in SQL but pretty sure you can get it via PowerShell - 
Get-SCSMConnector
or
Get-SCSMConnector | fl

Conner_Wood

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.