SCCM: Machines that have failed an advertisement

I get tired of sifting through the standard reports that restrict you to a particular deployment, a particular advertisement, a particular collection, a particular state, etc. I often like to see details at a more global level, even if those details run to hundreds or thousands of lines, so I often write my own reports in WQL.

I decided it would be a good idea today if I could see all machines that have failed an advertisement... or more particularly, failed any advertisement. As usual, the standard report requires you to specify an advertisement and a state.

So I spent a half hour or so working out how I could get a report of any machines that had failed any advertisement. I decided it would be a good idea to optionally filter the results using an Advertisement ID in case I wanted to narrow it down. Here's the result:

The Prompt Query for the Variable @AdvertID (defaults to %) is:

begin
if (@__filterwildcard = '')
select AdvertisementID, AdvertisementName, Comment from v_Advertisement order by AdvertisementName
else
select AdvertisementID, AdvertisementName, Comment from v_Advertisement
WHERE AdvertisementID like @__filterwildcard
order by AdvertisementName
end

The main SQL Statement for the report is:

select sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0,site.SMS_Installed_Sites0, Client_Type0, ainfo.AdvertisementName, LastStatusMessageID, LastStatusMessageIDName, DATEADD(ss,@__timezoneoffset,LastStatusTime) as LastStatusTime, stat.AdvertisementID, LastExecutionResult, LastExecutionContext
from v_ClientAdvertisementStatus stat
join v_AdvertisementInfo ainfo on stat.AdvertisementID=ainfo.AdvertisementID
join v_R_System sys on stat.ResourceID=sys.ResourceID
left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID
where stat.LastState='11' /* 11 = failed */
and stat.AdvertisementID LIKE @AdvertID

I hope someone else finds this useful.

Cheers,
Sean

PS: I've extended this concept into a collection, The WQL Query for the collection is:

select sys.ResourceID, sys.ResourceType, sys.Name,
sys.SMSUniqueIdentifier, sys.ResourceDomainORWorkgroup, sys.Client
from SMS_R_System as sys
join SMS_ClientAdvertisementStatus as adstat
on adstat.ResourceID=sys.ResourceID
where adstat.LastState='11'

Excel: Listing Distinct Elements in a List

I found this info over at http://www.cpearson.com which is an absolutely awesome Excel reference. I wanted to copy these bits here so that I wouldn't lose them.

You can use a simple formula to extract the distinct elements in a list. Suppose your list begins in cell C11. In some cell, enter

=IF(COUNTIF($C$11:C11,C11)=1,C11,"")

Eliminating Blank Cells from a list

You can use a formula to return only the non-blank cells from a range. The following function will return all the cell values in a range named BlanksRange that are not empty.

Create a range name, with the same number of rows as BlanksRange called NoBlanksRange. The range NoBlanksRange must have the same number of rows as BlanksRange but it need not be in the same row numbers. Enter the following Array Formula in the first cell of NoBlanksRange, and then use Fill Down to fill out the range:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

The first N rows of NoBlanksRange will contain the N non-blank cells of BlanksRange. Note that this is an array formula, so you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }.

Note that if you do not use named ranges and enter the actual cell references, you must use absolute cell references (e.g., $B$1) rather than relative cell references (e.g., B1).

File Recovery

I had to attempt to recover a PST file from a hard disk that had massive numbers of bad blocks. How to copy the file off though? A straight file copy wouldn't work... it just kept giving me CRC errors because the 2.84GB email archive file sat across some of the bad blocks.

I knew if I could get most of the file, I'd probably be able to use the repair tool to recover most of what was in the archive.

JFileRecovery came to the rescue, recovering all but 19MB of the email archive. One of the best parts was that I didn't even need to install anything! It runs as a Java applet straight off the web page.

This one's definitely going in my bookmarks.

WSUS: Clients overwriting each other

I discovered an interesting issue recently where a number of machines that were clones of each other were using the same IDs to report to WSUS and were therefore constantly overwriting each other in the WSUS database.

A quick run of this script against the machines was able to force a change of those IDs and set a registry entry flag so that if the script were run again (such as from a computer startup GPO), it wouldn't reset the IDs again.

Set oShell = CreateObject("WScript.Shell")

sRegKey = "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate"

' suppress error in case values does not exist
On Error Resume Next

' check for marker
sIDDeleted = oShell.RegRead( sRegKey & "\IDDeleted")

' to be sure values is only deleted once, test on marker
If sIDDeleted <> "yes" Then
' delete values
oShell.RegDelete sRegKey & "\AccountDomainSid"
oShell.RegDelete sRegKey & "\PingID"
oShell.RegDelete sRegKey & "\SusClientId"

' Stop and start the Automatic updates service
oShell.Run "%SystemRoot%\system32\net.exe stop wuauserv", 0, True
oShell.Run "%SystemRoot%\system32\net.exe start wuauserv", 0, True

' Run wuauclt.exe with resetauthorizations
Cmd = "%SystemRoot%\system32\wuauclt.exe /resetauthorization /detectnow"
oShell.Run sCmd, 0, True

' create marker
oShell.RegWrite sRegKey & "\IDDeleted", "yes"
End If

We actually chose to run the script on the target machines using SCHTASKS.EXE from the Support Tools. I created a text file containing the names of all of the target machines and simply ran this script:

