We've been in production about a year now, and I've decided to start
cleaning up the Product Categorizations (we made the mistake of
importing them from the DSL in addition to our own.) So this morning I
decided to write a quick and dirty SQL statement doing a union between
all the modules we use that have Product Categorizations, then join it
with the Product Catalog form and see which ones there don't appear in
any of the modules.
So I find it interesting how BMC uses different field names on all of
their apps to display the same data, but it's not a huge deal. The
worst one though is Asset Management/CMDB. On the Base Element form
there is a field called "Model" that is called "Product Name" in Asset
Management. The "Version" field on Base Element is the "Model" field on
Asset Management.
Anyway, if you need to do any such cleanup, try building off of the SQL
statement below.
Thanks,
Shawn Pierson
SELECT distinct
ppc.Product_Categorization_Tier_1,
ppc.Product_Categorization_Tier_2,
ppc.Product_Categorization_Tier_3,
ppc.Product_Name
FROM PCT_Product_Catalog ppc
LEFT JOIN (SELECT
cic.Product_Cat_Tier_1_2_ AS product_categorization_tier_1,
cic.Product_Cat_Tier_2__2_ AS Product_Categorization_Tier_2,
cic.Product_Cat_Tier_3__2_ AS Product_Categorization_Tier_3,
cic.Product_Name__2_ AS Product_Name
FROM CHG_Infrastructure_Change cic
WHERE cic.Product_Name__2_ IS NOT NULL UNION
SELECT
hhd.product_categorization_tier_1,
hhd.Product_Categorization_Tier_2,
hhd.Product_Categorization_Tier_3,
hhd.Product_Name AS Product_Name
FROM HPD_Help_Desk hhd
WHERE hhd.Product_Name IS NOT NULL UNION
SELECT
bcbbe.Category AS product_categorization_tier_1,
bcbbe.[Type] AS Product_Categorization_Tier_2,
bcbbe.Item AS Product_Categorization_Tier_3,
bcbbe.Model
FROM BMC_CORE_BMC_BaseElement bcbbe
WHERE bcbbe.Model IS NOT NULL UNION
SELECT
ppi.Product_Categorization_Tier_1,
ppi.Product_Categorization_Tier_2,
ppi.Product_Categorization_Tier_3,
ppi.Product_Name
FROM PBM_Problem_Investigation ppi
WHERE ppi.Product_Name IS NOT null
) things
ON ppc.Product_Name = things.Product_Name
WHERE ppc.status = 1
AND ppc.Product_Name IS NOT NULL
AND things.Product_Name IS NULL
ORDER BY
ppc.Product_Categorization_Tier_1,
ppc.Product_Categorization_Tier_2,
ppc.Product_Categorization_Tier_3,
ppc.Product_Name;
Private and confidential as detailed here:
http://www.sug.com/disclaimers/default.htm#Mail . If you cannot access the
link, please e-mail sender.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
