Run a script when windows resumes from suspend/sleep via WMI Event Consumer

You may want to achieve some power savings, but it turns out some services or programs misbehave.
One way around this issue is to run a script when the machine resumes. We can achieve this via two OS native methods:
Task Scheduler on Vista+
WMI Event consumer on Win2K+

I am going to only show my approach for the WMI Event, but I wanted to preempt the comments about task scheduler, yes – I am aware, but there are still two advantages to running it via WMI.
Works for all Windows flavors & it is embedding the script code within WMI, so no visibility to end users that they can modify or disable.
Both methods will run the script/code as SYSTEM, which can be a problem when your issues are related to GUI apps, such as Outlook/Exchange connectivity. This limitation does not exist with the excellent 1E NightWatchman that can run scripts as the logged in user. Without NightWatchman there is no way around programming WTSEnumerateSessions () -> WTSQueryUserToken() -> CreateEnvironmentBlock() -> CreateProcessAsUser() and actually compiling an executable that our script would have to call. Maybe I will post an actual example if some requests do come in.

The main obstacle I found with WMI scripts is the deployment. Likely you want to deploy this solution on a number of machines. The classical way would be to write a MOF file and compile that via mofcomp. I decided to actually create my instances via a VBS script. Note that no matter which approach, the Instances will have to be created while running in SYSTEM. Any consumers created as a regular user would only execute if that user is specifically logged in and is a local admin.

This script will restart a specified Service on your machine. As an example only I am restarting “Wireless Zero Configuration” or in short WZCSVC which exists on all XP machines.
Each time this service stops/starts it will write into the application event log as source EAPOL. This is how you can verify the process worked.

Interesting bits worth noting from the script:
The script will force to use the 64bit version of Wscript when available, even if this script is launched via 32bit process such as the SCCM client. I do this via the WMI method Win32_Process whilst forcing to connect to the 64 bit WMI.
The following code snippet is relevant: objCtx.Add “__ProviderArchitecture”, 64 ‘Load 64bit version of WMI if available. In fact I should create another blog post just on how to escape the bit-ness via WMI of a process.

Use of vbNewLine for the script text. This is needed as VBScript is line aware and I have to somehow add the new line command into WMI. Note that vbCrLf doesn’t work for this scenario.

Windows bitlocker drive encryption recovery key entry

Seen this discussed, and actually the DOS based bios update tool warns about it, but for whatever reasons the Lenovo team decided not to show that warning on the Windows tool.

As I did not have access to the recovery key due to it being the weekend and the internal IT being unavailable I had to look at alternative recovery solutions. The Windows bootup greets me with the message:
Windows Bitlocker Drive Encryption Recovery Key Entry
Enter the recovery key for this drive

Obviously the big question is: Will restoring to the old Bios version do the trick? The answer is YES.
Now let’s hope your vendor has a DOS/CD-ROM/Floppy disk based alternative to modify the bios, because that is what you will need as you are locked out of your Windows.

Since I do not own a CDR-Drive, I’ve chosen to use a USB stick instead.
I’ve used XBoot for this purpose and found it worked well, here is the process:
1.) Get XBoot and open the program.
2.) Drag and drop your .iso file into it, and in the wizard select: Grub4dos ISO Emulation
Unfortunately I’ve found you can only add one Grub4dos ISO Emulation .iso file successfully.
3.) Boot up the affected machine via this USB key and downgrade the BIOS.
4.) Success? If not you may have chosen the wrong bios version and want to replace the created .iso file on your memory stick with another bios version.
5.) Once you can boot again, Suspend Bitlocker encryption via the Control Panel, re-do the Bios upgrade and after the reboot resume Bitlocker.

Remove SQL block comments via regex

My blog is a bit quiet sometimes, and this is because I usually want to post big projects.
However I think I should also post small code snippets as they may help somebody too.
Trevor just asked about how to detect SQL block statements via regex, and I remember I had to use such a regex in order to trim a massive SQL query into 8000 chars for a classic SCCM report.
Use this Perl regex to detect SQL block comments
/\*([^*]|[\r\n]|(\*+([^*/]|[\r\n])))*\*+/

This will not detect comments denoted by -- which you should not use anyway :)

My next bigger project is to show WMI Event Consumer driven scripts, run scripts on resume and on PC idle btw.

Side by Side assembly issues. Error 1935.

Affected OS: Vista, 2008, Win7

Error: When attempting to install redistributable (vcredist_x86.exe) we get:
Error 1935.An Error occurred during the installation of assembly ‘Microsoft.VC90.ATL,version=”9.0.30729.4148″,publicKeyToken=”1fc8b3b9a1e18e3b”,processorArchitecture=”x86″,type=”win32″‘. Please refer to Help and Support for more information. HRESULT: 0x8007054F