@echo off
FOR /F %%i IN (WSUS_Fix_Targs.txt) DO (
schtasks /create /tn "%%i_WSUS_SID_Fix" /tr "wscript.exe \\server\share\WSUS_Fix.vbs" /sc once /st 15:00 /ru domain\adminuser /rp userpassword /z /s %%i
)

SCCM: selecting objects not in a collection

Although SCCM provides a way of restricting a result set to a collection, it doesn't provide a way of excluding another collection from your collection.

It's not that hard, if you get the Collection ID of the collection that you want to exclude, which you'll find in the properties of your collection and use WQL to rule out the membership of that collection.

select sms_r_system.resourceid, sms_r_system.name
from sms_r_system
where resourceid not in
(
select sms_r_system.resourceid
from sms_cm_res_coll_[ID], sms_r_system
where sms_r_system.resourceid = sms_cm_res_coll_[ID].resourceid
)

You can also chain them together. So, for example, to execute a query for a collection that you want to patch and automatically reboot the machines, but you want to exclude your 2 collections for manual patching and suppressed reboots and for reporting purposes you want to exclude the machines that don't have a client:

select sms_r_system.resourceid, sms_r_system.name
from sms_r_system
where resourceid not in
(
select sms_r_system.resourceid
from sms_cm_res_coll_sms00001, sms_r_system
where sms_r_system.resourceid = sms_cm_res_coll_sms00001.resourceid
)
and resourceid not in
(
select sms_r_system.resourceid
from sms_cm_res_coll_sms00002, sms_r_system
where sms_r_system.resourceid = sms_cm_res_coll_sms00002.resourceid
)
and resourceid not in
(
select sms_r_system.resourceid
from sms_cm_res_coll_sms00003, sms_r_system
where sms_r_system.resourceid = sms_cm_res_coll_sms00003.resourceid
)

Cheers,
Sean

PS: If anyone can tell me how I can place a comment in WQL code, I'd love to know!

PPS: It looks like the SQL convention of using /* comment */ works as I found it in some of the inbuilt reports. I could have sworn I'd tried this and it didn't work, but maybe I had them the wrong way around. /* embarrassed grin */

Cisco: Password Recovery

Ever lost the password to a Cisco device? I've had to do this about 4 times so far during my career (Not because I lost the password personally!). The steps are similar for many Cisco devices. These are the steps to reset the enable password on an ASA5505 Security Appliance.

The following steps were designed using a Cisco ASA 5505 Security Appliance. They are not appropriate for a Cisco PIX Firewall appliance.

1. Power-cycle your security appliance by removing and re-inserting the power plug at the power strip.
2. When prompted, press Esc to interrupt the boot process and enter ROM Monitor mode. You should immediately see a rommon prompt (rommon #0>).
3. At the rommon prompt, enter the confreg command to view the current configuration register setting: rommon #0>confreg
4. The current configuration register should be the default of 0x01 (it will actually display as 0x00000001). The security appliance will ask if you want to make changes to the configuration register. Answer no when prompted.
5. You must change the configuration register to 0x41, which tells the appliance to ignore its saved (startup) configuration upon boot: rommon #1>confreg 0x41
6. Reset the appliance with the boot command: rommon #2>boot
7. Notice that the security appliance ignores its startup configuration during the boot process. When it finishes booting, you should see a generic User Mode prompt: ciscoasa>
8. Enter the enable command to enter Privileged Mode. When the appliance prompts you for a password, simply press (at this point, the password is blank): ciscoasa>enable Password: ciscoasa#
9. Copy the startup configuration file into the running configuration with the following command: ciscoasa#copy startup-config running-config Destination filename [running-config]?
10. The previously saved configuration is now the active configuration, but since the security appliance is already in Privileged Mode, privileged access is not disabled. Next, in configuration mode, enter the following command to change the Privileged Mode password to a known value (in this case, we'll use the password system): asa#conf t asa(config)#enable password system
11. While still in Configuration Mode, reset the configuration register to the default of 0x01 to force the security appliance to read its startup configuration on boot: asa(config)#config-register 0x01
12. Use the following commands to view the configuration register setting: asa(config)#exit asa#show version
13. At bottom of the output of the show version command, you should see the following statement: Configuration register is 0x41 (will be 0x1 at next reload)
14. Save the current configuration with the copy run start command to make the above changes persistent: asa#copy run start Source filename [running-config]
15. Reload the security appliance: asa# reload System config has been modified. Save? [Y]es/[N]o:yes
Cryptochecksum: e5f81433 5493266b 4e24072 d71d5cbf
2157 bytes copied in 1.490 secs (2157 bytes/sec) Proceed with reload? [confirm]
When your security appliance reloads, you should be able to use your newly reset password to enter privileged mode.

Sawmill Report Analyser

I have discovered today that Sawmill is a really cool tool for slicing and dicing data to produce useful analytical reporting information. Well worth a look if you want to produce reports from logged data from Proxies, RADIUS servers, Firewalls, Mail Server logs... you name it.

It has a bunch of preconfigured plug-ins for just about everything, will munch just about any log file you throw at it with quite reasonable performance and is very versatile in its configurability. Pretty cheap too!

I've known about the tool for a while, but never had a reall good play with it until today.

Disclaimer: I am in no way affiliated with Sawmill.