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

Hi, I'm trying to create a report that shows me the number of minutes incidents have been open, adjusted for our SLA clock. For example if i log an incident at 09:00 and its resolved at 09:15 i want a report that returns the value of 15 minutes. If a request was logged at 08:00 and resolved at 09:15 but our sla clock is 09:00 to 17:00 i want it to return a value of 15 mins as the request was only open for 15 minutes of sla time. Im assuming the time elapsed must be stored or calculated by the system somewhere but cannot figure out how to access it in a report. Does anyone have any ides? Thanks all James

To be clearer, i'm talking about users of the Service.Hornbill.com portal, not the customer one.

Hi, I'm trying to get live chat set up for this weekend. I want any of our basic users to be able to chat with our IT staff (full users). While testing this seems to work - i have assigned my own account the portal chat session user role and i can see the chat popup and speak to analysts. I have now added the portal chat session user role to the Service account under home>system>manage portals>portal accounts but my basic users cannot see the chat popup or link. Do i need to do anything else?

Hi, could you let me know if live chat only works for people who have an account in your instance of hornbill or can it be used at the customer portal by any visitor without a login? Thanks James

Hi All, I dont seem to be able to change the order of my feedback questions: I'd like the answers to go Excellent > Good > ok > poor > awful. When i drag them to the correct locations and click update it seems to work but when i go back to check they have reverted to awful > poor > ok > Good > excelent Thanks James

Hi all, Any update on this issue?

Any update on this? i use these charts all the time.

Same here too. I get the same loading message as above. The request views work fine, the charts dont load.

Hi, I have been asked to see if i can come up with a way to determine the average duration of incidents resolved over a given time period (the last 6 months) in minutes during working hours. Our Incident SLA timer for all priorities is fix within 9 hours 15 mins (8am to 5:15 pm) monday to friday. Our working time calendar is also set up for 8am to 5:15pm. Is there any easy way to do this in reporting? i need to be able to say "the average time to fix an incident during the period was ???? minutes" Any ideas? Thanks

Hi @Pamela Im not sure if i'm following you For example, if i ran a "last 30 days" report today on the 4th of June, would it give me: a: 01 May to 31 May (Previous calendar month) b: 05 May to 04 June (last 30 days from the data the dash report is run) What i'm after is something that always returns the previous calendar month, not the last 30 days. Thanks James