When attempting to install 1E Wakeup Server 6 we get:
Microsoft.VC90.CRT,processorArchitecture=”x86″,publicKeyToken=”1fc8b3b9a1e18e3b”,type=”win32″,version=”9.0.30729.4148″ could not be found. Please use sxstrace.exe for detailed diagnosis.

Further Details:
I have established that the actual DDL’s are here:
C:\Windows\winsxs\x86_microsoft.vc90.crt_1fc8b3b9a1e18e3b_9.0.30729.4148_none_5090ab56bcba71c2
C:\Windows\winsxs\x86_microsoft.vc90.atl_1fc8b3b9a1e18e3b_9.0.30729.4148_none_51ca66a2bbe76806

The manifests are also present. I’ve created the sxstrace log file, however these does not indicate any errors.  I am unsure why these trace logs did not show any error – not helpful.

MS has a KB for error 1935: http://support.microsoft.com/kb/970652
This KB did not help: Under HKEY_LOCAL_MACHINE\COMPONENTS, the offending keys do not exist.

The resolution:
Windows has a further log file called CBS.log for the component -based servicing also known as the trusted installer, which managed the installation of SxS. This log file indicated the presence of the pending.xml file, however the CBS.log also complained that the action cannot be found in the registry in the path the above KB discussed. It appears this server had an issue were the registry was rolled back but the pending.xml file remained. Manually renaming/deleting this pending.xml brought the CBS back into action. As with any trusted installer files you first have to wrest control of the file via taking ownership and setting new DACLs.

CBS.log file extract:
2011-11-02 13:57:46, Error                 CSI    0000e320 (F) Could not find pending.xml identifier in registry.

