asset_import.exe [ERROR] Database configuration not set

[Jörg Walther]

Hi All,
We're a long-standing customer but fairly new to asset management and asset imports. 
I got my head around importing from csv, the json syntax and structure and got that working well for several types of our assets.

I'm now trying to use asset_import directly from our on-prem (not cloud) snow database instance. 
However, I keep coming up against these errors:
2024/01/25 15:25:44 [ERROR] Database configuration not set.
2024/01/25 15:25:44 [ERROR]  [DATABASE] Database Connection String Empty. Check the SQLConf section of your configuration.

I've looked at the relevant documentation I could find and looked at some community posts along similar lines, e.g.: '13603-asset-import-tool-windows-authentication-not-working' and tried the various suggestions discussed, without luck.

So fundamentally I'm trying to run asset_import on one server connecting to the snow d/b hosted on another MSSQL server (failover cluster).

Things I've tried without success so far:
    - Looked at the various json example files for correct syntax and SQLConf
    - ran this json against JSONLINT and this came out as "JSON is valid!".
    - ran  asset_import on the remote server and the local d/b server (active cluster node) with this syntax:
asset_import.exe -dryrun=true -debug=true -file=conf_DTS-snowdb_2.json
    - I tried connecting to the d/b with IP, with and w/o FQDN, with and without the default instance specified, e.g.  "paamssqldb02\\MSSQLSERVER"
With the "server" connection string "paamssqldb02" I can connect remotely from SQL management studio ok without specifying the instance as it uses the default one.
    - Come across two different config versions, and chose the latter as it seems most appropriate to me(?): "SourceConfig" and "SQLConf"
    - For this test I'm running this with administrator credentials, which has sufficient permissions although intend to run this under a restricted service account once working.
    - The sql select query works ok in SSMS.

I've attached the json file in question.

Is anyone able to shed some light on what's wrong here?
Thanks,
Jörg

conf_DTS-snowdb_2.json

[Steve Giller]

That looks like an outdated format for the config file to me.

I would suggest reviewing the Hornbill Documentation site, ensuring that you have downloaded and configured the latest version from Github, and posting any remaining issues here.

The first line of the logs will indicate the version - the json above looks like a version 3 config, this has changed significantly for version 4.x particularly in the connection area.

[Jörg Walther]

Thanks Steve,
I was just about to upload the log file... I'm using: Hornbill Asset Import Utility v4.3.1
Not sure how I can differentiate json v3 and v4.  I tried some options with the examples included in the GitHub download, but they didn't work either for me (at least initially) so looked around and ended up with this format.

Should the d/b connectivity bit look something like this then?
If so how do I specify a remote database server?  Without knowing all the correct mandatory and optional settings it's difficult to build this w/o picking and choosing from other community articles, etc...

"SourceConfig": {
        "Source": "mssql",
        "Database": {
            "Authentication": "SQL",
            "Encrypt": false,
            "Query": "SELECT...."
        }
    },

Asset_Import_20240125152543_0.log

[Steve Giller]

The critical information is on the Authentication sub-page.

The database connection is held securely in the KeySafe (referenced by the keySafeKeyId)
There is no longer any storing of connection data in the json.

[Jörg Walther]

That's very helpful so far,
I have now created a keysafe.
However I can't see in the documentation where and how I reference this in the conf...json file.
The example conf files that come with only show   
"KeysafeKeyID": 0,
but the Keysafe entry doesn't show an 'id'. Is that it's name?
 

[Steve G]

Hi @Jörg Walther,

The KeySafe Key ID can be taken from the URL when you are on the key details form. I've added some additional information to the documentation to clarify this, which will be published in the next 10 minutes. 

Thanks,

Steve

[Jörg Walther]

Hi Steve,
Thanks for your quick response, info and documentation update!
Once you know it, it's obvious and easy. I just expected some long GUID of sorts. 
I tried it again and it works:

---- Hornbill Asset Import Utility v4.3.1 ----
[DEBUG] Flag - Config File conf_DTS-snowdb_2.json
[DEBUG] Flag - Dry Run true
[DEBUG] Flag - Concurrent 1
[MESSAGE] Successfully decrypted Hornbill instance authentication information
Current version 4.3.1 (patch) is greater than or equal to the latest release version on Github 4.3.1
Caching Application Records from Hornbill...
Connecting to Database Server: <....FQDN....>
[DATABASE] Running database query for Laptop assets. Please wait...
[DATABASE] 0 of 1 returned assets successfully retrieved ready for processing.

