Setting up a Role in SMS for Specific Collections

SMS Rights are a pain to work with. (don't think so? Well then stop reading!) For instance, Giving a user or group rights for specific collections, becomes a tricky situation (I say this because rights are not inherited, so when you try to create roles with less than super admin access, it gets sticky.

Role for a specific collection, or set of collections.

Assign class rights for the following: Advertise, Create, Manage Folders, and Delegate.

Assign the following instance rights for the collections you wish the role to have access to: Read, Modify, Delete. (Each collection, and one of those collections must contain some computers!)

If you do this with a hierarchy for example, you would have the top level collection with the computers which the role can manage. Then anyone in that role can manage only that collection of computers. Every time they make a new collection, they will be forced to limit their query to that top level collection with the computers in it.

If necessary, remove the users instance rights for other collections.

Any collection which they can see, they can advertise to.

The user who creates a collection, must use their delegate right to grant rights to others in the role.

SQL Function to take an IP and return the class B

Creates a SQL function to take an IP and return the class B. There's probably a better way to do this, if you've got one, I'd love to see it. Comment out the second "Set @returnIp" to return a Class C.

----Begin SQL----
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION dbo.getClassB(@Ip varchar(16))

RETURNS varchar(16) AS
BEGIN

Declare @returnIp varchar(16)

Set @returnIp = Reverse(Right(Reverse(@IP),Len(Reverse(@IP)) - Charindex('.',Reverse(@IP))))
Set @returnIp = Reverse(Right(Reverse(@returnIp),Len(Reverse(@returnIp)) - Charindex('.',Reverse(@returnIp))))

return @returnIp
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----End SQL----

VBScript to Show Patch Status on a Windows Client using Win32_PatchState_Extended

There are many Win32 classes in Windows Management Instrumentation (WMI). The following vbscript shows you patches and their status from the Win32_PatchState_Extended Class. You can also use this same code and query the Win32_PatchState class, however it's no longer used for the new Microsoft Scan tool (it uses PatchState_Extended).

---- Begin VBScript ----

'On Error Resume Next
Declare Count

Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Patchstate_Extended",,48)

'Set the Count to Zero
Count = 0

'Loop through the collection of Win32_PatchState_Extended items
For Each objItem in colItems
'Echo out the properties for each item
Wscript.Echo "AuthorizationName: " & objItem.AuthorizationName
Wscript.Echo "Title: " & objItem.Title
Wscript.Echo "QNumbers: " & objItem.QNumbers
Wscript.Echo "RebootType: " & objItem.RebootType
Wscript.Echo "ScanAgent: " & objItem.ScanAgent
Wscript.Echo "ScanDateTime: " & objItem.ScanDateTime
Wscript.Echo "Status: " & objItem.Status & vbCrLf
Next

----End VBScript----

The Win32 classes offer tons of functionality through WMI. If you havn't browsed through the different classes, take a look at Win32 Classes on MSDN. For a cool tool to help with WMI Scripting, check out Scriptomatic by the Scripting Guys at MSDN.

SMS SQL to view the Database Schema

Microsoft doesn't support direct access to the SMS Database, however from time to time you may need to query the db directly from SQL. To view the schema, the following SQL comes in handy.

---- Begin SQL----

--This query will show all the views in the SMS db
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'dbo'

----End SQL----

VBScript to query WMI for Chassis Type

Use Chasis type when you need to figure out what kind of computer it is, laptop, desktop, pizza box, lamp, etc.

Sure, you can definately build an SMS Collection based on inventory using the chassis type. Then you can manage your laptops differently, and with download execute, proper cache management, and a little bit of hot sauce, you've got some good eats.

----Begin VBScript----
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colChassis = objWMIService.ExecQuery _
("Select * from Win32_SystemEnclosure")
For Each objChassis in colChassis
For Each strChassisType in objChassis.ChassisTypes
Select Case strChassisType
Case 1
Wscript.Echo "Other"
Case 2
Wscript.Echo "Unknown"
Case 3
Wscript.Echo "Desktop"
Case 4
Wscript.Echo "Low Profile Desktop"
Case 5
Wscript.Echo "Pizza Box"
Case 6
Wscript.Echo "Mini Tower"
Case 7
Wscript.Echo "Tower"
Case 8
Wscript.Echo "Portable"
Case 9
Wscript.Echo "Laptop"
Case 10
Wscript.Echo "Notebook"
Case 11
Wscript.Echo "Handheld"
Case 12
Wscript.Echo "Docking Station"
Case 13
Wscript.Echo "All-in-One"
Case 14
Wscript.Echo "Sub-Notebook"
Case 15
Wscript.Echo "Space Saving"
Case 16
Wscript.Echo "Lunch Box"
Case 17
Wscript.Echo "Main System Chassis"
Case 18
Wscript.Echo "Expansion Chassis"
Case 19
Wscript.Echo "Sub-Chassis"
Case 20
Wscript.Echo "Bus Expansion Chassis"
Case 21
Wscript.Echo "Peripheral Chassis"
Case 22
Wscript.Echo "Storage Chassis"
Case 23
Wscript.Echo "Rack Mount Chassis"
Case 24
Wscript.Echo "Sealed-Case PC"
Case Else
Wscript.Echo "Unknown"
End Select
Next
Next
----End VBScript----

Pasted from
http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept04/hey0921.mspx