AlexSemi's Blog

New view - Real IP address based on v_GS_NETWORK_ADAPTER_CONFIGURATION and calculated Subnet field (not a subnet Mask!!!)

alexsemi — Wed, 30 Jan 2013 17:00:00 GMT

Another custom query and a function for me. Basically, IP address is a mutivalue property, and sometimes discovery finds supernets if it can't figure out the subnet mask. That means, if you break your 10.0.0.0 subnet, it still assumes class A network, so under

v_RA_System_IPAddresses and v_RA_System_IPSubnets you'll see 10.0.0.0 and 255.0.0.0, when actually it's a class C subnet. So, the only reliable source of this information is inventory, v_gs_network_adapter_configuration. There are two problems with this inventory class that reflects Win32_NetworkAdapterConfiguration class:

It got all possible virtual adapters and it's very "noisy"
It does not contain the subnet, you'll have to do the math yourself, under "Sunbet" field it has the subnet mask

So, here is the solution.

First is the view, I called it v_gs_RealIPv4 , it tries to do it's best trying to find the only one and real IP address per machine. It gives priority to one that comes from DHCP, then it takes static ones with DNS Suffix, then it takes any valid IP address with gateway, then any address at all. If there are multiple IP addresses, it gives priority to the one with max adapter ID (kind of randomization between equal adapters).

The second one is the scalar function CustomGetSubnet that takes IP address and Subnet Mask and returns IP Subnet.

I strongly recommend NOT to create those objects in production CM Database! Create a side database on the same SQL instance, and call it CM_Supplemental, for example. Then you can refrence it in your reports or queries like this:

SELECT Name0, v_gs_RealIPv4.* FROM 
 v_r_system inner join CM_Supplemental..v_gs_RealIPv4 
 ON v_r_system.ResourceID = v_gs_RealIPv4.ResourceID

Here is the code. Change P01 to your side code and execute this T-SQL code.

CREATE VIEW [dbo].[v_gs_RealIPv4]
 AS
 
 SELECT 
 NAC.ResourceID,
 CASE
 WHEN 
 PATINDEX('%,%',NAC.IPAddress0) > 0 THEN SUBSTRING(NAC.IPAddress0, 1, PATINDEX('%,%', NAC.IPAddress0)-1)
 ELSE NAC.IPAddress0 
 END AS IPAddress,
 CASE
 WHEN 
 PATINDEX('%,%',NAC.IPSubnet0) > 0 THEN SUBSTRING(NAC.IPSubnet0, 1, PATINDEX('%,%', NAC.IPSubnet0)-1)
 ELSE NAC.IPSubnet0 
 END AS SubnetMask,
 dbo.CustomGetSubnet(NAC.IPAddress0, NAC.IPSubnet0) AS Subnet,
 NAC.ServiceName0
 from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION NAC INNER JOIN
 (
 SELECT ResourceID, MAX(Index0) as MxIndex FROM 
 (
 -- First - list those with default gateway and and DHCPServer
 select * from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION 
 where IPEnabled0 = 1
 and IPAddress0 is not NULL
 and DefaultIPGateway0 is not Null
 and DHCPServer0 is not NULL
 and DNSDomain0 is not NULL
 -- Second - thos that don't have default gateway and those are only adapters on the system, i.e. no other adapter with a gateway
 UNION
 SELECT * from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION A where
 IPEnabled0 = 1
 and IPAddress0 is not NULL
 and not exists (
 select * from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION B where
 A.ResourceID=B.ResourceID
 and IPAddress0 is not NULL
 and DefaultIPGateway0 is not Null
 )
 -- Static IPs with default gateway
 UNION
 select * from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION where
 IPEnabled0 = 1 AND
 IPAddress0 is not NULL
 and DefaultIPGateway0 is not Null
 AND ResourceID in (
 (
 select ResourceID from CM_P01..v_R_System
 where 
 ResourceID not in
 (
 select ResourceID from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION 
 where IPEnabled0 = 1
 and IPAddress0 is not NULL
 and DefaultIPGateway0 is not Null
 and DHCPServer0 is not NULL
 and DNSDomain0 is not NULL
 )
 ))
 ) A
 group By ResourceID
 ) D ON NAC.ResourceID = D.ResourceID and NAC.Index0 = D.MxIndex

And here is the function that calculates the subnet:

CREATE FUNCTION [dbo].[CustomGetSubnet](@IPAddress varchar(255), @IPSubnet varchar(255))
 RETURNS varchar(255)
 AS
 BEGIN
 
 DECLARE @Return varchar(255)
 
 --Split out IP v6
 IF PATINDEX('%,%', @IpAddress) > 0
 SELECT @IPAddress = SUBSTRING(@IPAddress, 1, PATINDEX('%,%', @IpAddress)-1)
 
 IF PATINDEX('%,%', @IPSubnet) > 0
 SELECT @IPSubnet = SUBSTRING(@IPSubnet, 1, PATINDEX('%,%', @IPSubnet)-1)
 
 IF PATINDEX('%.%', @IpAddress) > 0 AND PATINDEX('%.%', @IPSubnet) > 0
 BEGIN
 -- First Octet
 SELECT @Return = CAST(SUBSTRING(@IPAddress,1,PATINDEX('%.%',@IPAddress)-1) as Integer) & CAST(SUBSTRING(@IPSubnet,1,PATINDEX('%.%',@IPSubnet)-1) as Integer)
 SELECT @IPAddress = SUBSTRING(@IPAddress, PATINDEX('%.%', @IPAddress)+1, 1024)
 SELECT @IPSubnet = SUBSTRING(@IPSubnet, PATINDEX('%.%', @IPSubnet)+1, 1024)
 ---Second Octet
 
 SELECT @Return = @Return + '.' + CAST(CAST(SUBSTRING(@IPAddress,1,PATINDEX('%.%',@IPAddress)-1) as Integer) & CAST(SUBSTRING(@IPSubnet,1,PATINDEX('%.%',@IPSubnet)-1) as Integer) as varchar(max))
 SELECT @IPAddress = SUBSTRING(@IPAddress, PATINDEX('%.%', @IPAddress)+1, 1024)
 SELECT @IPSubnet = SUBSTRING(@IPSubnet, PATINDEX('%.%', @IPSubnet)+1, 1024)
 --Third Octet
 
 SELECT @Return = @Return + '.' + CAST(CAST(SUBSTRING(@IPAddress,1,PATINDEX('%.%',@IPAddress)-1) as Integer) & CAST(SUBSTRING(@IPSubnet,1,PATINDEX('%.%',@IPSubnet)-1) as Integer) as varchar(max))
 SELECT @IPAddress = SUBSTRING(@IPAddress, PATINDEX('%.%', @IPAddress)+1, 1024)
 SELECT @IPSubnet = SUBSTRING(@IPSubnet, PATINDEX('%.%', @IPSubnet)+1, 1024)
 --Fourth octet
 
 SELECT @Return = @Return + '.' + CAST(CAST(@IPAddress as Integer) & CAST(@IPSubnet as Integer) as varchar(max))
 SELECT @IPAddress = SUBSTRING(@IPAddress, PATINDEX('%.%', @IPAddress)+1, 1024)
 SELECT @IPSubnet = SUBSTRING(@IPSubnet, PATINDEX('%.%', @IPSubnet)+1, 1024)
 
 END
 
 Return @Return
 
 END

