Jörg Walther

Hi Philip, Thanks very much for the suggestion. I had tried a regex before in various forms, but failed every time. I'd used the "AdditionalFilters" section as suggested in the Hornbill documentation, rather than your "AssetFilters". Because of your suggestion I've tried again, still using "AdditionalFilters", but because I couldn't quite figure out how to structure the regex in the context of the conf.json I actually asked Copilot for a 'golang' format (saw this for hornbill) - which suggested: regexp.MustCompile(`^(iPhone|SM-)`) So I transposed this into conf.json and it actually worked! This is the relevant portion of my conf.json: "AssetTypes": [ { "AssetType": "Smartphone", "OperationType": "Both", "PreserveShared": false, "Query": "", "AdditionalFilters": [ { "Field": "model", "Operator": "REGEXMATCH", "Value": "^(iPhone|SM-)" } ], "AssetIdentifier": { "SourceColumn": "deviceName", "Entity": "AssetsMobileDevice", "EntityColumn": "h_name" } } ], So this is trying to get any record where their 'model' startswith "iPhone" or "SM-". I've now got what I was looking for with our Intune imports - obvs successfully connect to Intune, identify and export all fields with their correct attributes - filter generically, so that new records of newer models are captured automatically (as much as possible); e.g. "Operator": "REGEXMATCH", "Value": "^(iPhone|SM-)" - work with strings within the conf.json, so I can only import relevant string portions; e.g.: "h_used_by": "{{ (split \"@\" .userPrincipalName)._0 }}", (this only gets the first portion of the UPN) I attached our complete conf.json if anyone's interested. conf_Intune_dynamic_Mobiles.json

I was recently and rightly pointed to documentation by Hornbill and Microsoft which, at least in theory, answers many of my questions from above. I had seen some of them before but read them too hastily. There's a lot of detail, I needed to take my time. https://docs.hornbill.com/data-imports-guide/assets/configuration explains a lot of the structure of the conf.json files, what attributes are for and how they're applied, etc. It also explains what query operators you can use for intune imports. Though I still keep not finding this entry even though I now know it's there. It's: "Microsoft Graph oData filter for returning asset details from Intune. See the filter parameter documentation https://learn.microsoft.com/en-us/graph/filter-query-parameter?tabs=http from Microsoft for instruction of how these filters can be constructed, and the managed device resource type documentation https://learn.microsoft.com/en-us/graph/api/resources/intune-devices-manageddevice?view=graph-rest-1.0#properties for a list of available properties." The issue now is that I can't make these filter parameters work. E.g.: This works ok: "Query": "model eq 'iPhone 12 mini' or model eq 'iPhone 14' or model eq 'SM-A326B' or ..." However none of these options listed/implied in the documentation work: "Query": "model startsWith 'iPhone'", "Query": "startsWith(model, 'iPhone')", "Query": "model in ('iPhone 12 mini', 'iPhone 13 mini', 'iPhone 14')", "Query": "model contains 'iPhone'", For all of those I keep getting this error message: "No Smartphone asset records returned from Intune - check your configuration!" I tried adding a "AdditionalFilters" section (as shown in the example conf.json in docs.hornbill.com/data-imports-guide/assets/configuration), but that stopped it working, too Any idea how I can structure the query more generically?

It appears the Intune attributes are defined and documented well enough: https://learn.microsoft.com/en-us/graph/api/resources/intune-devices-manageddevice?view=graph-rest-1.0#properties

What I'm yet to figure out is - and would be grateful for any suggestions on: - what are the attributes Intune provides and what are their proper names - which of those attributes can I get - which of those attributes can I use for queries in asset_import (some I know I can get but cannot query on) - what are the query comparison operators available for this Intune asset_import. We know that the below works. "Query": "model eq 'iPhone 12 mini' or model eq 'iPhone 13 mini' or model eq 'iPhone 14' or model eq 'iPhone 15' or model eq 'SM-A326B' or model eq 'SM-A346B' or model eq 'SM-A546B'", However that's very static and I cannot get something more flexible and dynamic to work which works for other imports, e.g.: "Query": "model CONTAINS 'iPhone' or model CONTAINS 'SM-'",

So, I still don't know which is the better or preferred method. However I've fiddled about with the asset_import.exe method starting with the included conf_example_intune.json. I'm still disappointed with the lack of documentation around this method as there are numerous aspects in the conf I've no idea how they work, what they do, if they're required and how I can change them. So for now I'm left with a disappointing trial and error approach. During this testing I changed '"AssetType": "Smart Phone"' to something else which didn't work and then changed it back - and even though the setting I changed it back to hadn't worked before works now! So the following is what works for me atm. Most attributes populate, although some don't (e.g. osVersion). I've not yet troubleshooted those. asset_import.exe -dryrun=false -debug=true -file=conf_intune_dynamic_Mobiles_test01.json conf_intune_dynamic_Mobiles_test01.json: { "KeysafeKeyID": <redacted>, "LogSizeBytes": 10000000, "HornbillUserIDColumn": "h_user_id", "SourceConfig": { "Source": "intune", "Intune": { "Fields": [ "id", "userId", "deviceName", "operatingSystem", "osVersion", "userPrincipalName", "model", "manufacturer", "serialNumber", "userDisplayName", "managedDeviceName", "phoneNumber", "subscriberCarrier", "imei" ] } }, "AssetTypes": [ { "AssetType": "Smart Phone", "OperationType": "Both", "PreserveShared": false, "Query": "model eq 'iPhone 12 mini' or model eq 'iPhone 13 mini' or model eq 'iPhone 14' or model eq 'iPhone 15' or model eq 'SM-A326B' or model eq 'SM-A346B' or model eq 'SM-A546B'", "AssetIdentifier": { "SourceColumn": "deviceName", "Entity": "AssetsMobileDevice", "EntityColumn": "h_name" } } ], "AssetGenericFieldMapping": { "h_name": "{{.deviceName}}", "h_asset_tag": "{{.deviceName}}", "h_description": "Imported from Intune.", "h_supported_by": "{{.subscriberCarrier}}", "h_used_by_name": "{{.userPrincipalName}}", "h_used_by": "{{.userPrincipalName}}" }, "AssetTypeFieldMapping": { "h_name": "{{.deviceName}}", "h_imei_number": "{{.imei}}", "h_model": "{{.model}}", "h_manufacturer": "{{.manufacturer}}", "h_os_description": "{{.operatingSystem}}", "h_os_version": "{{.osVersion}}", "h_phone_number": "{{.phoneNumber}}", "h_serial_number": "{{.serialNumber}}" } }

