goDBassetImport and SCCM integration

[Giuseppe Iannacone]

we are succesfully using the open tool goDBassetImport and we are currently syncing some information from SCCM to Hornbill asset manager.
There's only one issue, if we select an asset to be as shared, the settings is overwritten when the next sync with sccm occurs.
How can be this avoid? is there the possibility to not overwrite the asset that are manually set to be shared?
thank you

 

[Giuseppe Iannacone]

@Steve G & @Marc Littlefair

can you help me with this?

[Steve G]

Hi @Giuseppe Iannacone,

I have a couple of ideas for this, will need some changes to the import tool though, leave it with me and I'll get back to you.

Cheers,

Steve

[Giuseppe Iannacone]

@Steve G I'll stay tuned!
Thank you!

[Steve G]

Hi @Giuseppe Iannacone,

We've just released v1.10.0 of the Database Asset Import Tool, which can be downloaded from Github.

In the AssetTypes objects in the tool config, there's a new boolean property, PreserveShared. If this is set to false then the tool will function as the previous versions (will overwrite the Used By fields of assets, where appropriate). If it's set to true, then any assets it finds that are set to Shared will not have the Used By fields updated. 

Let me know how you get on with this.

Cheers,

Steve

[Giuseppe Iannacone]

@Steve G thank you very much for the quick feedback (as usual)
I will test it as soon as possible and return with a feedback.

[Giuseppe Iannacone]

@Steve G I forgot to add the dry-run option and now i have a lot of duplicate... beside the issue it self, why it is happening that we now have duplicate?

If I'm not wrong if the "h_serial_number" is matched it should updated the assed and not create a new one, right?

[Steve G]

Hi @Giuseppe Iannacone,

The code that works out if there's an asset to update hasn't actually changed... I've just tested it to be sure and it's finding and updating records as expected:

Is there anything useful in the import log to tell us what's happening? Are you sure the serial numbers in the asset records in Hornbill match those in the source data?

Thanks,

Steve

[Giuseppe Iannacone]

@Steve G I've looked into the log file and there's nothing very useful to my eyes... would you like me to send you a copy privately via email?

[Steve G]

Hi @Giuseppe Iannacone,

Sure, if you send your log and config file to me I'll give them a once-over. I'll send you my email address in a personal message.

Thanks,

Steve

[Guest Paul Alexander]

HI

Was the duplication problem sorted out for this please?

I've just updated from an old version of the import tool to the latest one, and we also use the SystemSerialNumber as the unique identifier, but after running the tool (thankfully remembering to add the -dryrun=true option!) it is creating lots of duplicates.

thanks

[Steve G]

Hi @Paul Alexander,

If memory serves, this was a data issue rather than defect in the tool - a number of records in the SCCM dataset had no serial number so matching Hornbill asset records could not be found. If you could check that first, and if you're still seeing duplicates then feel free to post your config here (minus any API keys, passwords etc) and I'll cast my eye over it.

Cheers,

Steve

[Guest Paul Alexander]

Hi @Steve G

I did do a check for the duplicates in the asset db, and it definitely looks like it's not doing something that it should (or the other way around!!)

So - here's a copy of our json script (I've marked the bit which looks for the serial number as the identifier): 

"APIKey": "??",
    "InstanceId": "??",
    "KeysafeKeyID": ??,
    "LogSizeBytes": 1000000,
    "HornbillUserIDColumn": "h_user_id",
    "SourceConfig": {
        "Source": "mssql",
        "Database": {
            "Authentication": "SQL",
            "Encrypt": false,
            "Query": "SELECT dbo.v_R_System.ResourceID AS [AssetID], 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], dbo.v_GS_Physical_DISK.SerialNumber0 AS [DiskSerNo], 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_GS_Physical_Disk ON dbo.v_GS_Physical_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",
            "Query": "AND OASysEncl.ChassisTypes0 IN (8, 9, 10, 14) AND dbo.v_R_System.Obsolete0 = 0 ORDER BY dbo.v_R_System.ResourceID ASC",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "AssetIdentifier": {
                "SourceColumn": "SystemSerialNumber",
                "Entity": "AssetsComputer",
                "EntityColumn": "h_serial_number"
            }
        }
    ],
    "AssetGenericFieldMapping": {
        "h_name": "{{.MachineName}}",
        "h_site": "{{.SiteName}}",
        "h_asset_tag": "{{.MachineName}}",
        "h_description": "{{.MachineName}} ({{.SystemModel}})",
        "h_owned_by": "{{.UserName}}",
        "h_used_by": "{{.UserName}}"
       },
    "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": "{{.MachineName}} ({{.SystemModel}})",
        "h_last_logged_on": "{{.ChassisDate}}",
        "h_last_logged_on_user": "{{.UserName}}",
        "h_memory_info": "{{.MemoryKB}}",
        "h_os_description": "{{.OperatingSystem}}",
        "h_os_serial_number": "{{.DiskSerNo}}",
        "h_os_service_pack": "{{.ServicePackVersion}}",
        "h_os_version": "{{.OperatingSystemVersion}}",
        "h_physical_disk_size": "{{.DiskSpaceMB}}",
        "h_serial_number": "{{.SystemSerialNumber}}",
        "h_cpu_clock_speed": "{{.ProcessorSpeedGHz}}",
        "h_physical_cpus": "{{.NumberofProcessors}}",
        "h_bios_name": "{{.BIOSDescription}}",
        "h_bios_release_date": "{{.BIOSReleaseDate}}",
        "h_bios_version": "{{.SMBIOSVersion}}"        
    }
}

 

Here's a couple of items from the log file:

2022/06/01 10:44:52 [DEBUG] Processing Asset: 5CG8010VKB
2022/06/01 10:44:52 [DEBUG] Create Asset: 5CG8010VKB
2022/06/01 10:44:52 [DEBUG] API Create XML: 

 

But there is already an asset in the db with that serial number:

 

 

 

 

However, SOME of the assets ARE being picked up as already being in the db:

 

2022/06/01 10:44:53 [DEBUG] Processing Asset: 5CG1310CNL
2022/06/01 10:44:53 [DEBUG] Asset match found, no software inventory updates required
2022/06/01 10:44:53 [DEBUG] Update Asset: 5CG1310CNL

 

 

 

 

So...I'm not too sure what's going on? Any help would be appreciated (although I'm on holiday after today for a week, so there's no rush  )

thanks

 

[Steve G]

Hi @Paul Alexander,

The config is correct, I've just run it against my SCCM database twice (once for create, then one to test for dupes), and I'm getting no duplicates in the 500 records I have that match your clauses:

 

Wondering if there's a whitespace issue in play in your data. Might be worth trimming the column in your query to remove any leading or trailing whitepace characters, just to be sure:

• SQL Server 2017 onwards: TRIM(dbo.v_GS_PC_BIOS.SerialNumber0) AS [SystemSerialNumber]
• Other versions: LTRIM(RTRIM(dbo.v_GS_PC_BIOS.SerialNumber0)) AS [SystemSerialNumber]

Hope this helps,

Steve

[Guest Paul Alexander]

Hi @Steve G

Thanks for looking at this - I've tried the 'trim' option, but I'm still getting assets being created where they shouldn't.

I would imagine that this will need to be logged as a support ticket, but, as I said earlier, I'm away for a week so, if that's the case, I'll log it when I get back....unless you have any more ideas to try? 

 

thanks

 

Paul