Troubleshooting LFX Connectors in Service Manager - the SCCM Connector where LastInventoryDate is not getting updated

  • Article

Hi everyone! :D It's been a while since my last post. I've been pretty busy with migrating to the cloud, hehe

I do have some posts however, which I wanted to write and never got the chance to yet. So, let's continue with the articles - this is one of them about troubleshooting LFX Connectors in Service Manager.

Enjoy!

 

First, try to concentrate only on specific stuff (if there is more than 1 issue, we need to take them one after the other):

  • Choose a single Computer that has the issue for which data should get imported / updated from SCCM through the SCCM Connector
  • Choose a specific type of data (field) – here we may start with LastInventoryDate not getting updated (focusing on the Computer we selected)

You can also have a look at this article which explains a little bit about how LFX Connectors work and check out if BatchSize(es) for the Data that is missing is high enough in relation to how much data is getting imported:

he different types of data import template definitions for the SCCM 2012+ Connector are defined in the Microsoft.EnterpriseManagement.ServiceManager.Connector.Sms2011 MP and you can export this MP using this query to understand the definitions:

 select MPName, convert(xml, MPXML)<br>from ManagementPack<br>where MPName = 'Microsoft.EnterpriseManagement.ServiceManager.Connector.Sms2011'

All this, is stored in the LFX.DataTable with specific DataName – here, we will have LFX Staging Table and View information together with the Queries used to get the data from the direct source (here SCCM site database) and afterwards from the LFX Staging Tabled, import it into the SM (CMDB) Instance Space (tables) => BME/TME, MT_ClassName and Relationship tables.

Here is a Query to have a general overview of the information needed (for now, for all the DataTables of the SCCM 2012+ Connector):

 select *<br>from LFX.DataTable<br>where DataName like '%CMv5_%'

Notice how some DataNames have a “Cached_ ” prefix in their name. This is because:

  • The ones without the “Cached_ ” prefix are the ones which are being used by the Data Provider (gets the data directly from the custom source – in this case, the SCCM site database)
  • The ones that have “Cached_ ” prefix are the ones used by the Data Consumer that will get the already imported data (from the LFX Staging Tables) and import it into the instance space (only then will it be visible in the Console)

 

This is important because if you look at the QueryString field, you can see the SQL Query that will be executed to get that data.

 

Let’s search for the type of data we are interested in (LastInventoryDate) directly in the XML definition of the MP and we find it here:

  <Object Path="$Context/Path[Relationship='LFX!System.LinkingFramework.ConnectorEmbedsTables' SeedRole='Source' TypeConstraint='LFX!System.LinkingFramework.DataTable']$"><br>       <Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/DataName$">Cached_CMv5_LogicalComputers</Property><br>       <Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/WatermarkField$">E.Lfx_Timestamp</Property><br>       <Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/WatermarkType$">0</Property><br>       <Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/BatchIdField$">E.Lfx_RowId</Property><br>       <Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/BatchIdType$">0</Property><br>       <Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/BatchIdSize$">500</Property><br>       <Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/UseCache$">false</Property><br>       <Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/QueryString$"><br>                                         SELECT E.Lfx_RowId,<br>                                                       E.Lfx_SourceID,<br>                                                       E.Lfx_Status,<br>                                                       E.DisplayName, <br>                                                       E.PrincipalName, <br>                                                       E.NetbiosComputerName, <br>                                                       E.NetbiosDomainName,<br>                                                       E.OffsetInMinuteFromGreenwichTime,<br>                                                       E.IsVirtualMachine,<br>                                                       E.LastInventoryDate,<br>                                                       E.ActiveDirectorySite<br>                                         from [LFXSTG].v_Cached_CMv5_LogicalComputers E <br>                                  </Property><br>       <Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/CollectionName$">Cached_CMv5_LogicalComputers</Property><br>       <Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/DependOnDataTable$">Cached_CMv5_PhysicalComputers</Property><br></Object>

