Browse by Tags

CODE - SQL for SMS/SCCM - How to get the CORRECT order when sorting on version numbers like xx.xx.xx.xx

This is the SQL code for THIS ARTICLE SNIPPET #2 - UDF_ITEMEXTRACT - SQL FUNCTION TO EXTRACT AN ITEM OUT OF A DELIMITED LIST -- ============================================= -- Author: John Nelson (Number2) -- Create date: 2007-05-26 -- Description: Given a delimited list, a position and a delimiter...

Posted to Number2 Blog - MyITForum.com (Weblog) by jnelson on 11-13-2007
Filed under: SQL, SMS, SMS 2003, outlook, udf_ItemExtract, v_ProductFileInfo, INT, BIGINT, CAST, itemExtract, order by, sql 2005, v_GS_SoftwareFile, SUBSTRING, CHARINDEX, sorting, v_GS_SoftwareProduct
SMS 2003 - SMS Permissions Matrix

This SQL query pulls all SMS permissions for each user and displays them in a matrix. Put this into an SMS Web report. This should work for SQL 2000, SQL 2005 and higher. SELECT UCP.UserName, SO.ObjectName, MAX(CASE UCP.PermissionName WHEN 'Administer' THEN 'X' END) as [Administer], MAX...

Posted to Number2 Blog - MyITForum.com (Weblog) by jnelson on 05-30-2008
Filed under: SMS 2003, web report, sql 2005, SQL 2000, permissions
M@d Skillz - SMS Web Reports - Passing a delimited list to SMS Web Reports like a table - (also convert list to rows in SQL)

BACKGROUND Pass multiple items into a single parameter? With SMS Web reports, there's a lot of power in being able to pass parameters to your report query and have it substitute those values into the SQL code. It really saves you a lot of time not having to write your own front end for your SQL queries...

Posted to Number2 Blog - MyITForum.com (Weblog) by jnelson on 01-16-2008
Filed under: SMS, UDF, user defined functions, SMS 2003 SP3, SMS 2003, SMS 2003 SP2, Microsoft Systems Management Server, SMS Web Reports, web report, webreport, smsschm_users, webreport_approle, sql 2005, web reports, user defined function, sms report, sms 2.0, sms 1.2, list to rows, udf_Split, delimited list, list to table, list to column
SMS 2003 - SQL Error: (SMS Message ID 620, SQL Message 2801) The definition of object 'sp_GetPublicKeySMSUID' has changed since it was compiled...

PROBLEM Recently, we started seeing a TON of errors in SMS_DISCOVERY_DATA_MANAGER, SMS_INVENTORY_DATA_LOADER and SMS_SOFTWARE_INVENTORY_PROCESSOR (click on graphic to see better if it's too small) The error for all of them was the same, in the status message viewer, it had SMS message ID 620: Microsoft...

Posted to Number2 Blog - MyITForum.com (Weblog) by jnelson on 12-03-2007
Filed under: SQL, SMS, SMS 2003 SP3, SMS 2003, sql 2005, sp_recompile, SMS_INVENTORY_DATA_LOADER, SMS_SOFTWARE_INVENTORY_PROCESSOR, SMS_DISCOVERY_DATA_MANAGER, sp_GetPublicKeySMSUID, recompiling stored procedures
SQL for SMS - Concatenate MAC addresses and IP Addresses into their own columns

If you've ever written a report that also selects IP addresses or MAC addresses, you've noticed that there can be multiple addresses per machine and it will throw off your counts. So I've whipped a little something together that concatenates them into their own columns. I don't have time...

Posted to Number2 Blog - MyITForum.com (Weblog) by jnelson on 11-21-2007
Filed under: SQL, SMS, UDF, SMS 2003, web report, sql 2005, SQL 2000, ip address, mac address, user defined function, FOR XML PATH, sms report
M@d Skillz - SMS Permissions - BITWISE "AND" with a PIVOT thrown in for pretty :)

M@d SKILLZ ARTICLE ALERT! (This is gonna get cr@zy, if you have a helmet, now's the time to put it on) So I was trolling the forums here on MyITForum.com the other night and I came across THIS POST from Tom Watson. He was geeking out pretty good and building a report that shows all the instance security...

Posted to Number2 Blog - MyITForum.com (Weblog) by jnelson on 11-19-2007
Filed under: SQL, SMS, PIVOT, SMS 2003, sql 2005, web reports, inner join, BYTE, v_UserClassPermissions, table, MAX, v_SecuredObject, forums, sms permissions, view, bitwise and, BIT
SQL Oddity - OUTER JOINs with criteria act like INNER JOIN

SYMPTOMS You have a query with an OUTER JOIN (aka LEFT JOIN, RIGHT JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN), and you need to put filter criteria on the table or view that is outer joined. But when you do that, the query acts like an inner join. Consider the following query: It's designed to show...

Posted to Number2 Blog - MyITForum.com (Weblog) by jnelson on 11-16-2007
Filed under: SQL, SMS, SMS 2003, sql 2005, v_GS_SoftwareFile, right outer join, inner join, right join, join, v_FullCollectionMembership, left outer join, outer join, V_R_System, left join
SQL Performance - Indexes and the LIKE clause

INDEXES AND THE LIKE CLAUSE Often times, people will make the generalization that when you have LIKE in your WHERE clause it won't use indexes. The real answer is a little more complicated than that, and I think it's important to understand SQL's behavior so you can make good decisions with...

Posted to Number2 Blog - MyITForum.com (Weblog) by jnelson on 11-16-2007
Filed under: SQL, SMS, table scan, clustered index seek, SMS 2003, index, sql 2005, LIKE, web reports, clustered index scan, index scan, wildcard, index seek
SMS Web Report - Outlook machine counts by version (Plus a trick to order by version PROPERLY)

RECOMMENDED READING First of all, you should read this article I wrote entitled " How to get the CORRECT order when sorting on version numbers like xx.xx.xx.xx " That article lays out a good explaination of the V_ProductFileInfo view, the CAST(dbo.udf_ItemExtract()) stuff, and information on...

Posted to Number2 Blog - MyITForum.com (Weblog) by jnelson on 11-15-2007
Filed under: SQL, SMS, SMS 2003, Microsoft Systems Management Server, web report, webreport, outlook, v_ProductFileInfo, BIGINT, CAST, itemExtract, sql 2005, v_GS_SoftwareFile, v_GS_SoftwareProduct, GROUP BY, DISTINCT
SQL for SMS/SCCM - How to get the CORRECT order when sorting on version numbers like xx.xx.xx.xx

(UPDATE: This post was updated 2008-08-16. Skip to the end to see the update) THE PROBLEM Let's say you query SMS to find all versions of Outlook you have out there, and you order the results by the ProductVersion (if you use V_GS_SoftwareProduct) or fileVersion (if you use V_GS_SoftwareFile), you...

Posted to Number2 Blog - MyITForum.com (Weblog) by jnelson on 11-13-2007
Filed under: SQL, SMS, SMS 2003, outlook, udf_ItemExtract, v_ProductFileInfo, INT, BIGINT, CAST, itemExtract, order by, sql 2005, v_GS_SoftwareFile, SUBSTRING, CHARINDEX, sorting, v_GS_SoftwareProduct

Page 1 of 1 (10 items)