Modified Administrative Security > "Administrative users security assignments" report with effective rights

alexsemi — Tue, 29 Jan 2013 20:38:18 GMT

One of most useful reports is Administrative Security > Administrative users security assignments. It shows you Configuration Manager administrative users with rights, assigned to them.

The only problem with that report, is that it shows you only groups and users you added as directly as Administrative Users. But, for example, if you have NA\JDoe and you want to see what kind of access that guy has and you don't want to enumerate all his groups in AD manually, this report may come handy.

Upload it to your SSRS with Reporting Point role, change DataSource pointing to the one that Config Manager created for you, and enjoy :)

Thank you.

Computer Make / Model with Lenovo real models

alexsemi — Wed, 23 Jan 2013 22:47:00 GMT

After looking at a couple of reports / SQL queries for Lenovo models, I realized that they have some drawbacks.

Lenovo stores its model in a different class, called Win32_ComputerSystemProduct and the name for the field is "Version". So, for SCCM, we need to enable it in Inventory first, but we're not going to get all inventory immediately, but wait a minute, we actually need just _one_ computer with that model to report to see all of them. And here is what I came up with. First query mimics v_gs_ComputerSystem, second is a report with CollectionID filter.

SELECT ResourceID, Manufacturer0,
 (
 CASE 
 WHEN CSP.Model0 IS NULL THEN CS.Model0
 WHEN CS.Manufacturer0 IN ('LENOVO','IBM') THEN CSP.Model0
 ELSE CS.Model0
 END
 ) AS Model0
 FROM v_GS_COMPUTER_SYSTEM CS LEFT OUTER JOIN
 (
 SELECT Name0 as LenovoModel, Version0 as Model0 FROM v_GS_COMPUTER_SYSTEM_PRODUCT
 group by Name0, Version0
 ) CSP ON CS.Model0 = CSP.LenovoModel


 SELECT Manufacturer0, Model0, COUNT(1) As [Count] FROM 
 
 ( SELECT ResourceID, Manufacturer0,
 (
 CASE 
 WHEN CSP.Model0 IS NULL THEN CS.Model0
 WHEN CS.Manufacturer0 IN ('LENOVO','IBM') THEN CSP.Model0
 ELSE CS.Model0
 END
 ) AS Model0
 FROM v_GS_COMPUTER_SYSTEM CS LEFT OUTER JOIN
 (
 SELECT Name0 as LenovoModel, Version0 as Model0 FROM v_GS_COMPUTER_SYSTEM_PRODUCT
 group by Name0, Version0
 ) CSP ON CS.Model0 = CSP.LenovoModel
 ) SmartModel INNER JOIN v_FullCollectionMembership FCM ON SmartModel.ResourceID = FCM.ResourceID
 
 WHERE FCM.CollectionID = @CollectionID
 
 GROUP BY Manufacturer0, Model0
 ORDER BY COUNT(1) Desc

Script, that populates base variable with packages and programs based on Computer's AD group membership.

alexsemi — Tue, 09 Oct 2012 22:15:00 GMT

Here is the script, that will find all groups computer belongs to and if they start with some special prefix (defined in the code as AD_GROUP_PREFIX constant), it will take a description, that's using the same format as a base variable for Install Software step (PackageID:Program Name) and will add it to the variable defined in BASE_VARIABLE constant. Run it before Install Software step, and by just adding computers to AD groups you will get the software installed. Put this script under "Scripts\Custom" folder on your MDT Package.

LoadState captures the same profile twice for users that were migrated with ADMT/Quest tools.

alexsemi — Tue, 25 Sep 2012 19:02:05 GMT

You may run in a situation, when LoadState captures the same profile two or more times. This happens if there are two or more SIDs under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList have a value for ProfileImagePath pointing to the same folder.

Excluding the old domain with /ue:OLDDOMAIN\* does not help if offline scenario. So the only way to fix it would be deleting old orphan entries from registry. Attached script does exactly that.

It takes two parameters:
OldDomainSID and NewDomainSID.

It enumerates profiles from
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList

If it finds a profile from new
domain, it looks for the profile pointing to the same folder in old domain then
deletes that profile record in registry.

Put this script under "Scripts\Custom" folder in your MDT package, run it before User State Capture step for UDI or ZTI/LTI like this:

cscript.exe "%DeployRoot%\Scripts\Custom\FixProfilesBeforeUSMT.wsf" /OldDomainSID:S-1-5-21-1111111111-776500477-1237804090 /NewDomainSID:S-1-5-21-2222222222-3324038498-27948981

You can grab old domain and new domain SIDs from the same registry key of affected computer: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList , just take out the part after last dash.