Not seeing any assets yet, but that'll be my select query being wrong. 

Thanks for your help!
 

[Jörg Walther]

Just as an update for info:
I've now got asset_import.exe to import from our on-prem Snow MS SQL database.  It took quite a bit of work to refine the SQL select statement (most of that work was done in SQL server management studio) and I'm sure it won't be the last iteration but this is our conf_...json so far:

{
    "KeysafeKeyID": <id redacted>,
    "LogSizeBytes": 1000000,
    "HornbillUserIDColumn": "h_user_id",
    "SourceConfig": {
        "Source": "mssql",
        "Database": {
            "Authentication": "Windows",
            "Encrypt": false,
            "Query": "WITH CTE AS (SELECT c.ComputerID, c.HostName, c.OperatingSystem, c.OSServicePack, c.Manufacturer, c.Model, c.BiosSerialNumber, c.IPAddress, c.IsPortable, c.Domain, u.UserName, cu.LastLogon,   ROW_NUMBER() OVER (PARTITION BY c.ComputerID ORDER BY cu.LastLogon DESC) AS RowNumber     FROM SnowLicenseManager.dbo.tblComputer AS c     INNER JOIN SnowLicenseManager.dbo.tblComputerUsers AS cu ON c.ComputerID = cu.ComputerID     INNER JOIN SnowLicenseManager.dbo.tblUser AS u ON cu.UserID = u.UserID ) SELECT ComputerID, HostName, OperatingSystem, OperatingSystem+' '+OSServicePack AS OSCombined, Manufacturer, Model, BiosSerialNumber, IPAddress, IsPortable, Domain, PARSENAME(REPLACE(UserName , '\\' , '.'),1) AS UserName, (CASE WHEN Domain LIKE 'AD' THEN HostName+'.ad.pvt' ELSE HostName END) AS FQDN, (CASE WHEN OperatingSystem LIKE '%Windows%' THEN 'Windows' WHEN OperatingSystem LIKE '%macOS%' THEN 'Mac' WHEN OperatingSystem LIKE 'OS X' THEN 'Mac' END) AS osType FROM CTE WHERE RowNumber = 1 AND OperatingSystem NOT LIKE 'VMware*'"
        }
    },
   "AssetTypes": [{
            "AssetType": "Laptop",
            "Query": "AND IsPortable = 1;",
            "AssetIdentifier": {
                "SourceColumn": "HostName",
                "Entity": "AssetsComputer",
                "EntityColumn": "h_name"
            }
        },
        {
            "AssetType": "Desktop",
            "Query": "AND IsPortable = 0;",
            "AssetIdentifier": {
                "SourceColumn": "HostName",
                "Entity": "AssetsComputer",
                "EntityColumn": "h_name"
            }
        }
    ],
    "AssetGenericFieldMapping": {
        "h_name": "{{.HostName}}",
        "h_asset_tag": "{{.BiosSerialNumber}}",
        "h_created_date": "",
        "h_description": "",
        "h_owned_by": "{{.UserName}}",
        "h_owned_by_name": "",
        "h_used_by": "{{.UserName}}",
        "h_used_by_name": "",
        "h_warranty_expires": "",
        "h_warranty_start": ""
    },
    "AssetTypeFieldMapping": {
        "h_name": "{{.HostName}}",
        "h_mac_address": "",
        "h_net_ip_address": "{{.IPAddress}}",
        "h_net_computer_name": "{{.FQDN}}",
        "h_net_win_domain": "{{.Domain}}",
        "h_model": "{{.Model}}",
        "h_manufacturer": "{{.Manufacturer}}",
        "h_cpu_info": "",
        "h_description": "Workstation synced from snow via asset_import.",
        "h_memory_info": "",
        "h_os_description": "{{.OperatingSystem}}",
        "h_os_service_pack": "",
        "h_os_version": "{{.OSCombined}}",
        "h_os_type": "{{.osType}}",
        "h_serial_number": "",
        "h_physical_cpus": "",
        "h_last_logged_on_user": "{{.UserName}}",
        "h_net_name": ""
    }
}