SCCM - Collection Query (Computers with VS 2008 Installed)

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_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name  from SMS_R_System  inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId  inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId  where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Visual Studio 2008%")

SCCM - Device Collection Query (Domain OU Membership)

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.SystemOUName = "DOMAIN/OU"

SCCM - SQL query (Find last logon user details of computers)

Select distinct
v_R_System.Netbios_Name0 AS "Computer Name",
v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS "Console User",
v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS "Console Logons",
v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS "Total Minutes on Console",
v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS "Last Console Use"
from v_R_System INNER JOIN
(SELECT
ResourceID,
SystemConsoleUser0,
NumberOfConsoleLogons0,
TotalUserConsoleMinutes0,
LastConsoleUse0,
ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY LastConsoleUse0 DESC) AS Point
FROM
v_GS_SYSTEM_CONSOLE_USER) v_GS_SYSTEM_CONSOLE_USER
ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
AND v_GS_SYSTEM_CONSOLE_USER.Point = 1
Order by v_R_System.Netbios_Name0

Description: This will get you the top console user info who logged in to the machines most recently.

SCCM - Approve Clients Script

 You can find the archive here.


Step 1.

Create the following collection and criteria on your Top SCCM Server and name it per your naming standards

My Collection and Query are both named "All Unapproved Systems".

You will need to Replace YOURDOMAIN with your domain name..

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= '0' and SMS_R_SYSTEM.ResourceDomainORWorkgroup = 'YOURDOMAIN'
 

Step 2.

Download the script, modify a few lines of code.

Line 85 = Add your Top SCCM Site Server here.

Line 86 = Add your Top SCCM Site Code here.

SCCM 2012 - SQL query (Find Computer by Serial Number)

SELECT     dbo.v_R_System.Name0, dbo.v_GS_COMPUTER_SYSTEM.UserName0, dbo.v_GS_PC_BIOS.SerialNumber0,
                      dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0
FROM         dbo.v_R_System INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID
WHERE SerialNumber0 LIKE @SerialNumber

SCCM 2012 - Report Builder 2.0 is not installed ...

If you are using SQL 2008 R2 with ConfigMgr 2012, you`ll probably receive this error "Report Builder 2.0  is not installed as a click-once application on report server"

ConfigMgr 2012 is designed to work out of the box with Report Builder 2.0

You’ll need to set a registry key to name the RB 3.0 application manifest.  The key in question is “HKLM\Software\Wow6432Node\Microsoft\ConfigMgr10\AdminUI\Reporting\ReportBuilderApplicationManifestName”.  Change its value from “ReportBuilder_2_0_0_0.application” to “ReportBuilder_3_0_0_0.application” on any machine from which you want to launch RB 3.0.

Then start the Configuration Manager Administrator console as administrator and it works.

Windows Sysprep

Every time I need to set up a bunch of virtual machines, I have to go back and look up where to find the Sysprep tool and how to use it. Here are the details so I can find it in the future…
In case you haven’t encountered Sysprep before, it is a tool that allows you to create a base OS image (including Windows, Office, Visual Studio, or whatever other applications you want) and then re-package it. You can then create cloned disks (or just copy the whole thing) and when you boot the new disk, it is like booting Windows for the first time, except with all your software installed. You get to choose a new computer name, SIDs are regenerated, etc.
Each version of Windows requires the correct version of Sysprep. Where do you find the correct version of Sysprep? On your install disks in <DVD>:\Support\Tools\Deploy.cab. Although System Preparation tool for Windows Server 2003 Service Pack 2 Deployment claims to install the Sysprep tool, I’ve never been able to make it work on my system. So don’t bother wasting your time. Go to your original install media and grab the file from there.

Creating the Sysprep Image

1. Open <DVD>:\Support\Tools\Deploy.cab and extract setupcl.exe, setupmgr.exe, and sysprep.exe to C:\Sysprep. (N.B. C: is your system drive. If you installed Windows to another drive letter, use that drive letter rather than C:.)
2. Run setupmgr.exe from C:\Sysprep.
3. The Setup Manager wizard starts. Click Next…
4. Create new… Next…
5. Select “Sysprep setup”. Next…
6. Select the correct OS version… Next…
7. Select “No, do not fully automate the installation”… Next…
8. Enter Name and Organization, Time Zone, Product Key, and Workgroup or Domain. The other settings can remain defaulted. Note that you don’t want to specify the computer name since you will be creating multiple computers from the base image and you don’t want to specify the admin password, even encrypted. If the sysprep program can extract the password from the answer file, so can any hacker worth their salt. Click Next… through to the end.
9. Finish… Save to C:\Sysprep\sysprep.inf. OK…
10. Wait while Setup Manager finishes. Cancel… (Yes, odd way to exit a program that has completed successfully.)
11. Run sysprep.exe.
12. Click OK.
13. Ensure that “Don’t regenerate security identifiers” is UNCHECKED. You want to regenerate the SIDs when each new clone boots.
14. Click Reseal, OK to confirm that you want to regenerate SIDs, and wait for the system to shut down.

Creating a Cloned Server

1. If you’re using VMWare Workstation, create a linked clone of your Sysprepped server. (You can also create a new linked disk using VirtualPC using File… Virtual Disk Wizard and then creating a new VM using the linked disk.)
2. Change any VM settings such as memory. DO NOT change number of processors from 1 to 2 as the HAL (hardware abstraction layer) for uni-processor vs. multi-processor Windows is different. Your system will blue screen if you do this.
3. Boot the cloned server.
4. The Windows Setup wizard will appear. Next…
5. Accept the license agreement. Next…
6. Enter a new computer name and administrator password. Next…
7. Windows will boot and you can log in with the administrator password you just entered.
8. When prompted, click “Yes” to update your product activation.
9. Select “Yes, let’s activate Windows over the Internet now”. Next…
10. Select “No, I don’t want to register now; let’s just activate Windows”. Next…
11. OK…
12. Update this server… to go to Microsoft Update.
13. Once you’re ensured that your patches are up-to-date, you can close the browser and click Finish… then Yes… on the dialog to start using Windows.

You should now have a fresh copy of Windows. You can create as many cloned servers as you need for your mini-network.