Your browser (Internet Explorer 6) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.
X
Aside

ConfigMgr 2012 – Collection Query With Special Characters

Recently had an issue at a client site where we wanted to query for all user accounts with "_a" at the end of the account.  Attempting to query for this didn’t work as we were getting back accounts that just ended with "a".  The Like statement was "%_a".

Turns out you need to put brackets around the underscore as it’s a special character.  So the proper query was "%[_]a".

Thanks to this post by John Nelson that pointed me on the right path.

Aside

List of Computers from an Active Directory User Security Group

Garth Jones (fellow ConfigMgr MVP) blogged a really great query for get a list o PC’s by using a user security group.

Read his original blog post here.

   1: select 

   2:  

   3:   CS.Name0,

   4:  

   5:   CS.UserName0

   6:  

   7: from 

   8:  

   9:   dbo.v_R_User U

  10:  

  11:   join dbo.v_RA_User_UserGroupName UGN on U.ResourceID = UGN.ResourceID

  12:  

  13:   join dbo.v_GS_COMPUTER_SYSTEM CS on CS.UserName0 = U.Unique_User_Name0

  14:  

  15: Where

  16:  

  17:   UGN.User_Group_Name0 = 'GARTEK\Domain Users'

Aside

ConfigMgr-Update Driver Source Paths SQL Query

CAUTION: Editing the Database directly is unsupported.  Proceed at your own risk. 

 

In addition to my previous post, in which I used a script to update the driver paths, you can also do this via SQL.

 

The following command will let you preview the changes you are about to make:

 

image

 

image

 

The following command will execute the changes.

 

image

 

image

 

Change Driver Pkg Source Path

Aside

Beginners Guide to Writing SCCM Reports

John Marcum has created a nice blog post for how to get started creating your own SQL based web reports. 

Read his full post here.

There are often questions on the forums about writing reports and SQL queries for SCCM. These are often people who have no SQL experience at all. There’s no easy way to learn to write these reports but I’m going to show you how I learned to do it and how I still do many of my reports today.

Aside

ConfigMgr–Creating A Collection For All Systems Not Approved

Saw this past by on the MSSMS list today and thought it was good info.  Thanks to Brandon Linton for contributing it.

Collection query:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client
from SMS_R_System inner join SMS_CM_RES_COLL_SMS00001 on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceId
where SMS_CM_RES_COLL_SMS00001.IsApproved<>’1′

Aside

How to retrieve the Lenovo Modelname (Full name) instead of TypeNumber within Configmgr

Great post by Kenny Buntinx.

Read the full post here.

Aside

SQL Query To Show Program Flags

Thanks to John Nelson for this query:

 

