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".
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:
The following command will execute the changes.
John Marcum has created a nice blog post for how to get started creating your own SQL based web reports.
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.
from SMS_R_System inner join SMS_CM_RES_COLL_SMS00001 on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceId
Great post by Kenny Buntinx.
Thanks to John Nelson for this query:
DECLARE @ProgramFlags TABLE (
BitFlag INT PRIMARY KEY,
INSERT INTO @ProgramFlags
(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 (
(f.BitFlag & p.ProgramFlags)/f.BitFlag AS [Enabled],
CROSS JOIN @ProgramFlags f
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]
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.
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.
You can also just double-click on the script if you are viewing it with windows explorer.
After the script runs, you will have a “Script_Logs” folder on the root of C:.
Open up the “ConfigMgr-UpdatePackageSourcePaths.log” to view the results. “BDD.log” will also have the same information.
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.
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:
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.