JBasey

Hi Paul, That’s blank for me. Im after exporting the software that’s installed on each PC which is discovered by SCCM and listed here:

Hi All, i have been asked by an auditor to export the software asset management data for their review. We use the software asset management bit of hornbill and have it connected via SCCM to upload and update software on each computer once per day. I can see the software assets in ./servicemanager/softwareassetmanagement/ but can see no way to export this information am i missing something? Thanks James

Same for us at Waltham Forest Council

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

Thanks both. I ran my SQL statement against our SCCM database and it does select the missing laptops so it seems likely that they are somehow not importing into Hornbill

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_id":"{{.AppID}}", "h_app_name": "{{.DisplayName0}}", "h_app_vendor":"{{.Publisher0}}", "h_app_version":"{{.Version0}}", "h_app_install_date":"{{.InstallDate0}}", "h_app_help":"", "h_app_info":"" } } } ], "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": "" } }

Hi, i would really like to know this too. Thanks JAmes

Hi, we are working on bringing all our laptop assets into Hornbill Asset manager from SCCM. One of the issues we have found is that i want to be able to record if the computer account has been disabled. I cannot do this with SCCM as it doesn't seem to hold a value for if a computer is disabled in the AD. I think its probably possible to use the LDAP import to get this data. If i were to use SCCM to update all my assets into hornbill and then use LDAP to add extra information would hornbill match on the laptop name and update the record or would it simply create duplicates from the ldap import? Thanks James

Brillant, thanks steve, we added a convert into the JSON and im pleased to report it is working. for anyone else who is trying to do this we added CONVERT(varchar, dbo.v_R_System.Last_Logon_Timestamp0,20) AS [Last_Logon_Timestamp0] into the select statment at the start of the JSON. Thanks James

Good morning, I've spotted this on a log file for a new asset: 2022/04/29 09:10:16 [ERROR] Unable to add asset: Invalid date/time format provided: [2022-04-18 18:53:08 +0000 UTC] for column h_last_logged_on so it looks like the script is now picking up the data but hornbills table cannot accept the format the SQL server is trying to provide. What format does the datetime need to be for the hornbill database?

Haha, i have found a view that contains what im looking for - dbo.v_R_System.Last_Login_Timestamp0 and that contains the last login as datetime - heres an example - 2022-04-21 13:56:33.000 What would i enter in the JSON to try and import this?

Thanks Steve, i think it is stored as a epoch value. I'm not sure there is another way to get a laptops last logged in time from SCCM so does anyone have any ideas on how i would alter the import query to do this? Has anyone else who uses the asset import tool with sccm managed to get this field populated? i would be really interested to know how you did it? Thanks James

Hi, im setting up our asset management in hornbill and have the asset import tool working and bring data into hornbill from SCCM. Im not able to get the "h_last_logged_on" field to populate from SCCM. im trying to make this show the SMS_R_System.LastLogonTimestamp from sccm so we can ceep a track of machines that have not been seen in the SCCM recently but i cannot seem to get this to populate any data: heres what i have under asset field mapping: "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": "{{.LastLogonTimestamp}}", "h_last_logged_on_user": "", "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": "" any help would be greatly appreciated. thanks all.

Hi, Our instance has single sign on so our users do not need a username/password to access service.hornbill.com We have a small number of users who this isnt suitable for, they are our employees but do not have the means to authenticate using self service. I would like to know if there is a way we can let them access service.hornbill.com which prompts them for a username password that we can manually set up for their hornbill basic user account. Is this possible? Thanks James

Hi. Yes I. Trying to use the reports in admin. Thanks