SCCM Query into ServiceNow

Question

Hi,

I am using the following query which is currently pulling information successfully from SCCM to ServiceNow:

SELECT

v_GS_COMPUTER_SYSTEM.Name0 as name,

v_GS_COMPUTER_SYSTEM.domain0 as domain,

v_GS_COMPUTER_SYSTEM.Model0 as model,

v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer,

v_GS_COMPUTER_SYSTEM.ResourceID,

v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 as username,

v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors,

v_GS_SYSTEM.SystemRole0 as SystemRole,

v_GS_OPERATING_SYSTEM.Caption0 as caption,

v_GS_OPERATING_SYSTEM.Version0 as Version,

v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,

WorkstationStatus_DATA.LastHWScan,

v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,

v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber,

v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,

v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,

v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,

WorkstationStatus_DATA.TimeKey as TimeKey,

v_GS_OPERATING_SYSTEM.TimeStamp as OsTimeStamp

FROM v_GS_COMPUTER_SYSTEM

LEFT JOIN WorkstationStatus_DATA ON v_GS_COMPUTER_SYSTEM.ResourceID = WorkstationStatus_DATA.MachineID

LEFT JOIN v_GS_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID

LEFT JOIN v_GS_PC_BIOS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID

LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID

LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID

LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID

LEFT JOIN v_GS_BASEBOARD ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_BASEBOARD.ResourceID

LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID

What I now want to do is reference the last online time, for which I am using the following, which I place under the 'OSTimeStamp'

v_CollectionMemberClientBaselineStatus.CNLastOnlineTime as LastOnlineTime

The problem however is that when I try to refence that command, I receive the following error:

Msg 4104, Level 16, State 1, Line 23

The multi-part identifier "v_CollectionMemberClientBaselineStatus.CNLastOnlineTime" could not be bound.

Does anybody know how I can rectify this?

Thank you.

Answer 1

Keep in mind that it is not supported to query tables directly. E.g. WorkstationStatus_DATA look for the supported view. https://learn.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/sql-server-views-configuration-manager

Also TimeKey, if I remember correctly, that column is for internal configmgr use.

Next you need to join on v_r_system otherwise your will get incorrect results. https://www.recastsoftware.com/resources/how-to-get-the-most-accurate-results-from-configmgr/

Answer 2

You're not using that view in your query. You need to join with that view to be able to reference its columns.

Answer 3

Sorry, the error should state:

Msg 4104, Level 16, State 1, Line 23

The multi-part identifier "v_CollectionMemberClientBaselineStatus.CNLastOnlineTime" could not be bound.