Jump to content


  • 0
bendover

Custom Report with SQL Server versions and editions installed

Question

I'd need to make custom report for SCCM 2012 R2 which includes the following:

 

Computer Name, User Name, SQL Version (2005, 2008, 2012, 2014 etc), Edition (Express, Standard etc) and Version Number.

 

Have tried many different solutions but none of them seems to work. Many solutions includes modifying configuration.mof and sms_def.mof files. That's not a problem but every SQL query gives some error message.

Share this post


Link to post
Share on other sites

9 answers to this question

Recommended Posts

  • 0

Share this post


Link to post
Share on other sites

  • 0

Yes, I have edited configuration.mof and added custom attributes to Default Client Settings using .mof file. Yes, I have run hardware inventory for all clients. I already found couple of errors; custom attributes added to client settings uses different names than classes in query. There are also syntax errors; if you just copy and paste the sql query it uses wrong quotation marks. Nevermind...this will work soon.

Share this post


Link to post
Share on other sites

  • 0

It is very common to have issue with quote marks within blog post, But other than that all SQL query should work.

 

Again without any details as to what errors or issues you are having, there is very little anyone can do to help you.

Share this post


Link to post
Share on other sites

  • 0

It's almost working but for some reason can't query SQL Express editions.

 

Query:

 

SELECT sys1.Netbios_Name0 AS Computer, usr1.Full_User_Name0 AS [user], MAX(CASE sql.PropertyName0 WHEN 'SKUName' THEN sql.PropertyStrValue0 END) AS Edition,
MAX(CASE sql.PropertyName0 WHEN 'VERSION' THEN sql.PropertyStrValue0 END) AS Version, MAX(CASE sql.PropertyName0 WHEN 'SPLEVEL' THEN sql.PropertyNumValue0 END) AS [service Pack],
MAX(CASE sql.PropertyName0 WHEN 'FILEVERSION' THEN sql.PropertyStrValue0 END) AS [CU Version]
FROM dbo.v_R_System AS sys1 LEFT JOIN
dbo.v_R_User AS usr1 ON sys1.User_Name0 = usr1.User_Name0 LEFT JOIN
dbo.v_GS_CUSTOM_SQL12_Property0 AS sql ON sys1.ResourceID = sql.ResourceID LEFT JOIN
dbo.v_GS_CUSTOM_SQL14_Property0 AS sql2 ON sys1.ResourceID = sql2.ResourceID LEFT JOIN
dbo.v_GS_CUSTOM_SQL_Property_2_00 AS sql3 ON sys1.ResourceID = sql3.ResourceID
WHERE (sql.PropertyName0 IN ('SKUNAME', 'SPLevel', 'version', 'fileversion')) OR
(sql2.PropertyName0 IN ('SKUNAME', 'SPLevel', 'version', 'fileversion')) OR
(sql3.PropertyName0 IN ('SKUNAME', 'SPLevel', 'version', 'fileversion'))
GROUP BY sys1.Netbios_Name0, usr1.Full_User_Name0
EDIT: Done, also SQL Express Editions are working

Share this post


Link to post
Share on other sites

  • 0

You didn't answer my question. Have you confirmed that SQL Express is being inventoried? aka can you see with within resource explorer?

 

I know for a fact that you can collect SQL express data. I have similar but custom method to collect this data.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.