SCCM asset import missing assets when no user assigned

[MikeW]

Has anyone seen this or know how to solve - 

I have setup and successfully imported/synced assets from SCCM directly in to Hornbill but now realise I am missing some data - data that has neither a primary user or a last logged on user. It would appear that my SQL code/query will not import/sync an asset if it has neither a primary user or last logged on user. We do have a number of devices with this and would like them imported/syncing.

I have tested my code on SQL Server Management Studio. The code is correct but it misses assets that don't have a primary or last logged on user so would appear to be a SQL issue rather than Hornbill. I remove the primary user and last logged on user code then the 'missing' assets appear and can then be imported/synced.

Does anyone have any ideas how to import/query asset data that doesn't have a primary or last logged on user? I could do an import without the user code then another with it but don't really want to be doing this. I presume there must be a way to still query the data if these fields are blank. Sadly by SQL knowledge is limited. 

My query code is - 

select dbo.System_DISC.Netbios_Name0 AS [MachineName], dbo.System_DISC.User_Name0 AS [LastUserName], dbo.User_DISC.employeeID AS [LastLogged], dbo.Operating_System_DATA.Caption00 AS [OperatingSystem], dbo.Operating_System_DATA.Version00 AS [OperatingSystemVersion], dbo.Computer_System_DATA.Manufacturer00 AS [SystemManufacturer], dbo.Computer_System_DATA.Model00 AS [SystemModel], dbo.PC_BIOS_DATA.SerialNumber00 AS [ServiceTag], dbo.PC_Memory_DATA.TotalPhysicalMemory00 / (1000) / (1000) AS [MemoryGB], dbo.disk_data.Size00 * (1024) * (1024) / (1000000000) AS [HDDGB], dbo.System_Enclosure_DATA.ChassisTypes00, dbo.PC_BIOS_DATA.SMBIOSBIOSVersion00 AS [SMBIOSVersion], dbo.Logical_Disk_DATA.DriveType00, dbo.disk_data.MediaType00, (select Top 1 dbo.v_R_User.employeeID from dbo.v_UsersPrimaryMachines, dbo.v_R_User, dbo.v_R_System, dbo.User_DISC WHERE dbo.v_UsersPrimaryMachines.UserResourceID = dbo.v_R_User.ResourceID and dbo.v_UsersPrimaryMachines.MachineID = dbo.v_R_System.ResourceID and dbo.v_R_System.Name0 = dbo.System_DISC.Netbios_Name0 and dbo.v_R_User.Name0 = dbo.User_DISC.Name0) AS [PrimaryUser] from dbo.System_DISC, dbo.User_DISC, dbo.Operating_System_DATA, dbo.System_Enclosure_DATA, dbo.PC_BIOS_DATA, dbo.PC_Memory_DATA, dbo.Disk_DATA, dbo.Logical_Disk_DATA, dbo.Computer_System_DATA WHERE 1=1

AND dbo.System_Enclosure_DATA.ChassisTypes00 IN (8, 9, 10, 14, 32) and dbo.Logical_Disk_DATA.DriveType00 = 3 and dbo.disk_data.MediaType00 = 'Fixed hard disk media' and dbo.system_disc.ItemKey = dbo.Operating_System_DATA.MachineID and dbo.System_DISC.User_Name0 = dbo.User_DISC.User_Name0 and dbo.system_disc.ItemKey = dbo.Computer_System_DATA.MachineID and dbo.system_disc.ItemKey = dbo.PC_Memory_DATA.MachineID and dbo.system_disc.ItemKey = dbo.PC_BIOS_DATA.MachineID and dbo.system_disc.ItemKey = dbo.Logical_Disk_DATA.MachineID and dbo.system_disc.ItemKey = dbo.Disk_DATA.MachineID and dbo.system_disc.ItemKey = dbo.System_Enclosure_DATA.MachineID

[Steve G]

Hi @MikeW,

My SCCM database looks very different to yours as I'm missing a number of those tables and columns, but I expect the issue you're experiencing is because you're using inner joins in your query, when you actually want to use left joins...

So, as a very stripped-down example, the following is how you would return all assets. For each row, if a match from the left-joined user_disc table is found, then LastLogged will be populated with the value of employeeid from the matched record, and if a match is not found then the LastLogged value will be NULL:

SELECT dbo.system_disc.netbios_name0 AS [MachineName], 
       dbo.system_disc.user_name0 AS [LastUserName], 
       dbo.user_disc.employeeid AS [LastLogged]
FROM   dbo.system_disc
LEFT JOIN dbo.user_disc ON dbo.system_disc.user_name0 = dbo.user_disc.user_name0

I hope this helps

Cheers,

Steve

[MikeW]

@Steve G

Thanks for the help, worked a treat!

I now have my missing assets.