SCCM AssetSetup Questions

[Kevin Kennedy]

@Steve Giller @Giuseppe Iannacone @JBasey

Hello gentlemen.  My Company is working on setting up Asset Import/syncing from SCCM into Hornbill.  You have either developed this tool, or have implemented it in your instances.  I had a couple of questions for you.

1. It seems to me that the tool is written so that it must be loaded onto the SCCM server itself?  Is this correct?  If so, does it need to run on the drive that the SQL database is setup on, or is it fine on the boot drive of that server?  My SCCM Admin has concerns that the tool "might screw up his highly tuned SCCM server".

2. Do any ports need to be opened on the Firewall to allow the tool to connect with Hornbill?  If so, which ones?

 

Thank you in advance for generously sharing your wisdom with me!

 

Kevin K.   

[SamS]

Hi @Kevin Kennedy,

1. The utility needs access to the SCCM database, it does NOT need to reside on the SCCM server itself.
2. The utility "talks" to Hornbill over HTTPS (by default port 443). IF you are using proxy servers, then you would need to look into using the environment settings HTTP_PROXY & HTTPS_PROXY (details should be in the utilities' documentation on the wiki.

[Kevin Kennedy]

@SamS Thanks for the Quick reply. Follow up Question...

How do I tell the import utility where the SQL database is located?

Kevin K

[Steve Giller]

9 minutes ago, Kevin Kennedy said:

How do I tell the import utility where the SQL database is located?

This would be part of the KeySafe Key you set up, see the Instance Config section of the documentation.

[Giuseppe Iannacone]

@Kevin Kennedy in my case I had trouble to directly connect to the sccm database, so i decided to use an odbc connection and currently the import tool is retrieve the data via this odbc connection.
 

[Kevin Kennedy]

Thanks so much for the help thus far.  I have now gotten to the point where the users are being cached from Hornbill, but I am getting an error on the Site caching.  I know it is likely something simple that I have not done or incorrectly done causing this, but cannot figure out what.  Any ideas?

 

Here is the actual text from the log file

2023/01/18 16:01:21 Loading Config File: c:\Hornbill\SCCMAssets/db_sccm.json
2023/01/18 16:01:23 Current binary is the latest version: 3.3.3
2023/01/18 16:01:24 ---- XMLMC Database Asset Import Utility v3.3.3 ----
2023/01/18 16:01:24 [DEBUG] Flag - Config File db_sccm.json
2023/01/18 16:01:24 [DEBUG] Flag - Dry Run true
2023/01/18 16:01:24 [DEBUG] Flag - Concurrent 10
2023/01/18 16:01:27 Caching User Records from Hornbill...
2023/01/18 16:01:28 Loading Users from Hornbill
2023/01/18 16:01:28 getUserAccountsList Count: 1977
2023/01/18 16:01:28 Loading User Accounts List Offset: 0

2023/01/18 16:01:29 Loading User Accounts List Offset: 1000

2023/01/18 16:01:29 Users Loaded: 1977
2023/01/18 16:01:29 Caching Site Records from Hornbill...
2023/01/18 16:01:30 [ERROR] Unable to Read Sites List json: cannot unmarshal number into Go struct field .params.count of type string
2023/01/18 16:01:30 Caching Application Records from Hornbill...
2023/01/18 16:01:31 [ERROR] Database configuration not set.
2023/01/18 16:01:31 [ERROR]  [DATABASE] Database Connection String Empty. Check the SQLConf section of your configuration.

[Kevin Kennedy]

@Steve Goldthorpe Any thoughts in the cause of the above? e.g. What stupid thing did Kevin forget to do...

[Giuseppe Iannacone]

@Kevin Kennedy
do you think you can share the conf file? (of course first mask the sensitive information)

[Kevin Kennedy]

@Giuseppe Iannacone

It is below, but the only lines I changed from the example are the first 3.  Thanks in advance for any advice anyone can offer.

 

 

{
    "APIKey": "********************************",
    "InstanceId": "nninc",
    "KeysafeKeyID": 3     ,
    "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], 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": "Desktop",
            "OperationType": "Both",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "Query": "AND OASysEncl.ChassisTypes0 IN (3, 4, 5, 6, 7, 12, 13, 15, 16) AND dbo.v_R_System.Obsolete0 = 0 ORDER BY dbo.v_R_System.ResourceID ASC",
            "AssetIdentifier": {
                "SourceColumn": "MachineName",
                "Entity": "Asset",
                "EntityColumn": "h_name",
                "SourceContractColumn": "",
                "SourceSupplierColumn": ""
            },
            "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":""
                }
            }
        },
        {
            "AssetType": "Server",
            "OperationType": "Both",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "Query": "AND OASysEncl.ChassisTypes0 IN (2, 17, 18, 19, 20, 21, 22, 23) AND dbo.v_R_System.Obsolete0 = 0 ORDER BY dbo.v_R_System.ResourceID ASC",
            "AssetIdentifier": {
                "SourceColumn": "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":""
                }
            }
        },
        {
            "AssetType": "Virtual Machine",
            "OperationType": "Both",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "Query": "AND OASysEncl.ChassisTypes0 = 1 AND dbo.v_R_System.Obsolete0 = 0 ORDER BY dbo.v_R_System.ResourceID ASC",
            "AssetIdentifier": {
                "SourceColumn": "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":""
                }
            }
        },
        {
            "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": {
                "SourceColumn": "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_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": "{{.SystemModel}}",
        "h_memory_info": "{{.MemoryKB}}",
        "h_os_description": "{{.OperatingSystem}}",
        "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}}"
    }
}

[SamS]

Hi @Kevin Kennedy,

The error message suggests that the DB connection is not functioning (i.e. it is already beyond the "Sites" bit).

I would expect the "Database" section in the configuration file to have the "Authentication" set to "SQL" of "Windows". I'd also double-check the settings in KeySafe for the ID given.

[Kevin Kennedy]

@SamS

@James Ainsworth helped me solve the connection problem, which was a problem in my KeySafe entry. 

Thanks to everyone who offered assistance, advice, or anything else toward solving this problem!

Kevin K.