Jump to content

goDBassetImport and SCCM integration


Recommended Posts

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

 immagine.png.42c6ede23178fa1dfab4a92608442be6.png

Link to comment
Share on other sites

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. 

image.png

Let me know how you get on with this.

Cheers,

Steve

Link to comment
Share on other sites

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:

image.png

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

Link to comment
Share on other sites

  • 2 years later...

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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:

image.png.11d2562f490d1b70c1d7d06e4ddc53cd.png

 

 

 

 

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

 

image.png.586858b37530feac8695a55c8f400e57.png

 

 

 

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

 

Link to comment
Share on other sites

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:

 image.png

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

Link to comment
Share on other sites

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

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
 Share

×
×
  • Create New...