Thank you.

Updated reports - now for CM 2012

alexsemi — Wed, 11 Apr 2012 18:50:33 GMT

I converted reports I created for CM 2007 to 2012 format: http://blogs.msdn.com/b/alex_semi/archive/2012/03/19/one-old-and-2-new-compliance-reports.aspx?wa=wsignin1.0

One old and 2 new compliance reports

alexsemi — Mon, 19 Mar 2012 20:53:51 GMT

Remember "Compliance Report, break down by months"? I created two child reports - one shows break down of patches released specifies month with compliance and if you click on patch it will show you the list of incompliant patches.

Plus I changed format - now all three reports come as a nice MOF for SCCM 2007

Here is a demo.

This is the first report you've probably already seen:

Let's check out December 2011.

By clicking on individual patch you can see the list of computers:

OS refresh fails with NTLDR can't be found error

alexsemi — Wed, 22 Feb 2012 20:53:00 GMT

Update. As Avi pointed out in comments to this post, my script does not handle root directories that invalid in Windows FileSystem space, because FSO does not see them when enumerating. I changed it to call of "DIR" command. And now I just delete them.
Another minor (but important!) change was taking out skipping deletion of "RECYCLER" folder from the script. I got a case of invalid names under RECYCLER. Please use at your own risk.

If you've been doing XP to Wind 7 migrations in larger scale, you probably came across some computers out there that ended up tossed with NTLD missing error.

Not nice! After this happens, you'd probably boot and safe user's data from state store, and try to figure out what the hack happened. Looking in smsts.log reveals some details:

Reporting deletion progress.
 Unable to delete directory C:\Documents and Settings\***\Local Settings\Temp\OICE_43BCF28F-9C21-4A31-BC1E-52B54828BD6F.0 (0x80070091). Continuing. 
 Unable to delete directory C:\Documents and Settings\***\Local Settings\Temp\OICE_930D53AE-2288-434E-97DD-C56505BB49CA.0 (0x80070091). Continuing. 
 Unable to delete directory C:\Documents and Settings\***\Local Settings\Temp (0x80070091). Continuing.
 Unable to delete directory C:\Documents and Settings\***\Local Settings (0x80070091). Continuing.

Searching on the internet is giving you some clue. Most of the posts saying it's permissions, so you come up with the script that resets permissions, but this is still happening.

So what's the culprit?

Let's see what in that folder.

 Directory of C:\Documents and Settings\***\Local Settings\Temp
 
 02/14/2012 05:50 PM <DIR> .
 02/14/2012 05:50 PM <DIR> ..
 02/14/2012 05:50 PM <DIR> OICE_1ED1F44B-623B-49CB-91A9-3D5FF12339E9.0
 02/14/2012 05:50 PM <DIR> OICE_450CAED0-70C4-4B0E-A799-4C20309918BE.0
 0 File(s) 0 bytes
 
 Directory of C:\Documents and Settings\***\Local Settings\Temp\OICE_1ED1F44B-623B-49CB-91A9-3D5FF12339E9.0
 
 02/14/2012 05:50 PM <DIR> .
 02/14/2012 05:50 PM <DIR> ..
 02/13/2012 07:28 PM 0 108D235B.
 1 File(s) 0 bytes
 
 Directory of C:\Documents and Settings\***\Local Settings\Temp\OICE_450CAED0-70C4-4B0E-A799-4C20309918BE.0
 
 02/14/2012 05:50 PM <DIR> .
 02/14/2012 05:50 PM <DIR> ..
 02/13/2012 06:12 PM 0 FE182149.
 1 File(s) 0 bytes
 
 Total Files Listed:
 2 File(s) 0 bytes
 8 Dir(s) 300,889,800,704 bytes free

Notice highlighted files. They got a dot at the end.

Bingo! Look at this answer:

http://stackoverflow.com/questions/4075753/how-to-delete-a-folder-that-name-ended-with-a-dot

Looks like OSDApplyOS does not use it to delete files, so we're in trouble.

So what creates it? I have a guess.

Looks like it's related somehow with Office Isolated Conversion Environment. So,

http://support.microsoft.com/kb/935865

I attached the script you can run if Apply OS Image failed before running Apply OS Image again.

Quick illustration:

How to use this script

This script needs ztiutility.vbs from MDT and it needs setacl.exe from http://sourceforge.net/projects/setacl/files/

If you use MDT, I recommend creating a Custom folder under Scripts on MDT package and copying the script and SetACL.exe into it.

Only If you DON'T use MDT change path to ZTIUtility from parent folder to current folder in the script

Copy SetAcl.exe, ZTIUtility.vbs and the script to a separate package in Configuration Manager.

Open your task sequence
Find "Apply Operating System Image" step
Go to options and select "Continue on error"
Create a new group, call it "Image Failed to Apply" right below the "Apply Operating System Image" step
Add a new step of type "Run Command Line" to your new group
If you use MDT and followed recommendation to copy it to Scripts\Custom use this command line: cscript.exe "%DeployRoot%\Scripts\Custom\OSDApplyOSRecovery.wsf"
If you're using a separate package, just use this command line: cscript.exe "OSDApplyOSRecovery.wsf" and Check "Package" and point it to the package you created above.
Call this step "Attempt to recover"
Go to options and set "Continue on error"
Copy "Apply Operating System Image" step and paste it under your "Attempt to Recover" step.
If you're using MDT template, you should see "Logical drive letter stored under variable" under Destination. No changes necessary.
If you're not using MDT, and it says "Next Available Partition" you should change it to Specific Disk and Partition and use a proper partition (usually partition 2 disk 0 if formatting for bitlocker)
Go to options and unselect "Continue on error" only for that step
Done! :)

TEST, TEST AND TEST

Try to create an "invalid" folder by using mkdir "\\?\C:\Screw my deployment with period at the end."

Set permissions on it removing Local System and administrators. Start OS Refresh.

