Jump to content

DBAsset Import SQL issue


Recommended Posts

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}}"
    }
}

Link to comment
Share on other sites

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'.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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": ""
    }
}

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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'.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 1 month later...

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.

Link to comment
Share on other sites

            "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"
 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ---- 
 

 

 

Link to comment
Share on other sites

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).

 

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

@QEHNick

Interesting.  While downloading I did get this

 image.png

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.  

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...