Share via

What AI data is in your database?

  • Article

Ever wonder what’s in the Asset Intelligence (AI) catalog on your site server’s database?  I wrote the following query for a customer to show the value of AI and the data this is in there (and convince them to deploy AI Sync Point).  It summarizes data to a list of Publishers and an sum of their products and rules for their products.  When I ran this query in my test environment (newly synchronized SP2 site), it returns the following counts among other things:

  • Publishers: 11,806
  • Products: 147,923
  • Rules: 252,798
  • Examples for Windows Live Messenger:
    • Products: 8 (e.g. Windows Live Messenger, Windows Live Messenger 8.0, Windows Live Messenger 14.0…)
    • Rules: 84
  • Top 10 publisher by product counts:
    1. Microsoft: 23,921
    2. HP: 3,424
    3. Red Hat: 2,021
    4. IBM: 1,671
    5. National Instruments: 1,554
    6. Adobe: 1,506
    7. Autodesk: 1,180
    8. OSI Software: 1,042
    9. Symantec: 830
    10. Canon: 807

Obviously, from the example of Windows Live Messenger you can see that the number of products is slightly misleading as the product is listed multiple times.  It just depends on the marketing and packaging naming format of the publisher the product counts may need to be looked at on a case by case basis.  Hopefully, the below will give you a starting point for this analysis.

SELECT CommonPublisher AS 'Publisher', COUNT(DISTINCT CommonName) AS 'Count of Products', COUNT(*) AS 'Count of Rules' FROM v_LU_SoftwareList GROUP BY CommonPublisher ORDER BY CommonPublisher ASC COMPUTE SUM(COUNT(DISTINCT CommonName)), SUM(COUNT(*))

This post was contributed by Saud Al-Mishari, a Premier Field Engineer with Microsoft Premier Field Engineering, UK.

UPDATE (09/03/2010): Removed test environment ConfigMgr DB name from T-SQL statement.  Sorry for any confused this caused people.