We can figure out from here, that the LastInventoryDate value is coming from the v_Cached_CMv5_LogicalComputers View (notice the v_ at the beginning of the name).

What we can also figure out already from the definition above, is that this depends on another view called Cached_CMv5_PhysicalComputers (just add a “v_ ” as prefix if you want to query it – and don’t forget that it belongs to the LFXSTG schema).

 

So, let’s have a look from SQL Management Studio at the definition of this view v_Cached_CMv5_LogicalComputers:

 CREATE VIEW [LFXSTG].[v_Cached_CMv5_LogicalComputers] AS<br>SELECT S.Lfx_RowId,<br>        S.Lfx_SourceID,<br>        S.Lfx_Timestamp,<br>        CCX.Lfx_Status,<br>        CCX.Name0 AS 'DisplayName', <br>        COALESCE(CCX.Name0, S.Netbios_Name0) <br>              + '.' + COALESCE(CCX.Domain0, S.Resource_Domain_OR_Workgr0) AS 'PrincipalName', <br>        S.Netbios_Name0 AS 'NetbiosComputerName', <br>        S.Resource_Domain_OR_Workgr0 AS 'NetbiosDomainName',<br>        CCX.CurrentTimeZone0 AS 'OffsetInMinuteFromGreenwichTime',<br>        CCX.IsVirtualMachine,        W.LastHWScan AS 'LastInventoryDate',<br>        S.AD_Site_Name0 AS 'ActiveDirectorySite'<br>FROM LFXSTG.CMv5_SYSTEM S<br>CROSS APPLY <br>LFXSTG.fn_CheckCMv5CachedComputers(S.Lfx_SourceID, S.ResourceID, S.SMS_Unique_Identifier0, S.Lfx_Status) AS CCX<br>    LEFT JOIN LFXSTG.CMv5_WORKSTATION_STATUS W<br>        ON S.ResourceID = W.ResourceID AND S.Lfx_SourceId = W.Lfx_SourceId<br>WHERE S.Netbios_Name0 IS NOT NULL <br>    AND S.Resource_Domain_OR_Workgr0 IS NOT NULL<br>    AND CCX.Lfx_Status != 'I'

So now we figure out that we get the LastInventoryDate value from LastHWScan field of the LFXSTG . CMv5_WORKSTATION_STATUS table.

 

This also tells us, that this is the Data Consumer part (notice the “Cached_ ” prefix in the DataName). So how are we getting this data in the table?

The table name is CMv5_WORKSTATION_STATUS and so we can look for the QueryString in the LFX.DataTable by its name:

 select *<br>from LFX.DataTable<br>where DataTableName = 'CMv5_WORKSTATION_STATUS'

So here we are, this is the Query we are executing directly on the SCCM site database to get the data from:

 
SELECT<br>       S.ChangeAction as Lfx_Status,<br>       S.ResourceID,<br>       S.BatchingKey,<br>       S.GroupKey,<br>       S.TimeStamp,       S.LastHWScan,<br>       S.SystemDefaultLCID,<br>       S.TimezoneOffset,<br>       S.LastReportVersion<br>FROM SCCM_Ext.vex_GS_WORKSTATION_STATUS S<br>INNER JOIN SCCM_Ext.vex_FullCollectionMembership CM<br>ON S.ResourceID = CM.ResourceID<br>INNER JOIN SCCM_Ext.vex_Collection C<br>ON C.CollectionID = CM.CollectionID<br>WHERE S.LastHWScan IS NOT NULL <br>AND C.ChangeAction = 'U' and CM.ChangeAction = 'U'<br>AND $COLLECTIONLIST<br>ORDER BY S.rowversion

We can see that we get the LastHWScan value from the SCCM_Ext.vex_GS_WORKSTATION_STATUS table/view from the SCCM site database.

  • Notice $COLLECTIONLIST here – this is replaced with the SCCM Site Collection filters that you have configured in your SCCM Connector.

 