Be careful with this script if you save your State Store somewhere else but \StateStore folder! Meaning - if you're running MDT 2010 U1 or earlier integration and pick and alternative location for State Store other then C:\StateStore!

Thank you.

x-mass present! _AUTOMATICALLY_ open all important logs - smsts.log, bdd.log - anything!

alexsemi — Thu, 22 Dec 2011 01:34:59 GMT

Hi!

How many times you have been looking for SMSTS.LOG checking all possible locations, finally finding the wrong version, because time in Win PE is off?

Finally, the pain is over! Attached script, ZTITrace32.wsf will open them for you automatically. See the picture.

How it works.

Script Starts another copy of itself in "TSR" mode and terminates
the new copy looks for trace32.exe in a path - IMPORTNAT, you need to inject right version of trace32 (x86/x64) to your Windows directory on you boot image
trace32 is getting registered, so it won't ask to associate itselsf with logs
Input variable LogsToMonitor, which is comma separated list, is getting parsed
WMI Notification Query to launch on changes in those files is getting registered
When change occurs, script checks, if there is a trace32.exe with this log is open, if not -
Script launches trace32.exe with that log. in PE it starts it minimized, in live OS - normal, because you won't see minimized programs in Winlogon session

So where to start it from?

First - to support your first Boot Image boot, inject it to unattend.xml like in attached example

Second - after each reboot in your task sequence. I recommend to copy it to your MDT package scripts directory and call it like this: cscript.exe "%deployroot%\scripts\ZTITrace32.wsf" /LogsToMonitor:bdd.log,smsts.log,NomadBranch.log,execmgr.log

Example.

Enjoy!

Increase CM cache and clean it - 2

alexsemi — Tue, 20 Dec 2011 20:02:23 GMT

Next version to http://blogs.msdn.com/b/alex_semi/archive/2011/12/15/increase-cm-cache-and-clean-it.aspx

Now the script goes to WMI, and checks, if each cached package required by any of mandatory assignment, and if it’s in a future, or recurring, it won’t delete.

Increase CM cache and clean it

alexsemi — Thu, 15 Dec 2011 20:42:00 GMT

Sometimes it's hard to accommodate all needed packages in SCCM cache, so admins usually increase the size of the cache to a few gigabytes. Since SCCM does not clean cache unless it needs to fit some new packages, you will end up with old versions of packages and some not really used stuff there.

Here is a script, that will increase the cache to 20GB, find all non-referenced versions (old packages, pretty much), it will also find packages, that have not been used in last 7 days (configurable) and delete them from the client cache.

It also includes two reports, one shows computers and how much space was saved, another a summary report, that shows a total of how much was saved, average and a number of computer that ran it.

Enjoy!

Remote Connection to WinPE during SCCM deployments - creating shortcuts with Ticket numbers and IP on a file share

alexsemi — Tue, 29 Nov 2011 23:37:00 GMT

Update - I've found a bug and fixed it. Now you can call this script from PE in OS Refresh scenario as well.

The well known name in Deployment world, Johan Arwidmark wrote an excelent article on adding remote control to MDT/SCCM Boot images.

http://www.deploymentresearch.com/Blog/tabid/62/EntryId/36/Software-Assurance-Pays-Off-Remote-Connection-to-WinPE-during-MDT-SCCM-deployments.aspx

Here is what he's saying:

"If you want to automate the connection to the client (for example provide a readymade link), you can create a script that reads the info from inv32.xml and creates a shortcut on the deployment server. Dart Remote Connection Viewer supports command line parameters for TicketNumber, IP Address and Port."

So I took some time to implement excactly that!

So, there is already some logic in MDT 2012 B2 for that, I just took his script, added a few parameters (like UNC share, user name, domain and a password to connect) and making it available for you :) Follow all his recommendations, but use files unattend.xml and StartRemoteRecovery.wsf from attachment to this article.

Don't forget to modify unattend.xml replacing *** with your data.

Sending SCCM Status Messages from MDT Scripts.

alexsemi — Fri, 04 Nov 2011 13:23:00 GMT

Recently, I asked Michael Niehaus would it be a good idea to send Events, that normally go to EventShare, to SCCM as a status message. Michael's response was that the challenge was Windows PE.

But still, if you're using MDT Scripting framework for all your VB Scripts (I do, and you should too, I don't see the reason why you should not :)), instead of reinventing the wheel and scanning through the logs or writing some kind of custom receiver for Event files - why not writing it directly to SCCM database as a status message using client API? This is not going to work in Windows PE, but in live OS it will work just fine.

I attached two files.

CustomUtility.vbs is an include file, add it to your WSF under ZTIUtility.vbs, like this:

<job id="ConvertProfiles">
 <script language="VBScript" src="ZTIUtility.vbs"/>
 <script language="VBScript" src="CustomUtility.vbs"/>
 <script language="VBScript" src="ZTIDataAccess.vbs"/>
 <script language="VBScript">

So, now you can call something like this in your script:

