Jump to content

SCCM asset import - h_last_logged_on


Recommended Posts

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.

Link to comment
Share on other sites

@JBasey I'm not familiar with the SCCM schema, but LastLogonTimestamp implies to me that the value will be an INT or BIGINT (i.e. an epoch-style value) where h_last_logon requires a DATETIME, so it may be that you need to use a different field, or alter the format of the Timestamp within the SCCM query.

Link to comment
Share on other sites

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?






Link to comment
Share on other sites

@JBasey I think there may be a SQL command to do that for you, but without knowing the exact data type in SCCM I'm not sticking my neck out with even a guess at the code.

For example, MySQL has a FROM_UNIXTIME() function, so I'd expect MSSQL to have something along those lines, but knowing Microsoft their Epoch may not be the same as the one everyone else uses!

Link to comment
Share on other sites

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?  


Link to comment
Share on other sites

That would depend on the SCCM Query but looking at the first post, and assuming that dbo.v_R_System.Last_Login_Timestamp0 is returned in the query I would suggest changing

"h_last_logged_on": "{{.LastLogonTimestamp}}",


"h_last_logged_on": "{{.Last_Login_Timestamp0}}",

and test that on a small set of Assets.

Link to comment
Share on other sites

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?


Link to comment
Share on other sites

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.




Link to comment
Share on other sites

Hi @JBasey

We are looking at bring in assets via SCCM so this would be really helpful.

Its very early days for us but good to see you are getting the mappings and info you need.


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...