Data to collect while we let the affected SCCM Connector (responsible for the chosen Computer) run until it finishes, would be:

  • SQL Profiler trace on the SCCM Site database
  • SQL Profiler trace on the ServiceManager database
  • SM ETW Trace on LFX Connectors: cmd -l VER -a CON

 

In the SQL Profiler Trace of the SCCM Site database, look for the query that we have identified that gets the data – and ideally, run it manually to see what data you get as result from the SCCM Site database.

After this, look in the SQL Profiler Trace of the ServiceManager database and look at the other query that we identified that gets the data from the LFXSTG tables/views (as explained/identified above).

 

Identify the ResourceId of the Computer you are looking for and you can check if it is being synchronized by which and what Connectors using this query (on the ServiceManager database):

 select<br>       wss.Lfx_RowId,<br>       wss.Lfx_Timestamp,<br>       ds.DisplayName,<br>       wss.Lfx_Status,<br>       wss.ResourceID,<br>       wss.BatchingKey,<br>       wss.GroupKey,<br>       wss.TimeStamp,<br>       wss.LastHWScan,<br>       wss.SystemDefaultLCID,<br>       wss.LastReportVersion<br>from LFXSTG.CMv5_WORKSTATION_STATUS wss<br>join LFX.DataSource ds<br>       on wss.Lfx_SourceId = ds.DataSourceId<br>where wss.ResourceId in (RES_ID_1, RES_ID_2, RES_ID_3, RES_ID_ETC)

Now another thing we may want to know about (as you might have noticed from the SQL Profiler queries and Batch/WaterMark fields in the LFX.DataTable), is that we import data in batches and that we also use a WaterMark field (defined in the LFX.DataTable) to keep track of where we left of the on the previous time to only get new data on next runs.

 

We are keeping track of the WaterMark for each DataName in the LFX.ClientWorkTable table:

 select<br>       cwt.ClientWorkTableId as [WorkTableId],<br>       cwt.Watermark as [WaterMark],<br>       cwt.LastSyncTime as [LastSyncTime],<br>       ds.DataSourceName as [ConnectorName],<br>       ds.DisplayName as [ConnectorDisplayName],<br>       dt.DataName as [DataName],<br>       dt.DataTableName as [DataTableName],<br>       (<br>              case<br>                     when dt.WatermarkType = -1 then 'None'<br>                     when dt.WatermarkType = 0 then 'DateTime'<br>                     when dt.WatermarkType = 1 then 'Timestamp'<br>                     when dt.WatermarkType = 2 then 'Number'<br>                     when dt.WatermarkType = 3 then 'Number'<br>              end <br>       ) as [WaterMarkType]<br>from LFX.ClientWorkTable as cwt<br>inner join LFX.DataSource as ds<br>       on ds.DataSourceId = cwt.DataSourceId<br>inner join LFX.DataTable as dt<br>       on dt.DataTableId = cwt.DataTableId

To reset the WaterMark for the CMv5_WORKSTATION_STATUS DataName for example, so that the SCCM Connector will try to resynchronize all the data type available in SCCM again, we can run this query:

  • Repace “AD_CONNECTOR_DSIPLAYNAME” with the DisplayName of the (SCCM) Connector which you are interested in (as seen from the results of the previous query).
  • Pay attention to the WaterMarkType and based on that, understand what type of value we need to set in order for the WaterMark to be set to “0” and try to re-sync everything related to this type of data (in this example CMv5_WORKSTATION_STATUS)
 update cwt<br>set cwt.Watermark = 0x00000000<br>from LFX.ClientWorkTable as cwt<br>inner join LFX.DataSource as ds<br>       on ds.DataSourceId = cwt.DataSourceId<br>inner join LFX.DataTable as dt<br>       on dt.DataTableId = cwt.DataTableId<br>where<br>       ds.DisplayName = 'REPLACE_WITH_ CONNECTOR_DSIPLAYNAME_STRING_HERE' and<br>       dt.DataName = 'CMv5_WORKSTATION_STATUS'