oLogging2.CreateEvent 78002, LogTypeInfo, "Migrated", Array(sDomainName + "\" + sAccountName, sSID, sMail, oEnvironment.Item("TargetDomain") + "\" + sTargetAccount, sTargetSID, sTargetGUID, sDisplayName, sProfileImagePath)

There are two parts of SCCM Status message - Attributes and Insert Strings. For attributes, this is how we re-purpose them:

400 is generally PackageID
401 is generally AdvertID
402 is generally CollectionID, but we use it for EventID
403 is generally User Account initiating action, but we use it for script name
404 is generally a distribution point, but we use it for the text message

The array of parameters (last input parameter) will be added as Insert Strings.

So, in our example, we know, that first is Old account, then goes SID, etc. Here is an example of query that will retrieve all those events for us (it's also attached to this post):

SELECT MachineName, DATEADD(ss,@__timezoneoffset,Time), DisplayName,OldAccount, NewAccount, EMail, Profile, Migrated.RecordID
 FROM 
 (select A.RecordID, A.MachineName, A.Time from dbo.v_StatusMessage A,
 v_StatMsgAttributes B
 where 
 A.RecordID = B.RecordID AND
 A.MessageID = 39997 AND
 B.AttributeID = 402 AND
 B.AttributeValue = '78002'
 ) Migrated,
 (select InsStrValue as DisplayName, RecordID from v_StatMsgInsStrings where InsStrIndex = 6) DisplayName,
 (select InsStrValue as OldAccount, RecordID from v_StatMsgInsStrings where InsStrIndex = 0) OldAccount,
 (select InsStrValue as NewAccount, RecordID from v_StatMsgInsStrings where InsStrIndex = 3) NewAccount,
 (select InsStrValue as EMail, RecordID from v_StatMsgInsStrings where InsStrIndex = 2) EMail,
 (select InsStrValue as Profile, RecordID from v_StatMsgInsStrings where InsStrIndex = 7) Profile
 where Migrated.RecordID = DisplayName.RecordID AND
 Migrated.RecordID = OldAccount.RecordID AND
 Migrated.RecordID = NewAccount.RecordID AND
 Migrated.RecordID = EMail.RecordID AND
 Migrated.RecordID = Profile.RecordID
 ORDER BY Time DESC

Do you like it?

Compliance report, break down by months

alexsemi — Tue, 03 May 2011 13:50:00 GMT

Instead of maintaining endless Update Lists with patches from each month, I created this custom report, breaking down by Months.

Creterias:

1. Category: Security patches

2. Prodcuts: All

3. Severity: Critical and Important

4. One missing patch brings entire computer to incomplicane.

Here is the query (copy/paste it to the report)

SELECT D.MonthPosted, Compliant, Incompliant, cast(Compliant / ( (Compliant + Incompliant) /100.0) as decimal(5,2) ) as PercentCompliant

FROM

(

Select MonthPosted, Count(1) as Compliant FROM

(

SELECT MonthPosted, ResourceID

FROM (SELECT TOP (100) PERCENT v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,

v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)

AS MonthPosted, COUNT(1) AS Count

FROM v_UpdateComplianceStatus INNER JOIN

v_UpdateInfo ON v_UpdateComplianceStatus.CI_ID = v_UpdateInfo.CI_ID INNER JOIN

v_R_System ON v_UpdateComplianceStatus.ResourceID = v_R_System.ResourceID

WHERE (v_R_System.Obsolete0 = 0) AND (v_UpdateInfo.Severity IN (8, 10)) AND (v_UpdateInfo.IsSuperseded = 0) AND (v_UpdateInfo.IsEnabled = 1)

GROUP BY v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,

v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)) A

where Status =3

AND not exists

(

SELECT B.MonthPosted, B.ResourceID

FROM (SELECT TOP (100) PERCENT v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,

v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)

AS MonthPosted, COUNT(1) AS Count

FROM v_UpdateComplianceStatus INNER JOIN

v_UpdateInfo ON v_UpdateComplianceStatus.CI_ID = v_UpdateInfo.CI_ID INNER JOIN

v_R_System ON v_UpdateComplianceStatus.ResourceID = v_R_System.ResourceID

WHERE (v_R_System.Obsolete0 = 0) AND (v_UpdateInfo.Severity IN (8, 10)) AND (v_UpdateInfo.IsSuperseded = 0) AND (v_UpdateInfo.IsEnabled = 1)

GROUP BY v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,

v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)) B

where Status =2

and B.MonthPosted = A.MonthPosted and B.ResourceID = A.ResourceID

Group By MonthPosted, ResourceID

)

Group By MonthPosted, ResourceID

) C

Group By MonthPosted

) D,

( SELECT MonthPosted, Count(1) as Incompliant

FROM (SELECT TOP (100) PERCENT v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,

v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)

AS MonthPosted, COUNT(1) AS Count

FROM v_UpdateComplianceStatus INNER JOIN

v_UpdateInfo ON v_UpdateComplianceStatus.CI_ID = v_UpdateInfo.CI_ID INNER JOIN

v_R_System ON v_UpdateComplianceStatus.ResourceID = v_R_System.ResourceID

WHERE (v_R_System.Obsolete0 = 0) AND (v_UpdateInfo.Severity IN (8, 10)) AND (v_UpdateInfo.IsSuperseded = 0) AND (v_UpdateInfo.IsEnabled = 1)

GROUP BY v_UpdateComplianceStatus.ResourceID, v_UpdateComplianceStatus.Status, CAST(DATEPART(yyyy,

v_UpdateInfo.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, v_UpdateInfo.DatePosted) AS VARCHAR(255)), 2)) F

where Status =2

Group By MonthPosted ) E

where D.MonthPosted = E.MonthPosted

order by MonthPosted Desc

Creating faster OEM Images with OSD and MDT.

alexsemi — Wed, 19 Jan 2011 00:00:00 GMT

Creating faster OEM Images with OSD and MDT.

Introduction

OEM scenario is where you prepare the reference computer and duplicate the hard drive using any tool or hardware duplicator.

Microsoft offers two products to accomplish OEM image creation.

1. Operating System Deployment from System Center Configuration Manager

2. Microsoft Deployment toolkit

Let’s compare two solutions

OSD

Pros:

Capable to lay down WIM image without having to run setup.exe. In OEM scenario it speeds up the process a lot

Cons:

Image is not truly self-contained. When duplicated computer starts up, it requires active Advertisement from Management point

MDT:

Pros:

Does not require active advertisement, can be completely self-contained

Cons:

Always uses setup.exe. In OEM scenario, it means that image will be extracted from locally stored WIM file back to the hard drive.

So, how to make your OEM image self-contained and fast? The solution, “best of both worlds”, would be using SCCM OSD to prepare reference computer, and MDT Media on the local drive to complete cloned installation. Some numbers - for x64 6.5GB Windows 7 image it takes about 10 minutes instead of 30 minutes.

Prerequisites

· Working SCCM 2007 SP2 Site. This does not have to be your production site, you can prepare image for the customer in the lab, and move to production when ready.

· MDT 2010 Update 1 installed on the same computer as a site server