2011-11-02 13:57:46, Error                 CSI    0000e321@2011/11/2:18:57:46.567 (F) d:\longhorn\base\wcp\componentstore\com\store.cpp(354): Store corruption detected in function `anonymous-namespace’::QueryPendingXmlIdentifier expression: 0
RegistryCorruption on resource [50]”\Registry\Machine\COMPONENTS\\PendingXmlIdentifier”[gle=0x80004005]

Maybe it helps someone, also interesting that the error text from the CBS.log showed no goggle results at the time, looks like this log file is not very well known when it’s clearly very helpful.

Cannot create an instance of ole db provider “oraoledb.oracle” for linked server

You know you’re dealing with something annoying when Google suggestions cannot wait to auto complete your error message!
Yet this is how I spent a good three hours of my life on earth, just chasing forums & blog posts, even translating a Russian one into English.
I needed that Oracle data, dammit. Sufficient to say that none of the suggestions worked, I kept receiving “Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server”

Ok, lets go back to the start and list the environment.
Using my local SQL Server 2008 Express R2 on my Win7 x64 workstation, I had to query a Oracle g10 Database on one of my VMWare box.
Yes, you could use the MSDAORA provider by Microsoft for Oracle DBs, but this provider is deprecating and does not exist in x64 flavor.

It all started when i downloaded the ODAC pack for Oracle g10 (ODAC10203x64.zip). This fails to install: Java(TM) 2 Platform Standard Edition binary has stopped working
At least there are a few confirmations that it’s save to use the OLEDB Oracle g11 driver, which uses an “Xcopy” deployment. So I download ODAC112030Xcopy_x64.zip and extract it. Grab an oracle login here via bugmenot. As of Sep 2012, this is still the latest version.
Fair enough, it contains clear instructions and installs easily. I get the provider in the list of providers on my SQL server management studio – Awesome, i thought I was done!

Not so, not by a longshot, now the pain was just starting! First I went into the properties of the provider set the AllowInProcess to enabled.
Now attempting to link the server failed with the error message that I have decided to best be the title of this post. Now this post is not going to list all the possible causes there are, hell I never really want to touch this subject again after today, but I want to “broadcast” the thing that made it ultimately work for me.

Let me just say that I’ve attempted each suggestion that I found sensible, those that I think everyone else that has this issue should repeat are as below:
1.) Set NTFS permission to the install directly to Authenticated Users with Read/Execute.
2.) Enable AllowInProcess for the Provider as outlined above.
3.) Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI and remove the three values there (create a backup export if you are so inclined). This step I am the least certain about, however why would you want to reference these 3 completely outdated dll’s in the registry?)
4.) Create the Sub folder NETWORK\ADMIN in your install directory and copy a working tnsnames.ora into it(Maybe from another machine that has the full feature Oracle SQL Developer installed)

Nothing helped – so what does one do at such a point? Unleash the furry of Procmon.exe!
This tool from Sysinternals/Mark Russinovich is the best, and my only regret that day was not launching it earlier instead of scouring Google and going insane.
I’ve limited Procmon to just sqlservr.exe, as it’s the SQL Service itself that loads/handles the providers and not the ssms.exe. Also of note is that the sqlservr.exe is a 64bit process while the management studio is still just 32bit. As the server service is loading the provider, and the service process is 64bit, the provider must also be available in 64 bit format.

The ODAC112021Xcopy_x64.zip was installed to C:\Oracle.
What Procmon showed me however is that sqlservr is attempting to find the oci.dll in any folder but his! (It iterates through the %Path% sysvariable). When it finally gives up on find the dll, the SQL Service is in a unstable shape and the only way to stop the service was to kill it via taskmgr/procexp.
Clearly I can see that the “xcopy” deployment – while not giving me any error messages – it also did not set the PATH variable!
And this is what this post is really about… adding C:\Oracle and C:\Oracle\Bin to the Path variable or maybe it’s about
employing investigative tools earlier in the process instead of relying on your search engine skills.

sqlservr.exe can now find the relevant DLL’s. The OCI.DLL in the root and the OraOLEDB11.DLL in the Bin subfolder.
At this point I could query the database! If you did my steps as above and you still get the same error, I strongly suggest using Procmon.exe as I have instead of jumping to the next search result.

I’ve left out the part where it initially could not find the tnsnames.ora file and it attempted to connect via the default oracle port instead of the custom one, and as an additional note if sqlservr cannot locate the tnsnames.ora in the first attempt, it’s not going to retry again until you restart the SQL service. I was actually surprised to even see the port connection attempt in procmon, it seems this tool just keeps getting better.

So this is what I’ve done on my “Day off” from work…. I hope you were reading this while at work yourself and can get back to the real work & fun stuff, writing queries!

Reporting on 1E Nomad Enterprise activity in SCCM

Most 1E products have dedicated reporting, however Nomad Enterprise does not.
This may be because adverts are already well reported on by SCCM via Status Messages, and usually the buck stops here. However you may still want to know more
about how it actually got there…
I order to “see the Nomad story” we can actually take advantage of Status Messages as well, and hence this will allow us to report on Nomad directly in SCCM!

First we need to configure Nomad to send the status messages.
You can do this at install time using the MSI property STATUSMSGEVENTS, or after the install directly in the registry or via GPO.
The possible values for this can be found in the Admin guide, I am not going to copy paste this massive document here :)
Be careful not to simply select “Everything” as this can be quite spammy! The report I wrote uses these messages:
Evt_Rqst_Started >>> I use this to identity the Package that we download and the start time
Evt_MasterChanged >>> This is being used to see from which machine this client actually downloads.
Evt_Completed >>> If Nomad finished successful this is used to record the end time.
Evt_Error >>> If Nomad errors for any reason this is used to record the end time as well as the detailed error message.
This means I have to set my STATUSMSGEVENTS to 0x0000002064

Now onto the report itself
This is actually an older report I did for a customer back in SMS 2003 times. I was surprised to find out the query still worked fine for SCCM and the latest Nomad,
so I thought let me polish it back up and share it on my blog.
This really answers who on the subnet became the master? How long did the master take to download it from the DP, and how soon did everyone finish downloading it,
of course it also is a good tool to find machines that had any kind of error.


Here I have chosen to display one advert but for all sites and packages. I was cheeky and hard coded (non existing) DP path in SCCM halfway through to get some error messages.

SQL Challenges
There where numerous challenges in this one!
First of all the Status messages are split into many rows, I joined them back up via FOR XML as a kind of rows concatenation trick.  Let me show you.
Say you have a sales territory team: Tom, Jerry, Micky and Mouse.  (Copyright expired on these names, right? Damn you Walt)
I am going to aggregate the whole team and display the sum. However I also would like to display each Team members name in order of sales.

SELECT TerritoryID
, SUM(SalesYTD) AS TerritorySales
, Accounts = REPLACE((            SELECT
SalesPersonID AS [data()]
FROM
[AdventureWorks].[Sales].[SalesPerson] XMLSales
WHERE
XMLSales.TerritoryID = Sales.TerritoryID
ORDER BY
SalesYTD
FOR XML PATH ('') ), ' ', ',')
FROM [AdventureWorks].[Sales].[SalesPerson] Sales
GROUP BY TerritoryID

This can be run against the good old sample Microsoft DatabaseAdventureWorks.
In effect this is like a glorified subquery. The REPLACE function is added to add commas between the sales reps. The MIN and MAX function can be used to similar effect but only for the largest and smallest of the aggregated values. Some of the status messages here contain 4 rows, so concatenation them together seemed like the best course of action. Later on I can get to my desired value via the SUBSTRING command.

The next challenge was the fact that only the Evt_Rqst_Started message contained the PackageId. This is made worse if you consider that each advert may really copy down multiple packages.
The way I solved it is to first define for each Package and Advert the start and end time, and then update the previous table with the PackageId between those timeframes.
I usually do not like using temp tables, but in this case it made it a whole lot easier. Due to this it’s advisable to filter by AdvertID – if filtration is needed – instead of PackageId.

Download

Followup 11/28/2012
This report has been further developed to have additional features such as reporting on BITS and TS downloads as well.
Additionally 1E has created 7 further reports on Nomad and bundled them into a reporting pack that you can freely request from 1E.
Here are some screenshots from the improved report(s). I am leaving this original report intact as this one still works fine and is a classic report, whereas the report pack is SSRS based.

Let’s add some SCCM right click actions for 1E’s NightWatchman too!

There are 1E wake up action already for turning machines on, however there maybe also sometimes the need to turn machines off on demand (or restart, or sleep).
Thanks to the easy expandability of SCCM it’s not all that hard to do so.

First we need an XML file that defines the action we want to take. I have named this XML 1ENightWatchman.xml
Next we need to have some kind of provider. I did this as a script as I feel it allows people to take my work and expand on it as needed – even if I may have lost interest.
The provider is called 1ENighWatchmanProvider.vbs – Yes, I am THAT creative :)

Now we need to find these files the appropriate home.
The XML file MUST reside in these folders for the SCCM console to parse:
First browse to this folder <Your SCCM Folder>\AdminUI\XmlStorage\Extensions\Actions
Here you will find folders that seem to be named at random – but not so. These are the GUID of the exposed SCCM objects. We are interested in the following:

Single Machine Actions: 7ba8bf44-2344-4035-bdb4-16630291dcf6
First-level collections: fa922e1a-6add-477f-b70e-9a164f3b11a2
Sub Collections: dbb315c3-1d8b-4e6a-a7b1-db8246890f59
Collection instance. Details pane: f91c082d-bb83-44db-8ab9-907607b1dc44

Simply copy the 1ENightWatchman.xml into all 4 of these folders, provided you want to have single machine and collection based actions.
(I am most definitely not taking responsibility if you send out a FORCE shutdown to “All Systems” collection by accident!)

Also copy 1ENighWatchmanProvider.vbs into <Your SCCM Folder>\AdminUI\XmlStorage\Tools (This folder may need to be created)

How it works:
The provider will make WMI connections and starts the Nightwatchman.exe ( CMD interface of NightWatchman Client) on the remote machine and sets the defined action.
This means the account running the SCCM Console would need to be a local admin on the target machines. The Nightwatchman.exe can actually also make remote connections natively,
however I found the WMI approach gives better flexibility.

Code flow:
1.)First it makes sure the target machine is actually on (ping able). For speed purpose this is done asynchronous in WMI, otherwise each machine that is Off or behind a firewall would cause a 2-3 seconds delay!
2.)Next it will connect to the machine via WMI and read the Nightwatchman folder from the registry in case it’s not in C:\Program Files. We also determine at this point if we are dealing with a 5.6 or a 6.0 or even a x64 flavor of these.
3.)Because we are using the OnceOff command, the script now determines the local time on the target machine so we can execute the OnceOff on the next full minute mark.
4.)Now it will execute the selected command. Since we are using OnceOff for this purpose, the command will not be executed right away but with a slight delay (2-62 Seconds).
5.) Now we create a reporting popup that shows which machine failed at which point for which reason. You can copy paste this into an excel if you are so inclined.

Let’s Add additional Commands!
Great – every environment’s needs is a bit different, so I’ve made it easy so that it can be done by editing the XML file only.
Simply copy any of the existing ActionDescription whole XML tags and add/change the command and description. Note any change in the XML file require a restart of the SCCM console to take effect.

Ok, Let’s add for example a passive Shutdown – great way to ensure you’re not shutting down the bosses computer while he’s still logged in :) Adding bold for emphasis.

<ActionDescription DisplayName="Shutdown System Passively" MnemonicDisplayName="Shutdown System Passively " Description="Shutdown System only if no one is logged in">
<Executable>
<FilePath>CScript.exe</FilePath>
<Parameters>..\XmlStorage\Tools\NWMOnceOffProvider.vbs /OnceOff:YES /ShutdownAction:POWEROFF /LogOffAction:PASSIVE /countdownsecs:5 /TargetName:##SUB:Name## /TargetColl:##SUB:CollectionID## /Namespace:\\##SUB:__Server##\##SUB:__Namespace##</Parameters>
</Executable>
</ActionDescription>

Known issues
Machines with 1E NightWatchman installed but unlicensed will still return a success message.

Planned improvements:
Add support to display a custom message to the end user. i.e “Admin Ben is restarting this machine in order to Apply Windows 7 SP1.”

Acknowledgements:
First, while I work for 1E this is not a “1E Expansion” and has been created in my spare time, so don’t start sending support tickets if this tool does not work. I’ll support it on this blog or the myitforum only and you use this at your own risk.
Thanks to chiners_68 and msilva597 for the testing and having the patience to actually see it through, and now SHUT IT DOWN! <<< Download link.