DECLARE @ProgramFlags TABLE (
   BitFlag INT PRIMARY KEY,
   Meaning VARCHAR(128),
   Description VARCHAR(512)
)
INSERT INTO @ProgramFlags
VALUES
   (2 ,’USECUSTOMPROGRESSMSG’,’The task sequence shows a custom progress user interface message.’),
   (16      ,’DEFAULT_PROGRAM’,’This is a default program.’),
   (32      ,’DISABLEMOMALERTONRUNNING’,’Disables MOM alerts while the program runs.’),
   (64      ,’MOMALERTONFAIL’,’Generates MOM alert if the program fails.’),
   (128     ,’RUN_DEPENDANT_ALWAYS’,’If set, this program”s immediate dependent should always be run.’),
   (256     ,’WINDOWS_CE’,’Indicates a device program.  If set, the program is not offered to desktop clients.’),
   (1024    ,’COUNTDOWN’,’The countdown dialog is not displayed.’),
   (4096    ,’DISABLED’,’The program is disabled.’),
   (8192    ,’UNATTENDED’,’The program requires no user interaction.’),
   (16384   ,’USERCONTEXT’,’The program can only run when a user is logged on.’),
   (32768   ,’ADMINRIGHTS’,’The program must be run as the local Administrator account.’),
   (65536   ,’EVERYUSER’,’The program must be run by every user for whom it is valid. Valid only for mandatory jobs.’),
   (131072  ,’NOUSERLOGGEDIN’,’The program is only run when no user is logged on.’),
   (262144  ,’OKTOQUIT’,’The program will restart the computer’),
   (524288  ,’OKTOREBOOT’,’Configuration Manager restarts the computer when the program has finished running successfully.’),
   (1048576 ,’USEUNCPATH’,’Use a UNC path (no drive letter to access)’),
   (2097152 ,’PERSISTCONNECTION’,’Persists the connection to the drive specified in the DriveLetter property.  The USEUNCPATH bit flag must not be set.’),
   (4194304 ,’RUNMINIMIZED’,’Run the program as a minimized window.’),
   (8388608 ,’RUNMAXIMIZED’,’Run the program as a maximized window.’),
   (16777216      ,’HIDEWINDOW’,’Hide the program window.’),
   (33554432      ,’OKTOLOGOFF’,’Logoff user when program completes successfully.’),
   (134217728     ,’ANY_PLATFORM’,’Override check for platform support.’),
   (536870912     ,’SUPPORT_UNINSTALL’,’Run uninstall from the registry key when the advertisement expires.’);
WITH cte AS (
   SELECT
      f.BitFlag,
      f.Meaning,
      (f.BitFlag & p.ProgramFlags)/f.BitFlag AS [Enabled],
      p.PackageID,
      p.ProgramName
   FROM
      dbo.v_Program p
      CROSS JOIN @ProgramFlags f
)  
SELECT
   PackageID,
   ProgramName,
   MAX(CASE BitFlag WHEN 2 THEN [Enabled] END) AS [USECUSTOMPROGRESSMSG],
   MAX(CASE BitFlag WHEN 16 THEN [Enabled] END) AS [DEFAULT_PROGRAM],
   MAX(CASE BitFlag WHEN 32 THEN [Enabled] END) AS [DISABLEMOMALERTONRUNNING],
   MAX(CASE BitFlag WHEN 64 THEN [Enabled] END) AS [MOMALERTONFAIL],
   MAX(CASE BitFlag WHEN 128 THEN [Enabled] END) AS [RUN_DEPENDANT_ALWAYS],
   MAX(CASE BitFlag WHEN 256 THEN [Enabled] END) AS [WINDOWS_CE],
   MAX(CASE BitFlag WHEN 1024 THEN [Enabled] END) AS [COUNTDOWN],
   MAX(CASE BitFlag WHEN 4096 THEN [Enabled] END) AS [DISABLED],
   MAX(CASE BitFlag WHEN 8192 THEN [Enabled] END) AS [UNATTENDED],
   MAX(CASE BitFlag WHEN 16384 THEN [Enabled] END) AS [USERCONTEXT],
   MAX(CASE BitFlag WHEN 32768 THEN [Enabled] END) AS [ADMINRIGHTS],
   MAX(CASE BitFlag WHEN 65536 THEN [Enabled] END) AS [EVERYUSER],
   MAX(CASE BitFlag WHEN 131072 THEN [Enabled] END) AS [NOUSERLOGGEDIN],
   MAX(CASE BitFlag WHEN 262144 THEN [Enabled] END) AS [OKTOQUIT],
   MAX(CASE BitFlag WHEN 524288 THEN [Enabled] END) AS [OKTOREBOOT],
   MAX(CASE BitFlag WHEN 1048576 THEN [Enabled] END) AS [USEUNCPATH],
   MAX(CASE BitFlag WHEN 2097152 THEN [Enabled] END) AS [PERSISTCONNECTION],
   MAX(CASE BitFlag WHEN 4194304 THEN [Enabled] END) AS [RUNMINIMIZED],
   MAX(CASE BitFlag WHEN 8388608 THEN [Enabled] END) AS [RUNMAXIMIZED],
   MAX(CASE BitFlag WHEN 16777216 THEN [Enabled] END) AS [HIDEWINDOW],
   MAX(CASE BitFlag WHEN 33554432 THEN [Enabled] END) AS [OKTOLOGOFF],
   MAX(CASE BitFlag WHEN 134217728 THEN [Enabled] END) AS [ANY_PLATFORM],
   MAX(CASE BitFlag WHEN 536870912 THEN [Enabled] END) AS [SUPPORT_UNINSTALL]