· Your reference Windows 7 WIM image, created using automated MDT Task Sequence (out of scope here, see http://blogs.msdn.com/b/alex_semi/archive/2009/07/16/images-builder.aspx for instance)

· Files from “FastOEMImage.zip”

1. Configure ConfigMgr Integration if not done yet by going to All Programs, Microsoft Deployment Toolkit, Configure ConfigMgr Integration. Run elevated.

2. Pick first, default option called “Install the ConfigMGR extensions.

3. Add you reference Windows 7 Image to ConfigMgr

4. Add distribution point to just added Custom Image

5. Right Click on Task Sequences, Create Microsoft Deployment Task Sequence

6. Select OEM Preload Task Sequence (Pre-OEM)

7. Call Task Sequence PRELOAD-OEM

8. Create a new Boot Image package. I recommend keeping Packages sources under the same location, i.e. C:\Packages. Recommended locations for each Package:

a. Boot Image: \\<servername>\c$\packages\BootImage

b. MDT Package: \\<servername>\c$\packages\MDTPackage

c. Settings Package: \\<servername>\c$\packages\Settings\<NameOfTheImage>

9. Open Microsoft Deployment Toolkit

10. You can use existing Deployment Share or, you can create a new one

11. In case you want to use existing one (multi-purpose Deployment Share), you should create a separate Selection Profile

12. Create a folder called Post-OEM under Applications, Out-Of-Box Drivers, and Task Sequences.

13. Under Task Sequences\Post-OEM create a new Custom Task Sequence, call it POST-OEM with ID – POST-OEM

14. Replace <Deployement Share>\Control\POST-OEM\ts.xml with file from attached FastOEMImage.zip

15. Create a selection profile, call it Post-OEM and include respective Post-OEM folders from Applications, Out-of-Box Drivers and Task Sequences

16. Create a new Media, call it “Post-OEM”, with location under C:\Packages\POST-OEM-MEDIA

17. Make sure that you select the right architecture and Generate ISO image is not selected

18. Choose selection profile “Post-OEM”, the one that you created in previous steps

19. Replace C:\Packages\POST-OEM-MEDIA\Content\Deploy\Control\customsettings.ini with the file from attached FastOEMImage.zip

20. Customize Media DS Rules if needed by changing default password, suppressing/enabling dialogs, etc.

21. Right click on the Deployment Media and select “Update Media Content”

22. Go to C:\Packages\POST-OEM-MEDIA\Content\Deploy and copy install.cmd and activate.txt from attached FastOEMImage.zip to this folder

23. Go back to SCCM console, go to Software Distribution, Packages and create a new Package. Call it “Post-OEM Injection” , Setting Data Source to C:\Packages\POST-OEM-MEDIA\Content

24. Add Distribution Point

25. Go to Operating System Deployment, Task Sequences, Right click on your PRELOAD-OEM task sequence, Edit

26. Find and highlight “Prepare Computer for OEM Capture” step

27. Go to Add, General, Run Command Line

28. Call new step “LTI Post-OEM Injection”

29. Enter DEPLOY\install.cmd %OSDisk% for Command line

30. Check Package and select “Post-OEM Injection”

31. Right Click on PRELOAD-OEM, Choose Create Task Sequence Media

32. Select “Stand Alone Media” and click “Next”

33. Select USB flash drive or DVD image and make sure that the right drive letter selected

34. Answer “Yes” to the warning

35. Uncheck “Protect Media with a password”, click next

36. Click Next

37. In Distribution Points hit “Add” button in the middle and click “Next”

38. In Add variables click “Next” skipping the prompt

39. In Summary click “Next”

40. Wait about 20 minutes until completion prompt

41. Insert just created media to any of the computers or VM

42. Answer all questions by default

43. After computer reboot and you see this Deployment Wizard prompt , turn off the computer with power off button

44. Take out the drive or capture it with any duplication tool.

Now we have the image to give to OEM!

45. When running on duplicated drive, it will walk you through prompts and restore the computer from Pre-OEM state

46. Then, it will run Task Sequence in standard MDT LTI and Shutdown the computer if you used scripts attached to this article.

Collecting SCCM computer object last logon time.

alexsemi — Tue, 12 Oct 2010 23:26:12 GMT

Sometimes it may be usefull to see when the last time computer account contacted domain, so you could see which records are old and long time gone.

First you need to add an attribute to AD discovery.

Go to site management, open your site, Site Settings, Discovery Methods, open Active Directory System Discovery

Go to Active Directory Attribute

Click yellow start, and add "lastLogon"

Kick off Active Directory System Discovery

After it's collected, you can run a query like this:

select

DATEADD(hh, cast((LastLogon0 / (600000000))/ 60 as bigint), cast('1601-1-1' as datetime2) ) as LastLogonDay, * from v_R_System

order

by LastLogonDay desc

So, you can see which computers just logged on to domain, but don't have client installed, for instance.

Associate log files with Trace32.exe

alexsemi — Mon, 07 Jun 2010 15:04:54 GMT

It's always a good idea to have Trace32.exe copied to every computer to be able to take a look on the logs, and I usually do it by adding it to the image copying it to \$OEM$\$$ under deployment share, so it's always in %WINDIR% on any computer. Plus, call regedit.exe /s and import this registry file when you build the image to associate all logs and lo_ files to trace32.exe.

Changed extension of the attached file to REG, copy it to Scripts folder and call it some time early in TS with this command line:

regedit.exe /S "%SCRIPTROOT%\LogFilesTrace32.reg"

ZTIWindowsUpdate.WSF does not install all patches

alexsemi — Wed, 17 Mar 2010 13:51:00 GMT

ZTIWindowsUpdate.wsf is the script that goes to Microsoft Update or internal WSUS server and install all approved updates from there. It is a part of automated image build process I've been creating for all of my customers.

In MDT 2010 this script was optimized to reduce the number of reboots. To accomplish it, the script now remembers all installed patches, and does not install them if they have been already installed.

The problem is that some patches need to be re-installed. Let's say we run this script for the first time, then install some applications overwriting newer files and some patches become applicable again, but second run of ZTIWindowsUpdate.wsf won't reapply the patch due to reasons above. With help of Keith Garner, I wrote a simple script that will zero the list of installed updates. It's attached here, copy WSF script to your MDT share scripts folder, and add actions to your Task Sequence like shown here:

Script to add specific packages to all distribution points

alexsemi — Thu, 11 Feb 2010 19:12:00 GMT

Script to add some packages to all distribution points

As your SCCM hierarchy grows, you might found that you spend more and more time adding packages to new distribution points. In SCCM, for BDP, there is a way to add a package automatically.

This does not happen momentarily, so I wrote this simple script that will add all DPs to the package tagged with the word “$ALLDPS” in the description:

Const Site_Code="HQ2"

Set loc = CreateObject("WbemScripting.SWbemLocator")
Dim WbemServices
Set WbemServices = loc.ConnectServer( , "root\SMS\site_" & Site_Code)

Call AddDPs("SMS_Package")
Call AddDPs("SMS_DriverPackage")
Call AddDPs("SMS_SoftwareUpdatesPackage")
Call AddDPs("SMS_ImagePackage")
Call AddDPs("SMS_BootImagePackage")

Sub AddDPs(PackageClass)

Set AllPackages = wbemServices.ExecQuery("Select * From " & PackageClass & " WHERE Description IS LIKE ""%$ALLDPS%""")
For Each Package in AllPackages
Set AllDPs = wbemServices.ExecQuery("Select * From SMS_SystemResourceList WHERE RoleName='SMS Distribution Point' AND NALPath IS NOT LIKE ""%SMSPXEIMAGES%""")
For Each DP In AllDPs
  Set Site = WbemServices.Get("SMS_Site='" & DP.SiteCode & "'")
  Set newDP = WbemServices.Get("SMS_DistributionPoint").SpawnInstance_()
  newDP.ServerNALPath = DP.NALPath
  newDP.PackageID = Package.PackageID
  newDP.SiteCode = DP.SiteCode
  newDP.SiteName = Site.SiteName
  newDP.Put_
Next
Next
End Sub

Just schedule it to run, for instance, every 30 minutes, and don’t forget to add “$ALLDPS” to the description of your package.

Patch management notifications dilemma

alexsemi — Fri, 05 Feb 2010 15:08:00 GMT

It's always been challenging to choose either to check "Hide all deployments from end users" in "Software Updates Client Agent Properties".

If you decide to suppress it - you won't be able to patch the computers manually. It may be challenging for servers, where administrators want to do it manually, with notifications of service interruption involved, and so on.

If you don't suppress them, you'll face two challenges:

1. If you rely on Maintenance Windows, even if there is no maintenance window available, but deployment is active (after start time), your users will receive task bar notification

2. If your update fails, users will see it.

You may want to prevent it by setting "Hide updates" on all computers except servers. So, you set the site wide setting to "Hide" (check the box) and distribute the setting that will unhide it where needed (Servers and, for example, your "Patch testing group")

Here is a VB script for that:

' Initialize the updatesDeployment variable.

dim updatesDeployment

' Create the COM object.

set updatesDeployment = CreateObject ("UDA.CCMUpdatesDeployment")

' Set interactive mode on the client by using the SetUserExperienceFlag method

' and the newUserExperienceFlag (set with a value of 1) passed in.

updatesDeployment.SetUserExperienceFlag 1

Reference:

http://msdn.microsoft.com/en-us/library/cc145097.aspx

http://msdn.microsoft.com/en-us/library/cc143858.aspx

Report, showing all collections computer belongs to

alexsemi — Wed, 13 Jan 2010 15:25:00 GMT

Here is an SCCM report, that takes computer name and shows all collections that computer belongs to

Query used in this report:

select C.Name0, B.CollectionID,B.Name from v_ClientCollectionMembers A,

v_Collection B,

v_R_System C

where A.CollectionID=B.CollectionID

and A.ResourceID = C.ResourceID

and C.Name0= @Name

Adding config.xml, generated by scanstate.exe /getconfig, to your migration.

alexsemi — Tue, 12 Jan 2010 15:32:00 GMT

After you have fixed the problem with scanstate.exe can't access down level manifests, (http://blogs.msdn.com/alex_semi/archive/2009/12/10/when-upgrading-or-reinstalling-windows-xp-to-windows-7-using-usmt-4-0-to-migrate-system-and-user-state-settings-like-wallpaper-and-network-printer-mappings-are-not-migrated.aspx)

you may found that now it's migrating too much - for instance, the "windows classic" theme is getting migrated from XP and your migrated customers are getting non-aero look of their Windows 7.

To tell it not to migrate the theme, you should use config.xml file. First, create one by running scanstate.exe /genconfig:yourconfig.xml on your source XP machine. Find the "theme" keyword and set migrate="no"

Then, copy captured file to your x86 USMT4 package folder.

So, now, if you use MDT extensions, you should see it under C:\_SMSTaskSequence\USMT\config.xml

Then, you need to set OSDMigrateAdditionalCaptureOptions to /config:"C:\_SMSTaskSequence\USMT\config.xml" in "set variable" step in your Task Sequence right after "Capture Network Settings".

When upgrading or reinstalling Windows XP to Windows 7 using USMT 4.0 to migrate system and user state, settings like Wallpaper and Network Printer mappings are not migrated.

alexsemi — Fri, 11 Dec 2009 01:00:00 GMT

Update: MDT 2010 Update 1 addresses this issue.

Frank Rojas came up with much simpler idea of just adding an extra step to the task sequence, here is how:

http://blogs.technet.com/configurationmgr/archive/2010/02/10/solution-when-using-usmt-4-in-an-sccm-2007-sp2-osd-task-sequence-files-are-captured-successfully-but-not-settings.aspx

This works both for OSD by itself and MDT+OSD.

The following content is retired.

Levi Stevens describes this issue here: http://blogs.technet.com/configmgrteam/archive/2009/12/01/known-issue-migrating-from-windows-xp-to-windows-7-with-usmt-when-used-with-configmgr-2007-sp2-os-deployment-may-not-migrate-all-settings.aspx

If your are using MDT task sequences in OSD, you can take advantage of much simpler solution. Here is how:

Find ZTIUSERSTATE.WSF script in your Scripts folder in MDT package.

Find these lines, which will be located in the ztiuserstate.wsf script at or nearline 1198 (search for "zticopyusmt.txt"):

oUtility.RunWithHeartbeat "cmd.exe/c xcopy /iesryh """ & sUSMTPath & """""" & oUtility.LocalRootPath & "\USMT" &""" 1>> " & oLogging.LogPath &"\zticopyUSMT.txt 2>>&1 "

InstallUSMT4 = oUtility.LocalRootPath& "\USMT"

Add the following line between the two lines above:

oUtility.RunWithHeartbeat "cmd.exe/c xcopy /iesryh """ & sUSMTPath &"\DlManifests"" """ &oEnv("SystemRoot") & "\system32\DlManifests" &""" 1>> " & oLogging.LogPath &"\zticopyUSMT.txt 2>>&1 "

After inserting this line, the script should contain this logic:

oUtility.RunWithHeartbeat"cmd.exe /c xcopy /iesryh """ & sUSMTPath &""" """ & oUtility.LocalRootPath &"\USMT" & """ 1>> " & oLogging.LogPath& "\zticopyUSMT.txt 2>>&1 "

oUtility.RunWithHeartbeat"cmd.exe /c xcopy /iesryh """ & sUSMTPath &"\DlManifests"" """ &oEnv("SystemRoot") & "\system32\DlManifests" &""" 1>> " & oLogging.LogPath &"\zticopyUSMT.txt 2>>&1 "

InstallUSMT4= oUtility.LocalRootPath & "\USMT"

Update distribution points for your MDT package.

Thank you.

Applications picker for SCCM/MDT

alexsemi — Sat, 05 Dec 2009 01:18:00 GMT

BUG: when after implementing this solution the customsettings.ini is not getting processed. Download updated version. Uninstall old one before installing this one.

Recently, I was moving my customer from MDT to OSD with MDT extensions, and I was puzzled how to make a list of applications show up as a dialog before running a task sequence. MDT comes with a simple Computer Name dialog. I extended it with domain join credentials, local admin password, and, the most important part - applications picker. It goes to SCCM DB, and finds all programs from packages tagged word "Image" in the programm's description.

So, here is how to install and configure it:

Install the package, attached to this post on any x64 machine with MDT and WAIK installed. It installs all needed files under c:\Program Files\Microsoft Deployment Toolkit\ExtensionsByAlexSemi.
Supplemental Database.

You will need a database, sitting next to your production SCCM database on the same server. I usually call it SMS_Supplemental.
Open StoredProcedures.sql in SQL Query analyzer, and change SMS_<YOUR SITE CODE> to your SMS DB. Switch to your supplemental DB and run the query that will create SP for you.
I've found it easier to deal with SQL security (sorry, I know it doable without it, but I prefer simplicity here), so I create a SQL login, call it something like "MDTdatareader" and assign the password to it. I gave this account rights to read from necessary objects and execute my queries. Logon with this account and try to run 3 stored procedures you just created. To make sure they work.

Modify SCCM_Bootstrap.ini changing domainnames, accounts, etc. Everything that ends with "=" need to be filled up
CreateMDT Boot image using MDT extensions integrated with SCCM. Don't forget pre-execution hook checkbox.
Add imagex.exe from WAIK for x64 to your path variable in environment
In the elevated command prompt change the current folder and run
alter_winpe.cmd <Path to the MDT image>. This will add needed files to your image
Update distribution points for your boot image.
Now, indicate some of the programs you want to publish in OSD by going to its comment and adding the word "image" into comment field. It can be anywhere in the comment field.
Done. If you did everything right, you'll be able to see your program in the list of applications, like on the screenshot.

Does it break any existing stuff?

I don't alter any of the standard files except wizard.hta, but even if you have customized wizard.hta, you can just add two includes.

My script that alters image uses the standard scripts from MDT templates folder.

Here is what this program does in addition to applications picker:

1. Suppresses cscript.exe command window like it described here
2. Retrieves the old computer name by serial number. Meaning, if you're re-imaging the computer, it will show up old computer name even if you replace the hard drive ;)

What are the limitations?

No folders. Sorry, SCCM does not expose folders structure in views
No bundles. And you can't use dependent programs if you had an idea to do it that way, not supported in the task sequence

What are the alternatives?

Modena

Why would you consider using this instead of Modena?

Well, my program, first, does not require any extra config files (I find it kinda cool :)), second - if you have existing investment in MDT, you would probably like to stay with MDT. Otherwise, I'd recommend using Modena.

Hide cscript.exe window for MDT general purpose wizard hook

alexsemi — Mon, 30 Nov 2009 21:23:00 GMT

As you could notice at this post by Michael Niehaus, on a back there is a big "dos" window with a script being running.

This happens because OSD always runs windows scripts in pre-exec hook with CSCRIPT.EXE, even if you specify WSCRIPT.EXE in the command line.

In order to hide it, simply copy files to your C:\Program Files\Microsoft Deployment Toolkit\SCCM\ folder with files from archive attached to this post.

You will need to rebuild your MDT boot image or mount it and update files there. Keep in mind that tsconfig.ini resides in the root folder.

If you don’t want to re-create your MDT boot image, you can replacetsconfig.ini in the existing source WIM with mine tsconfig.ini and place two EXE in Deploy\scripts.

PS: Tricky part was to make it both x86/x64. Thanks god %PROCESSOR_ARCHITECTURE% worked in the command line.

Update: you'll always have to manualy add AMD64ZTIMediaHook.exe and x86ZTIMediaHook.exe files to Deploy\Scripts folder in your image. Even for new boot images, after you create them.