DBAsset Import SQL issue

[QEHNick]

We're using the latest DBAsset Import tool (the one which allows use of storing credentials in KeySafe).

The out of the box JSON file for Lansweeper (taken from the new version) has been modified to include our instance ID and the keysafe number all that required stuff.

On executing (Dryrun), the process fails with the error "Incorrect syntax near the keyword 'WHERE'." for each and every WHERE statement.

These statements are the same as the previous version (which worked fine), in fact, all of the out of the box statements are the same.

I include the file here (redacted) for evidentiary purposes (it won't allow me to attach it, so..big splurge of text coming up at the end).

Any suggestions would be welcome. Thank you!

 

{
    "APIKey": "OUR API KEY HERE",
    "InstanceId": "OUR INSTANCE ID HERE",
    "KeysafeKeyID": KEYSAFE ID HERE,
    "LogSizeBytes": 1000000,
    "HornbillUserIDColumn": "h_user_id",
    "SourceConfig": {
        "Source": "mssql",
        "Database": {
            "Authentication": "SQL",
            "Encrypt": false,
            "Query": "SELECT  at.AssetType AS AssetTypeID,  at.AssetTypename AS AssetTypeName,  a.AssetID,  a.AssetUnique,  a.Domain, a.Username AS ADUserID,  a.FQDN,  a.IPAddress,  a.SiteID,  CASE WHEN at.AssetTypename = 'Windows' THEN os.Caption WHEN at.AssetTypename = 'Apple Mac' THEN mos.SystemVersion END AS OperatingSystem,  a.SP,  convert(varchar, a.Firstseen, 20) as FirstSeen,  a.Description,  a.AssetName,  a.Mac,  a.Uptime,  a.Memory,  a.NrProcessors,  a.Processor,  convert(varchar, a.LastChanged, 20) as LastChanged,  os.Caption,  os.ProductType,  convert(varchar, ac.PurchaseDate, 20) as PurchaseDate,  convert(varchar, ac.Warrantydate, 20) as Warrantydate,  ac.Manufacturer,  ac.Model,  ac.Serialnumber,  u.Displayname AS ADUserName  FROM dbo.tblAssets AS a  LEFT JOIN dbo.tsysAssetTypes at ON a.Assettype = at.AssetType  LEFT JOIN dbo.tblOperatingsystem os ON a.AssetID = os.AssetID  LEFT JOIN dbo.tblMacOSInfo mos ON a.AssetID = mos.AssetID LEFT JOIN dbo.tblAssetCustom ac ON a.AssetID = ac.AssetID  LEFT JOIN lansweeperdb.dbo.tblADusers u ON a.Username = u.Username"
        }
    },
    "AssetTypes": [{
            "AssetType": "Laptop",
            "OperationType": "Both",
            "Query": "WHERE at.AssetTypename = 'Windows' AND os.ProductType = 1",
            "AssetIdentifier": {
                "SourceColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name",
                "SourceContractColumn": "",
                "SourceSupplierColumn": ""
            }
        },
        {
            "AssetType": "Desktop",
            "OperationType": "Both",
            "Query": "WHERE (at.AssetTypename = 'Windows' AND os.ProductType = 1",
            "AssetIdentifier": {
                "SourceColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            }
        },
        {
            "AssetType": "Server",
            "OperationType": "Both",
            "Query": "WHERE os.ProductType IN (2, 3)",
            "AssetIdentifier": {
                "SourceColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            }
        }
    ],
    "AssetGenericFieldMapping": {
        "h_name": "{{.AssetName}}",
        "h_asset_tag": "{{.AssetName}}",
        "h_created_date": "{{.Firstseen}}",
        "h_description": "{{.Description}}",
        "h_owned_by": "{{.ADUserID}}",
        "h_owned_by_name": "{{.ADUserName}}",
        "h_used_by": "{{.ADUserID}}",
        "h_used_by_name": "{{.ADUserName}}",
        "h_warranty_expires": "{{.Warrantydate}}",
        "h_warranty_start": "{{.PurchaseDate}}"
    },
    "AssetTypeFieldMapping": {
        "h_name": "{{.AssetName}}",
        "h_mac_address": "{{.Mac}}",
        "h_net_ip_address": "{{.IPAddress}}",
        "h_net_computer_name": "{{.FQDN}}",
        "h_net_win_domain": "{{.Domain}}",
        "h_model": "{{.Model}}",
        "h_manufacturer": "{{.Manufacturer}}",
        "h_cpu_info": "{{.Processor}}",
        "h_description": "{{.Description}}",
        "h_memory_info": "{{.Memory}}",
        "h_os_description": "{{.OperatingSystem}}",
        "h_os_service_pack": "{{.ServicePackVersion}}",
        "h_os_version": "{{.OSCode}}",
        "h_serial_number": "{{.Serialnumber}}",
        "h_physical_cpus": "{{.NrProcessors}}",
        "h_net_name": "{{.FQDN}}"
    }
}

[James Ainsworth]

Hi @QEHNick

Thanks for your post.  In the second WHERE statement, this open bracket seems to me to be out of place.  You could try removing this?

[QEHNick]

Thanks for pointing that out, that's on the "out of the box" script as well so HB may need to correct that; changing it didn't help I'm afraid.

 

[DATABASE] Running database query for Laptop assets. Please wait...
[ERROR]  [DATABASE] Database Query Error: mssql: Incorrect syntax near the keyword 'WHERE'.
[DATABASE] Running database query for Desktop assets. Please wait...
[ERROR]  [DATABASE] Database Query Error: mssql: Incorrect syntax near the keyword 'WHERE'.
[DATABASE] Running database query for Server assets. Please wait...
[ERROR]  [DATABASE] Database Query Error: mssql: Incorrect syntax near the keyword 'WHERE'.

[James Ainsworth]

Hi @QEHNick

I'll have another look.  Nothing else jumping out at the moment that I can see.  

[Steve Giller]

Looking on the wiki and in the zip file on GitHub, the out of the box script for that section is:
 

        {
            "AssetType": "Desktop",
            "OperationType": "Both",
            "Query": "WHERE (at.AssetTypename = 'Windows' AND os.ProductType = 1 AND ac.Model = 'Precision WorkStation T5500') OR at.AssetTypename = 'Apple Mac'",
            "AssetIdentifier": {
                "SourceColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            }

so it looks like there's a truncation issue here.

[QEHNick]

Has anyone else used this tool successfully with Lansweeper? Can you share your script at all so I can work out what is wrong with ours?

Cheers.

[James Ainsworth]

Hi @QEHNick

As there are only 3 WHERE statements, are you able to remove and test one at a time so that we can pinpoint which one is causing the issue?

[QEHNick]

I've reverted to the old version which has exactly the same WHERE statements, and it works.

It's not the statements.

[Steve Giller]

1 hour ago, QEHNick said:

I've reverted to the old version which has exactly the same WHERE statements

Can you post the old version that you're using so we can check the config, please?

[QEHNick]

Here you go.

{
    "APIKey": "KEYHERE",
    "InstanceId": "INSTANCEHERE",
    "Version":"",
    "LogSizeBytes": 1000000,
    "SQLConf": {
        "Driver": "mssql",
        "Server": "SERVERHERE,
        "Database": "DBHERE",
        "Authentication": "SQL",
        "UserName": "",
        "Password": "",
        "Port": 1433,
        "Encrypt": true,
       "Query": "SELECT  at.AssetType AS AssetTypeID,  at.AssetTypename AS AssetTypeName,  a.AssetID,  a.AssetUnique,  a.Domain, a.Username AS ADUserID,  a.FQDN,  a.IPAddress,  a.SiteID,  CASE WHEN at.AssetTypename = 'Windows' THEN os.Caption WHEN at.AssetTypename = 'Apple Mac' THEN mos.SystemVersion END AS OperatingSystem,  a.SP,  convert(varchar, a.Firstseen, 20) as FirstSeen,  a.Description,  a.AssetName,  a.Mac,  a.Uptime,  a.Memory,  a.NrProcessors,  a.Processor,  convert(varchar, a.LastChanged, 20) as LastChanged,  os.Caption,  os.ProductType,  convert(varchar, ac.PurchaseDate, 20) as PurchaseDate,  convert(varchar, ac.Warrantydate, 20) as Warrantydate,  ac.Manufacturer,  ac.Model,  ac.Serialnumber,  u.Displayname AS ADUserName  FROM dbo.tblAssets AS a  LEFT JOIN dbo.tsysAssetTypes at ON a.Assettype = at.AssetType  LEFT JOIN dbo.tblOperatingsystem os ON a.AssetID = os.AssetID  LEFT JOIN dbo.tblMacOSInfo mos ON a.AssetID = mos.AssetID LEFT JOIN dbo.tblAssetCustom ac ON a.AssetID = ac.AssetID  LEFT JOIN lansweeperdb.dbo.tblADusers u ON a.Username = u.Username"
    },
    "AssetTypes": [{
            "AssetType": "Laptop",
            "OperationType": "Both",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "Query": "WHERE at.AssetTypename = 'Windows' AND os.ProductType = 1",
            "AssetIdentifier": {
                "DBColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name",
                "DBContractColumn": "",
                "DBSupplierColumn": ""
            }
        },
        {
            "AssetType": "Desktop",
            "OperationType": "Both",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "Query": "WHERE at.AssetTypename = 'Windows' AND os.ProductType = 1",
            "AssetIdentifier": {
                "DBColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            }
        },
        {
            "AssetType": "Server",
            "OperationType": "Both",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "Query": "WHERE os.ProductType IN (2, 3)",
            "AssetIdentifier": {
                "DBColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            }
        }
    ],
    "AssetGenericFieldMapping": {
        "h_name": "{{.AssetName}}",
        "h_site": "=",
        "h_asset_tag": "{{.AssetName}}",
        "h_acq_method": "",
        "h_actual_retired_date": "",
        "h_beneficiary": "",
        "h_building": "",
        "h_company_name": "",
        "h_cost": "",
        "h_cost_center": "",
        "h_country": "",
        "h_created_date": "{{.Firstseen}}",
        "h_deprec_method": "",
        "h_deprec_start": "",
        "h_description": "{{.Description}}",
        "h_disposal_price": "",
        "h_disposal_reason": "",
        "h_floor": "",
        "h_geo_location": "",
        "h_invoice_number": "",
        "h_location": "",
        "h_location_type": "",
        "h_maintenance_cost": "",
        "h_maintenance_ref": "",
        "h_notes": "{{.ADUserID}}",
        "h_operational_state": "",
        "h_order_date": "",
        "h_order_number": "",
        "h_owned_by": "{{.ADUserID}}",
        "h_owned_by_name": "{{.ADUserName}}",
        "h_product_id": "",
        "h_received_date": "",
        "h_residual_value": "",
        "h_room": "",
        "h_scheduled_retire_date": "",
        "h_supplier_id": "",
        "h_supported_by": "",
        "h_used_by": "{{.ADUserID}}",
        "h_used_by_name": "{{.ADUserName}}",
        "h_version": "",
        "h_warranty_expires": "{{.Warrantydate}}",
        "h_warranty_start": "{{.PurchaseDate}}"
    },
    "AssetTypeFieldMapping": {
        "h_name": "{{.AssetName}}",
        "h_mac_address": "{{.Mac}}",
        "h_net_ip_address": "{{.IPAddress}}",
        "h_net_computer_name": "{{.FQDN}}",
        "h_net_win_domain": "{{.Domain}}",
        "h_model": "{{.Model}}",
        "h_manufacturer": "{{.Manufacturer}}",
        "h_cpu_info": "{{.Processor}}",
        "h_description": "{{.Description}}",
        "h_last_logged_on": "",
        "h_last_logged_on_user": "",
        "h_memory_info": "{{.Memory}}",
        "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": "{{.OSCode}}",
        "h_physical_disk_size": "",
        "h_serial_number": "{{.Serialnumber}}",
        "h_cpu_clock_speed": "",
        "h_physical_cpus": "{{.NrProcessors}}",
        "h_logical_cpus": "",
        "h_bios_name": "",
        "h_bios_manufacturer": "",
        "h_bios_serial_number": "",
        "h_bios_release_date": "",
        "h_bios_version": "",
        "h_max_memory_capacity": "",
        "h_number_memory_slots": "",
        "h_net_name": "{{.FQDN}}",
        "h_subnet_mask": ""
    }
}

[Steve Giller]

@QEHNick The config you first posted has:

On 3/25/2022 at 5:07 PM, QEHNick said:

            "AssetType": "Desktop",
            "OperationType": "Both",
            "Query": "WHERE (at.AssetTypename = 'Windows' AND os.ProductType = 1",
            "AssetIdentifier": {
                "SourceColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name"

Which includes the stray ( after the WHERE

The most recent config has:

31 minutes ago, QEHNick said:

            "AssetType": "Desktop",
            "OperationType": "Both",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "Query": "WHERE at.AssetTypename = 'Windows' AND os.ProductType = 1",
            "AssetIdentifier": {
                "DBColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
 

Which does not have the bracket and explains why the first failed.

[QEHNick]

It also failed after I removed the bracket.

On 3/28/2022 at 1:49 PM, QEHNick said:

Thanks for pointing that out, that's on the "out of the box" script as well so HB may need to correct that; changing it didn't help I'm afraid.

 

[DATABASE] Running database query for Laptop assets. Please wait...
[ERROR]  [DATABASE] Database Query Error: mssql: Incorrect syntax near the keyword 'WHERE'.
[DATABASE] Running database query for Desktop assets. Please wait...
[ERROR]  [DATABASE] Database Query Error: mssql: Incorrect syntax near the keyword 'WHERE'.
[DATABASE] Running database query for Server assets. Please wait...
[ERROR]  [DATABASE] Database Query Error: mssql: Incorrect syntax near the keyword 'WHERE'.

 

[Steve Giller]

40 minutes ago, QEHNick said:

Thanks for pointing that out, that's on the "out of the box" script as well

Can you point me to the "out of the box" script you're referring to, as the ones I have checked (on the wiki and in the zip file on GitHub) do not have that stray bracket (they do have the bracket, but they also have a closing bracket so it's not stray) so I'd need to know which script/file to request an update for.

[QEHNick]

Ignore that, it's a red herring as this was a left-over from editing it.

[QEHNick]

The original post is still an issue. Spoken to Bob, who will escalate - thanks Bob!

[SamS]

Hi @QEHNick

There should be a line in the log file which gives the full SQL query being run (per asset type).

[DATABASE] Query for [[AssetType]] assets: ...

What is the query stated there...

This log entry should be in between the two lines (per type) you provided - which will have been visible on screen.

[QEHNick]

            "Query": "SELECT  at.AssetType AS AssetTypeID,  at.AssetTypename AS AssetTypeName,  a.AssetID,  a.AssetUnique,  a.Domain, a.Username AS ADUserID,  a.FQDN,  a.IPAddress,  a.SiteID,  CASE WHEN at.AssetTypename = 'Windows' THEN os.Caption WHEN at.AssetTypename = 'Apple Mac' THEN mos.SystemVersion END AS OperatingSystem,  a.SP,  convert(varchar, a.Firstseen, 20) as FirstSeen,  a.Description,  a.AssetName,  a.Mac,  a.Uptime,  a.Memory,  a.NrProcessors,  a.Processor,  convert(varchar, a.LastChanged, 20) as LastChanged,  os.Caption,  os.ProductType,  convert(varchar, ac.PurchaseDate, 20) as PurchaseDate,  convert(varchar, ac.Warrantydate, 20) as Warrantydate,  ac.Manufacturer,  ac.Model,  ac.Serialnumber,  u.Displayname AS ADUserName  FROM dbo.tblAssets AS a  LEFT JOIN dbo.tsysAssetTypes at ON a.Assettype = at.AssetType  LEFT JOIN dbo.tblOperatingsystem os ON a.AssetID = os.AssetID  LEFT JOIN dbo.tblMacOSInfo mos ON a.AssetID = mos.AssetID LEFT JOIN dbo.tblAssetCustom ac ON a.AssetID = ac.AssetID  LEFT JOIN lansweeperdb.dbo.tblADusers u ON a.Username = u.Username"
 

[SamS]

Hi @QEHNick,

I appreciate that that is the Query from the configuration file.

I am looking for the query from the .log file. You will find that in the "log"-folder alongside the .exe.

The log file will contain the full SQL as was sent to the DB Server.

I can see from the configuration files you have posted what SQL is supposed to be there, I want to confirm the actual SQL matches expectations.

[QEHNick]

Cheers Sam, do you mean this?

 

2022/03/28 13:48:21 Loading Config File: CONFIG FILE NAME
2022/03/28 13:48:22 Current binary is the latest version: 3.0.0
2022/03/28 13:48:22 ---- XMLMC Database Asset Import Utility v3.0.0 ----
2022/03/28 13:48:22 [DEBUG] Flag - Config File CONFIG FILE NAME
2022/03/28 13:48:22 [DEBUG] Flag - Dry Run true
2022/03/28 13:48:22 [DEBUG] Flag - Concurrent 1
2022/03/28 13:48:23 Caching User Records from Hornbill...
2022/03/28 13:48:23 Loading Users from Hornbill
2022/03/28 13:48:23 getUserAccountsList Count: 7610
2022/03/28 13:48:23 Loading User Accounts List Offset: 0

2022/03/28 13:48:23 Loading User Accounts List Offset: 1000

2022/03/28 13:48:24 Loading User Accounts List Offset: 2000

2022/03/28 13:48:24 Loading User Accounts List Offset: 3000

2022/03/28 13:48:24 Loading User Accounts List Offset: 4000

2022/03/28 13:48:25 Loading User Accounts List Offset: 5000

2022/03/28 13:48:25 Loading User Accounts List Offset: 6000

2022/03/28 13:48:25 Loading User Accounts List Offset: 7000

2022/03/28 13:48:25 Users Loaded: 7610
2022/03/28 13:48:25 Caching Application Records from Hornbill...
2022/03/28 13:48:26 Connecting to Database Server: OUR SERVER NAME
2022/03/28 13:48:26  
2022/03/28 13:48:26 [DATABASE] Running database query for Laptop assets. Please wait...
2022/03/28 13:48:26 [DATABASE] Query for Laptop assets: WHERE at.AssetTypename = 'Windows' AND os.ProductType = 1
2022/03/28 13:48:26 [ERROR]  [DATABASE] Database Query Error: mssql: Incorrect syntax near the keyword 'WHERE'.
2022/03/28 13:48:26  
2022/03/28 13:48:26 [DATABASE] Running database query for Desktop assets. Please wait...
2022/03/28 13:48:26 [DATABASE] Query for Desktop assets: WHERE at.AssetTypename = 'Windows' AND os.ProductType = 1
2022/03/28 13:48:26 [ERROR]  [DATABASE] Database Query Error: mssql: Incorrect syntax near the keyword 'WHERE'.
2022/03/28 13:48:26  
2022/03/28 13:48:26 [DATABASE] Running database query for Server assets. Please wait...
2022/03/28 13:48:26 [DATABASE] Query for Server assets: WHERE os.ProductType IN (2, 3)
2022/03/28 13:48:26 [ERROR]  [DATABASE] Database Query Error: mssql: Incorrect syntax near the keyword 'WHERE'.
2022/03/28 13:48:26 -=-=-= Summary =-=-=-
2022/03/28 13:48:26 Created: 0
2022/03/28 13:48:27 Create Skipped: 0
2022/03/28 13:48:27 Create Failed: 0
2022/03/28 13:48:27 Updated: 0
2022/03/28 13:48:27 Update Skipped: 0
2022/03/28 13:48:27 Update Failed: 0
2022/03/28 13:48:27 Update Extended Record Skipped: 0
2022/03/28 13:48:27 Update Extended Record Failed: 0
2022/03/28 13:48:27 Assets Software Inventory Skipped: 0
2022/03/28 13:48:27 Software Records Created: 0
2022/03/28 13:48:27 Software Records Create Failed: 0
2022/03/28 13:48:27 Software Records Removed: 0
2022/03/28 13:48:27 Software Records Removal Failed: 0
2022/03/28 13:48:27 Asset Supplier Associations Success: 0
2022/03/28 13:48:27 Asset Supplier Associations Failed: 0
2022/03/28 13:48:27 Asset Supplier Associations Skipped: 0
2022/03/28 13:48:27 Asset Supplier Contract Associations Success: 0
2022/03/28 13:48:27 Asset Supplier Contract Associations Failed: 0
2022/03/28 13:48:27 Asset Supplier Contract Associations Skipped: 0
2022/03/28 13:48:27 Time Taken: 6s
2022/03/28 13:48:27 ---- XMLMC Database Asset Import Complete ---- 
 

 

 

[SamS]

Hi @QEHNick,

Thanks for that, it is indeed what I needed:-)

From the log file I can tell that your binary/executable is v3.0.0 whereas your configuration file is (still) in the v2.x format.

Either downgrade back to v2.4 of the asset import utility or migrate your configuration to the new configuration format (you will notice that predominantly the top area of the configuration has changed).

 

[SamS]

Hi @QEHNick,

Along with the change to the configuration file to make it v3.x compatible, a (significant) bug was found in the v3.0.0 release.

Please download the latest iteration of the utility (v3.0.1 as of this writing): https://github.com/hornbill/goDBAssetImport/releases/latest

v3.0.0 configuration files should work now. v2.x configuration files will need to be re-written to v3.x format.

[QEHNick]

So...does this mean I wasn't going mad?

[QEHNick]

Sorry to say I was prevented from downloading/opening the v3.0.1 zip file as our AVS (Sophos) says it has a virus in it.

 

[James Ainsworth]

@QEHNick

Interesting.  While downloading I did get this

 

which makes sense as it is a newly made available file.  I did download and scan the file but I didn't get any reports of a virus.  I have fed this back so that someone can double check.  

[James Ainsworth]

@QEHNick

I can confirm that the files have been scanned and do not contain a virus.  This false positive has been reported in the past and is simply caused by the zip file containing an exe that is incorrectly flagged as a virus.