SQL of SQL Behind Collection

If you are like us, then you have no doubt created a very intricate Altiris environment.  By providing the foundation of data for our CMDB, there are still some pieces of data that are not known to the OS and therefore, unable to be inventoried via custom inventories.  One such data point is site.  With our clients and locations being varied, we need to be able to query and build collections, jobs, and reports based on a servers' 'site'.  That is where connectors come in.  We leverage our connector solution to pull the 'site' location for each server in our environment and tie it to that record.  This has allowed us to do some very neat configuration and software delivery job designing.  Especially for HA and DR scenarios.

However, things change.  We are undergoing a change to the fundamental values for the 'site' data class in our CMDB.  This will, of course, severely impact our Altiris infrastructure considering the amount of customization we have done simply based on this value alone.

To properly prepare us for this change, I had to determine which collections, jobs and such actually reference this data class that is imported nightly via the connector solution.

After much trial and tribulation, the below sql will show you which items in Altiris contain references to our 'site' data class, which is called 'CMDB_Site'

select * from item

wherestate like '%CMDB_Site%'

While the output may not be pretty, you will know instantly which collections (that contain a sql query) that references the 'CMDB_Site' data class.

We now have a finite list of impacted items that will need to be addressed before we change the 'site' values.

Thank you.