Jump to content

Missing Assets


Recommended Posts

Hi all,


Ive been running an SCCM to hornbill asset import nightly which is successfully creating/updating laptop assets.  I have noticed that some of the laptops in our SCCM are not being imported to hornbill but cannot figure out why this would be.  i have examined our log files and do not see the relevant machine names at all yet i can see them in our SCCM system.

I dont really know where to start troubleshooting this.  my import script is here (minus config info).  any tips would be much appreciated!


 "Query": "SELECT dbo.v_R_System.ResourceID AS [AssetID], CONVERT(varchar, dbo.v_R_System.Last_Logon_Timestamp0,20) AS [Last_Logon_Timestamp0], dbo.v_R_System.User_Name0 AS [UserName], dbo.v_R_System.Netbios_Name0 AS [MachineName], dbo.v_R_System.Resource_Domain_OR_Workgr0 AS [NETDomain], dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [OperatingSystemCaption], dbo.v_R_System.Operating_System_Name_and0 AS [OperatingSystem], dbo.v_GS_OPERATING_SYSTEM.Version0 AS [OperatingSystemVersion], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [ServicePackVersion], dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [SystemManufacturer], dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [SystemModel], dbo.v_GS_PC_BIOS.SerialNumber0 AS [SystemSerialNumber], OAProc.MaxClockSpeed0 AS [ProcessorSpeedGHz], OAProc.Name0 AS [ProcessorName], dbo.v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [NumberofProcessors], dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [MemoryKB], dbo.v_GS_LOGICAL_DISK.Size0 AS [DiskSpaceMB], dbo.v_GS_LOGICAL_DISK.FreeSpace0 AS [FreeDiskSpaceMB], OAIP.IP_Addresses0 AS [IPAddress], OAMac.MAC_Addresses0 AS [MACAddress], dbo.v_GS_PC_BIOS.Description0 AS [BIOSDescription], dbo.v_GS_PC_BIOS.ReleaseDate0 AS [BIOSReleaseDate], dbo.v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [SMBIOSVersion], dbo.v_GS_SYSTEM.SystemRole0 AS [SystemType], OASysEncl.ChassisTypes0 AS [ChassisTypes], OASysEncl.TimeStamp AS [ChassisDate], dbo.v_R_System.AD_Site_Name0 AS [SiteName] FROM dbo.v_R_System OUTER APPLY (SELECT TOP 1 dbo.v_GS_SYSTEM_ENCLOSURE.* FROM dbo.v_GS_SYSTEM_ENCLOSURE WHERE dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID ORDER BY TimeStamp DESC) OASysEncl OUTER APPLY (SELECT TOP 1 IP_Addresses0, ROW_NUMBER() OVER (order by (SELECT 0)) AS rowNum FROM dbo.v_RA_System_IPAddresses WHERE dbo.v_RA_System_IPAddresses.ResourceID = dbo.v_R_System.ResourceID ORDER BY rowNum DESC) OAIP OUTER APPLY (SELECT TOP 1 MAC_Addresses0 FROM dbo.v_RA_System_MACAddresses WHERE dbo.v_RA_System_MACAddresses.ResourceID = dbo.v_R_System.ResourceID ) OAMac OUTER APPLY (SELECT TOP 1 MaxClockSpeed0, Name0 FROM dbo.v_GS_PROCESSOR WHERE dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID ORDER BY TimeStamp DESC) OAProc LEFT JOIN dbo.v_GS_X86_PC_MEMORY ON dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_R_System.ResourceID LEFT JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID LEFT JOIN dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID LEFT JOIN dbo.v_GS_PC_BIOS ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_R_System.ResourceID LEFT JOIN dbo.v_GS_LOGICAL_DISK ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID LEFT JOIN dbo.v_FullCollectionMembership ON (dbo.v_FullCollectionMembership.ResourceID = v_R_System.ResourceID) LEFT JOIN dbo.v_GS_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_R_System.ResourceID WHERE dbo.v_GS_LOGICAL_DISK.DeviceID0 = 'C:' AND dbo.v_FullCollectionMembership.CollectionID = 'SMS00001' "
    "AssetTypes": [
            "AssetType": "Laptop",
            "OperationType": "Both",
            "PreserveShared": false,
            "Query": "AND OASysEncl.ChassisTypes0 IN (8, 9, 10, 14) AND dbo.v_R_System.Obsolete0 = 0 ORDER BY dbo.v_R_System.ResourceID ASC",
            "AssetIdentifier": {
                "DBColumn": "MachineName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            "SoftwareInventory": {
                "AssetIDColumn": "AssetID",
                "AppIDColumn": "AppID",
                "Query": "SELECT AppID = CASE WHEN Publisher0 IS NULL AND Version0 IS NULL THEN DisplayName0 WHEN Publisher0 IS NOT NULL AND Version0 IS NULL THEN Publisher0+DisplayName0 ELSE Publisher0+DisplayName0+Version0 END, DisplayName0 , Version0, FCM.Name, convert(datetime, InstallDate0, 112) AS InstallDate0, Publisher0, ProdID0, FCM.ResourceID FROM v_Add_Remove_Programs AS ARP JOIN v_FullCollectionMembership As FCM on ARP.ResourceID=FCM.ResourceID WHERE FCM.CollectionID = 'SMS00001' AND FCM.ResourceID = '{{AssetID}}' AND DisplayName0 IS NOT NULL AND DisplayName0 != '' AND DisplayName0 NOT LIKE '%Update for Windows%' ORDER BY ProdID0 ASC",
                "Mapping": {
                    "h_app_name": "{{.DisplayName0}}",
    "AssetGenericFieldMapping": {
        "h_name": "{{.MachineName}}",
        "h_site": "{{.SiteName}}",
        "h_asset_tag": "{{.MachineName}}",
        "h_acq_method": "",
        "h_actual_retired_date": "",
        "h_beneficiary": "",
        "h_building": "",
        "h_cost": "",
        "h_cost_center": "",
        "h_country": "",
        "h_created_date": "",
        "h_deprec_method": "",
        "h_deprec_start": "",
        "h_description": "{{.MachineName}} ({{.SystemModel}})",
        "h_disposal_price": "",
        "h_disposal_reason": "",
        "h_floor": "",
        "h_geo_location": "",
        "h_invoice_number": "",
        "h_location": "",
        "h_location_type": "",
        "h_maintenance_cost": "",
        "h_maintenance_ref": "",
        "h_notes": "",
        "h_operational_state": "",
        "h_order_date": "",
        "h_order_number": "",
        "h_owned_by": "",
        "h_product_id": "",
        "h_received_date": "",
        "h_residual_value": "",
        "h_room": "",
        "h_scheduled_retire_date": "",
        "h_supplier_id": "",
        "h_supported_by": "",
        "h_used_by": "{{.UserName}}",
        "h_version": "",
        "h_warranty_expires": "",
        "h_warranty_start": ""
    "AssetTypeFieldMapping": {
        "h_name": "{{.MachineName}}",
        "h_mac_address": "{{.MACAddress}}",
        "h_net_ip_address": "{{.IPAddress}}",
        "h_net_computer_name": "{{.MachineName}}",
        "h_net_win_domain": "{{.NETDomain}}",
        "h_model": "{{.SystemModel}}",
        "h_manufacturer": "{{.SystemManufacturer}}",
        "h_cpu_info": "{{.ProcessorName}}",
        "h_description": "{{.SystemModel}}",
        "h_last_logged_on": "{{.Last_Logon_Timestamp0}}",
        "h_last_logged_on_user": "{{.UserName}}",
        "h_memory_info": "{{.MemoryKB}}",
        "h_net_win_dom_role": "",
        "h_optical_drive": "",
        "h_os_description": "{{.OperatingSystem}}",
        "h_os_registered_to": "",
        "h_os_serial_number": "",
        "h_os_service_pack": "{{.ServicePackVersion}}",
        "h_os_type": "",
        "h_os_version": "{{.OperatingSystemVersion}}",
        "h_physical_disk_size": "{{.DiskSpaceMB}}",
        "h_serial_number": "{{.SystemSerialNumber}}",
        "h_cpu_clock_speed": "{{.ProcessorSpeedGHz}}",
        "h_physical_cpus": "{{.NumberofProcessors}}",
        "h_logical_cpus": "",
        "h_bios_name": "{{.BIOSDescription}}",
        "h_bios_manufacturer": "",
        "h_bios_serial_number": "",
        "h_bios_release_date": "{{.BIOSReleaseDate}}",
        "h_bios_version": "{{.SMBIOSVersion}}",
        "h_max_memory_capacity": "",
        "h_number_memory_slots": "",
        "h_net_name": "",
        "h_subnet_mask": ""



Link to comment
Share on other sites

Hi @JBasey

I don't know SCCM, however that top part is the query that the import script is running to gather the records to then import into Hornbill.

If you run the same query directly onto your SCCM database, do you see the missing laptops in the results?

It is only a select query, so it won't change any of the SCCM data by running the query, but that is the same query the script is using so if the missing laptops are not in the results that come back when you run that, then there is something in that query that is filtering out the missing laptops. 

Again I don't know SCCM so I wouldn't be able to comment on which part of the query could be filtering out the missing laptops, but I'm pretty sure it'll be the query that is filtering out the data you are looking for.

EDIT: Steve has highlighted a part of the query that may be the cause above

Link to comment
Share on other sites

Bingo.  ive now run it including the additional clause and it is not pulling those laptops.  presumably i need to find the missing chassis type and include it?

AND OASysEncl.ChassisTypes0 IN (8, 9, 10, 14) AND dbo.v_R_System.Obsolete0 = 0 ORDER BY dbo.v_R_System.ResourceID ASC

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...