FROM
   cte
GROUP BY
   PackageID,
   ProgramName

Aside

ConfigMgr Inventory–Group Policy Preferences

Trevor Sullivan has a nice post over on his blog talking about inventorying your systems to find out which systems don’t have the Group Policy Preferences installed.  He’s also included a WQL query for you use to build a collection. 

Read the blog post here.

Good job Trevor!

Aside

System Center Configuration Manager (ConfigMgr)–Updating Package Source Paths

The information provided in this post is provided as-is, this is most likely unsupported, proceed at your own risk.

There are times when you may need to move the source files for your packages from one location to another.  Maybe your server is running low on space, or you just want to reorganize things a little bit.  Regardless, manually updating all of your packages with a new source would be a very tedious task.  Provided with this blog post is a script that will make the necessary changes and create a log file so you can easily see what was changed. 

If you are the original creator of the script that I modified, thank you, as I’m not sure where I got the script from, it was something I had laying around and then I modified to take advantage of MDT’s logging capabilities.

I’ve taken ZTIUtility.vbs from MDT and modified it a little bit to do what I need, and then my script utilizes the logging functions of ZTIUtility to create a log file for you that shows the status and results of the changes. 

Modifying The Script For Your Environment

In order for the script to function properly, we will need to modify a few items in the script for your environment. 

image

First, set the old server path and then the new server path, this could be just the server, or this could possibly be a share path as well.  Basically whatever you want to replace goes here.

Also, set the server you want to connect to to make the changes. 

Running The Script

Place the script files (ZTIUtility.vbs and configmgr-updatepackagesourcepaths.wsf) in a folder (I use “Scripts” in this example), and then run the following command line to run them. You will need the modified version of ZTIUtility included with the download, otherwise, it should work with any copy of ZTIUtility, however the logs will show up under \MININT\SMSOSD\LOGS most likely.

cscript.exe ConfigMgr-UpdatePackageSourcePaths.wsf

image

You can also just double-click on the script if you are viewing it with windows explorer.

image

After the script runs, you will have a “Script_Logs” folder on the root of C:.

image

Open up the “ConfigMgr-UpdatePackageSourcePaths.log” to view the results. “BDD.log” will also have the same information.

image

Here you can see that we modified the Package “Test Package” and changed the source from \\2008-test to \\2008-configmgr. I have coded the script to show the package name as a Warning type message so it’s easy to find the packages in the log file.  You will be able to see every package that was updated and what the original path and the new path is now.

I would also recommend that you view the changes in SQL to quickly see all the packages.  This can be accomplished through SQL Management Studio by running the following query.

select * from v_package
order by PkgSourcePath

I sorted the query by the Package Source Path (PkgSourcePath), and then you can easily see where everything is located. Here we can see our Test Package amongst other packages with the new source path we specified in the script file.

image

Aside

ConfigMgr–Exclude Members Of Another Collection

Was working on a crazy collection query today for a client and I needed to exclude the members of another collection in addition to the other queries I had already built into the collection.  Took me awhile of searching, but I finally found this post on TechNet.

This is the query:

Select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,

SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,

SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Client = 1 and

SMS_R_System.ClientType = 1 and SMS_R_System.ResourceId not in

(select ResourceID from SMS_CM_RES_COLL_XXXxxxxx)

 

Replace the red XXXxxxxx with the CollectionID you want to use.