Hi All, I just wanted to check what a supported or best method is for regular asset imports from Microsoft (Azure) Intune? I noticed that the newer/newest asset_import.exe comes with a sample conf_example_intune.json - https://docs.hornbill.com/data-imports-guide/assets/configexamples/intune . Unfortunately I can't get that to work for us with error "..No ... asset records returned from Intune - check your configuration!" I then noticed some articles implying that the above won't work and you'd best use this powershell script HornbillAzureIntuneAssetImport.ps1 Ideally I'd like to use the asset_import utility as we've already utilised this for many other type of asset imports and I'd like to avoid using different mechanisms needlessly. Regards, Jörg

Hi James, That's very helpful and basically is what I'm after. Shows I have a lot to learn about the platform. At first I struggled to find the h_cmdb_assets_... until I realised that entity explorer gives different results depending on Configuration choice - Service Manager or Platform Configuration. So, yes I would have found the date options h_license_expiry_date and h_license_renewal_date I was looking for. Also by now I think I have a better understanding of the difference of the sections 'AssetGenericFieldMapping' and 'AssetTypeFieldMapping' and where to place attributes. Thanks a lot, Jörg

Hi All, Is there a complete reference document for all asset related attribute names? I could only find partial references, e.g.: Conf.json syntax found in https://wiki.hornbill.com/index.php/CSV_Asset_Import and supplied example conf.json files. Asset attribute structure https://wiki.hornbill.com/index.php/Understanding_the_Asset_Structure ui naming e.g.: https://live.hornbill.com/<instanceid>/servicemanager/asset/type/new The names often don't match, especially in the conf.json files. Quite a few are mentioned in the ui but not documented in the asset attribute structure or for the conf.json syntax. Some appear mentioned in conf.json and asset structure, but not the ui. Etc. Also several are not specifically identified as belonging into the Generic or Type portion of the conf.json's. Name and Description often appear in both. I recently had some trouble using some date entries and could not find anywhere their proper json location and name syntax. It took many iterations of trial and error to figure it out. I mapped all I could find myself in the attached document. Perhaps this could form the basis of a reference doc if not already available? Perhaps this helps others. (I can't guarantee it's perfect though) PASM-Assets_AttributeMapping.xlsx

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

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

Thanks again all for your help here. It's much appreciated. However I decided to delete the incorrect assets using the hornbillCleaner utility, which worked well. I struggled for quite some time but despite your help could not get the Invoke-RestMethod, nor the HornbillAPI Invoke-HB-XMLMC method working. I tried your suggestions and at every error attempted a few variations, but nothing worked. In order to get our project to progress I decided to start from scratch. This brand new import from the snow d/b has since worked well.

Thanks Craig! Hopefully we're starting to get there, so your suggestion's much appreciated! Though I'm still unsure how this fits into the scriptlet suggested by the documentation for doing this in PowerShell (yes, that's how I rock ) . Firstly the ID is then mentioned twice, in assetId and generalProperties. If that's how it works, ok, though confusing. Secondly PowerShell really isn't happy about the syntax, see error example and script screenshot (attached). Error: At line:9 char:36 + "name": "type", + ~~~~~~~~~~~~~~~ Unexpected token 'name": "type", "value": "8291"}] "' in expression or statement. Anything obvious I'm doing wrong? I appreciate that code examples have serious limitations, there's just too many possible variations of how it may be used. But I often find even one or two examples useful as a very basic indication of which direction to go.

Unfortunately I can't make heads or tails from the documentation. It may be sufficient if you're well versed and work lots with API, but I'm not. I'm struggling with the general approach and a lot with syntax. So, for example is 'type' a generalProperties or additionalProperties. Does this correlate with AssetGenericFieldMapping and AssetTypeFieldMapping? Does that cover AssetType, type or h_type at all? If so what syntax does the "JSON string of the properties to use to populate the asset details" take, what does it look like for a single item. The Powershell version certainly doesn't seem happy with anything like generalProperties = "xs:h_type: Laptop", "xs:"h_type": "Laptop"" or "xs:'h_type': 'Laptop'"

Thanks @CraigP I'll have a look at the API. Also thanks @SamS I think I'll either, hopefully be able to resolve it with the above APO or we'll look at starting from scratch.

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