Video Screencast Help

Import Assets from our CMDB through ODBC

Created: 20 Feb 2013 • Updated: 12 Apr 2013 | 5 comments
This issue has been solved. See solution.

Hi all

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

-Luca.

Operating Systems:

Comments 5 CommentsJump to latest comment

cmccoy2's picture

- Is there a way to validate the SQL statement?

 

The only way I know to validate the SQL statement would be to go into SQL Studio and connect to the database server and then try to run the query to see what results you might get.  

- Are there any limitations to the SQL statement?
I would guess the only limitations are for what you can do with a Select statement.

- Can I use the same ODBC connection for Windows and UNIX machines?

You should be able to use it for both Asset types as long as you can clearly identify the Operating System type

- Is there some debug output? I did not find anything useful in the Event Log.

on the CCS manager you should be able to find the logs in C:\ProgramData\Symantec.CSM\Logs\....  if you are on a 2008 server.  Also I would enable verbose logging on the CCS Manager and BladeWorker processes so that you can see everything that is going on.   I think you can do a search and find specific instructions on enabling verbose logging at the http://www.symantec.com/business/support site.

- Where can I find additional information on the ODBC Asset import? Especially on the Mapping-Table feature.

I don't think there is a lot of infomration on ODBC Asset imports except what is in the manual or on the online help site that is created when you install CCS.

 

I am not sure of the 

decurgia's picture

Hi cmccoy2

 

Thank you for the response. Sadly it did not help me.

I can run the SQL statement inside "IBM Data Studio" and it does provide me a result set. My current query is as following:

SELECT 'CORP' as DOMAINWORKGROUPNAME, UPPER(name) AS HOSTNAME FROM SNOW.S_CMDB_CI WHERE SYS_CLASS_NAME = 'cmdb_ci_win_server' AND CURRENT TIMESTAMP BETWEEN VAL_FROM AND VAL_TILL;

The Entity Table Mapping form looks as following:

Once I press the OK-button I'm back on the Add Configuration form:

After pressing the OK-button the Scope has not been created. There is no error message and I have to start trying from scratch...

 

How do you guys define ODBC mappings using SQL? Is there a certain order to follow?

The most annoying thing here is that there is no error message and I do have to start from scratch.

Any idea how to solve this?

 

Greetings

-Luca.

cmccoy2's picture

After you have created your ODBC Connection is it not available in the drop down box of the scope field?  I haven't actually done this, but I assume that it shouldn't be too difficult.    Normally if you have created an ODBC or LDAP connection, then it will show up in the General>Settings>Data Locations and you can edit it from there.   If I get a chance, I may try to replicate your scenario and see what I can find out.

 

cmccoy2's picture

I did look into the issue you are having and it looks like you have found a bug in the system.  I have a fully patched CCS 11 system with PU 2013-1, SCU 2012-4 and PACU 2013-7 and I still got the same issue.  You will need to open up a Tech Support Case and then they can escalate to development so that this can be fixed.

SOLUTION
decurgia's picture

Hi all

Tech Support got the issue fixed. I did receive a patch and it did solve the issue. The next release shall contain this patch.

Cheers