Import Assets from our CMDB through ODBC
I would like to import our assets through an ODBC connection. The assets are stored in a database (our CMDB) which is available through ODBC. The product has been updated via LiveUpdate before.
I was able to import HostName and DomainWorkgroupName using the following SQL-statement:
SELECT 'CORP' as DomainWorkgroupName, name AS HostName FROM SNO.S_CMDB_CI WHERE SYS_CLASS_NAME = 'cmdb_ci_win_server' AND CURRENT TIMESTAMP BETWEEN VAL_FROM AND VAL_TILL;
Sadly I'm not able to change the query above once it has been saved. After editing the Job the query stays the same as before. I just wanted to add UPPER() in that case. (Later I would like to query additional fields and do an UNION ALL with an other table to get all asset information in one query.)
SELECT 'CORP' as DomainWorkgroupName, UPPER(name) AS HostName FROM SNO.S_CMDB_CI WHERE SYS_CLASS_NAME = 'cmdb_ci_win_server' AND CURRENT TIMESTAMP BETWEEN VAL_FROM AND VAL_TILL;
Not being able to change an existing query is just one issue I'm facing with the Asset import. Most of the time I want to create an Asset Import Job I can't get any further than the "Select Asset Type, Source, and Scope" window.
I do select the following:
- Asset type: Windows Machine
- Source: Database using ODBC Connection
- Scope: Add new...
Then I press the green cross, followed by the selection of the Data-location. In the Entity-Table mapping I do enter an SQL-statement for the Machines Entity. After confirming the "Entity Table Mapping" and "Add Configuration" nothing happens. The list of selected scopes remains empty! I would have to start from scratch defining the Asset Import Job. Pressing the green cross a second time leads to an empty Site name selection...
My questions are:
- Is there a way to validate the SQL statement?
- Are there any limitations to the SQL statement?
- Can I use the same ODBC connection for Windows and UNIX machines?
- Is there some debug output? I did not find anything useful in the Event Log.
- Where can I find additional information on the ODBC Asset import? Especially on the Mapping-Table feature.
Best wishes from Switzerland