Data Integrator – Using SQL Functions

Tobin Isenberg

By: Tobin Isenberg

January 18, 2012 11:44 am

Reads: 263

Comments:0

Rating:0

Many of our customers leverage Operations Center’s Data Integrator adapter to pull in data from external databases. This adapter has the ability to connect with several databases such as Microsoft SQL, Oracle, Sybase, postgres, etc. The typical set up is to drag and drop tables and columns and build an integration on the fly with the Data Integrator IDE. One thing typically omitted is that the Data Integrator supports pretty much any SQL related function which can help you clean up the data, translate values to more obvious meaning, etc.

One example is that you are generating a view of all the computers in your environment, the problem is, the way they are entered into the table, they are mixed upper and lowercase. Your preference is to bring them in as all lowercase. If you were running a query with the OEM’s native query utility, you could add string functions to flip the values to all lowercase, even parse strings. In the attached flash video, I use the lower() function which is postgres specific, other vendors have the same capability, it is just a different function.

Another example is that you would like to have each alarm or element’s date/time update based on the last time the Data Integrator ran. This is helpful to understand how recent your data is. In this case I used the now() postgres function to get the current date/time, Microsoft SQL Server uses the getdate() function for the same type of results. I applied this to the lastUpdated column which in turn stampes the element or alarm with this value.

There are endless possiblities for leveraging functions. I have put full fledge “case” statements in for the severity/condition column in order to evaluate specific data to specific severify/conditions. For example, if the helpdesk ticket has not been updated in the past 5 minutes, mark it green/ok, if it is 20 minutes old with no updates, mark it red/critical. This would require a case statement along with some functions to do math on the tickets last updated column.

The attached flash video shows a couple basic examples of accessing SQL functions to manipulate the results returned from the query. Enjoy!

Video: dataintegratorsqlfunctions.swf

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

Tags:
Categories: Operations Center, Technical Solutions

Disclaimer: As with everything else at NetIQ Cool Solutions, this content is definitely not supported by NetIQ, so Customer Support will not be able to help you if it has any adverse effect on your environment.  It just worked for at least one person, and perhaps it will be useful for you too.  Be sure to test in a non-production environment.

Comment