asset_import Database Query Error: mssql: Incorrect syntax

[Jörg Walther]

Hi All,
another question from me. I'm quickly becoming a regular...

Using asset_import v4.3.1 I have an import from our on-prem MS SQL d/b with a relatively involved select query. This worked well. Until that is, I expanded the select statement to include a join from a fourth table to get a "CostCentre" value into "h_cost_center": "{{.CostCentre}}".
This new select statement has been tested and works well inside SQL Management Studio. It gives me what I'm expecting, so I assume the syntax is correct (confirmed by 365 Copilot).

However, when I run

asset_import.exe -dryrun=true -debug=true -file=conf_PAsnowdb_dynamic_Workstations.json

with that new SQL query it errors:
[ERROR]  [DATABASE] Database Query Error: mssql: Incorrect syntax near the keyword 'AND'.

The only new section with 'AND' is this
"...(SELECT EnvValue FROM SnowLicenseManager.dbo.tblComputerEnvironment WHERE ComputerID = CTE.ComputerID AND EnvName = 'CostCentre') AS CostCentre
FROM CTE..."
and I can't see anything obviously wrong, like a JSON special character quirk or some such.

Running the whole conf...json file through jsonlint.com it comes back with "JSON is valid!".

Can anyone see the problem?
Is there a limitation of asset_import for SQL select queries, e.g. for number of AND conditions or some such?? Surely it's not the length of the query, There are longer ones in the included example conf files. Also I can't imagine that asset_import checks the SQL syntax, surely it just passes that onto the SQL server, as long as the JSON is valid (which it is).


I've attached the conf file and the debug log. Also the SQL SELECT straight from working on SSMS. It's the third row from the bottom that contains the new 'AND' 's. 
 

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,ce.EnvName,ce.EnvValue,
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.tblComputerEnvironment AS ce ON c.ComputerID = ce.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 '' END) AS FQDN, (CASE WHEN OperatingSystem LIKE '%Windows%' THEN 'Windows' WHEN OperatingSystem LIKE '%macOS%' THEN 'MacOS' WHEN OperatingSystem LIKE 'OS X' THEN 'OS X' END) AS osType, 
 (SELECT EnvValue FROM SnowLicenseManager.dbo.tblComputerEnvironment WHERE ComputerID = CTE.ComputerID AND EnvName = 'CostCentre') AS CostCentre
FROM CTE
WHERE RowNumber = 1 AND OperatingSystem NOT LIKE 'VMware%';

Any suggested gratefully received,
Jörg

conf_PAsnowdb_dynamic_Workstations_HBForum.json Asset_Import_20240213114548_0.log

[Steve Giller]

MSSQL is not my forte, but I'd start by simply removing the semicolon terminator from your statement.
Each Asset class adds its own "AND" clause to ensure only the right types are returned, so assuming the SQL is parsing the semicolon as the end of the statement, and then there's an additional "AND" clause that fits the error you're seeing.

[Jörg Walther]

That's brilliant, thanks Steve.
Am not sure where I copied the semicolons from and they worked before; but removing them allows asset_import to run now!

This works now:

...
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,ce.EnvName,ce.EnvValue,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.tblComputerEnvironment AS ce ON c.ComputerID = ce.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 '' END) AS FQDN, (CASE WHEN OperatingSystem LIKE '%Windows%' THEN 'Windows' WHEN OperatingSystem LIKE '%macOS%' THEN 'MacOS' WHEN OperatingSystem LIKE 'OS X' THEN 'OS X' END) AS osType, (SELECT EnvValue FROM SnowLicenseManager.dbo.tblComputerEnvironment WHERE ComputerID = CTE.ComputerID AND EnvName = 'CostCentre') AS CostCentre 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"
            }
        },
...