August 15 2011

SCCM report for SQL server Developer, Enterprise and Standard version installations

I had a requirement to report on SQL Server installations in our environment as part of our Microsoft EA true-up process. This included SQL 2000, 2005 & 2008 installations. The difficulty was that you can’t run a simple Add / Remove Programs report because it will report all of the SQL client tools installations as well as the Express and Windows Internal Databases version. I needed to actually report on just the SQL Server Developer, Enterprise and Standard version installations.

After using the MOF edit by Sherry Kissinger (http://myitforum.com/cs2/blogs/skissinger/archive/2010/12/20/installed-sql-05-and-08-version-information-via-configmgr-hardware-inventory.aspx), I then create a report that filtered out much of the details and gave me a summary of SQL 2000-2005-2008-2008R2 Server Developer, Enterprise and Standard version installations. The query for the SCCM report is:

select
sys1.Netbios_name0 as [Machine],
max(Case sql.PropertyName0 when 'SKUName' then
sql.PropertySTRValue0 end) as [SQL 2008 / R2 Edition],
max(Case sql2.PropertyName0 when 'SKUName' then
sql2.PropertySTRValue0 end) as [SQL 2005 Edition],
sys1.ad_site_name0 as [Site Name],
v_R_User.Full_User_Name0 As [Primary Machine User]
from v_r_system sys1
left join v_gs_sql_property0 sql on sys1.resourceid=sql.ResourceID
left join v_gs_sql_property_legacy0 sql2 on sys1.ResourceID=sql2.ResourceID
left Outer Join v_R_User On sys1.User_Name0 = v_R_User.User_Name0
where
(sql.PropertyName0 in ('SKUNAME','SPLevel','version','fileversion')
or
sql2.PropertyName0 in ('SKUNAME','SPLevel','version','fileversion'))
and
(
sql.PropertyStrValue0 LIKE '%standard%' or
sql2.PropertyStrValue0 LIKE '%standard%' or
sql.PropertyStrValue0 LIKE '%enterprise%' or
sql2.PropertyStrValue0 LIKE '%enterprise%' or
sql.PropertyStrValue0 LIKE '%develop%' or
sql2.PropertyStrValue0 LIKE '%develop%'
)
group by sys1.Netbios_name0, sys1.User_Name0, sys1.ad_site_name0, v_R_User.Full_User_Name0

 
 



----------------------------------------------------------------------------
I use a maximum of one Google Ad per post to help offset some of my blog hosting costs.

----------------------------------------------------------------------------


Tags: , , , , , , , ,

Posted August 15, 2011 by danovich in category "SMS / SCCM", "Tools

Leave a Reply