Alan's Blog

Enumerate SQL Servers for a Windows Domain

Alan — Fri, 30 Mar 2012 20:39:11 +0000

In a very large environment, getting a list and keeping track of SQL installs may be come problematic. I wanted a way to track down all SQL installs, for licensing, management and security. When you install SQL, it creates a Service Principal Name (SPN) in Active Directory.

I took a script which created a list of SQL installations by looking at SPNs, and then added the information I wanted to collect. DomainSQLServerReport.vbs does the following: 1) Gets list of SQL SPN servers from AD. 2) Ping the list. 3) If there is a reply then try to connect to the SQL server using integrated authentication to query version and SQL instances.

If you are a Domain Admin, and if integrated authentication is enabled, you will get a lot of data this way. You will also find your AD to be littered by unused SPNs, which you can delete (see, for example instructions here).

If you run this from a desktop with Excel installed, you will have a nicely formatted log file. If no Excel is installed, you will have a tab delimited log.

Recursively Enumerate Local Administrator Group

Alan — Fri, 30 Mar 2012 20:25:36 +0000

Getting a list of the local administrators is a routine security task. A popular way to do this is with EnumLocalGroup.vbs, a script written by Richard Mueller. I found that this has some error handling issues, especially where you have the problem of a nested group which creates a recursion loop.

EnumAdmins.vbs is my version of this file. Enumeration failures are logged. It only runs locally.

Who logs onto this computer?

Alan — Fri, 03 Feb 2012 19:47:19 +0000

One of the questions that is frequently asked in a large organization is, “Who uses computer XYZ123?”. Many tools will report the current user, but the current user may or may not be the the person who usually works on a given computer. The current user for the computer you are logged on logged onto to fix is probably not the name you want.

In pre-Windows 7 days, I used to pull the user information from the registry location HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\DefaultUserName. I recently found out that this has been moved to HKLM\Software\Microsoft\Windows\CurrentVersion\Authentication\LogonUI\LastLoggedOnUser for Windows 7.

Unfortunately when I went looking in the new location, there was nothing there. The missing LastLoggedOnUser was a weird problem. After some Google search time I found that if you use a GPO entry to clear the last username then LastLoggedOnUser is not populated.

I then spent some time with WMI, and found some interesting information is available in Win32_NetworkLoginProfile. I ended up writing two scripts: LastUserLogon.vbs gets the last user for a computer, and TopComputerUsers.vbs which collects the top 5 interactive logons for a computer. Both let you copy the data to the clipboard if Word is installed, otherwise the information can be output to notepad.

The TopComputerUsers script is interesting because WMI contains a count of user logons, and I use a disconnected recordset to sort the user information by number of logons. The LastUserLogon gives you a subset of the information from TopComputerUsers, and can help you determine whether a given computer is underutilized.

Both will work on local or remote computers. Both take a computer name as an argument. And both scripts tell you who the current user is.

Why I am not monetizing my blog

Alan — Tue, 24 Jan 2012 20:26:08 +0000

I am still resisting the call from my friends to monetize my blog. This means that there is no advertising here, now or later. I see this primarily as an ethical issue. You, as taxpayers, have already paid for this work. I, as a federal employee, have already been compensated for the work.

Uninstall All But the Most Recent Version of Java

Alan — Tue, 10 Jan 2012 19:50:41 +0000

UninstallJava.vbs creates a list of programs with “Java” in the name. I then uninstalls all but the most recent version. You can run it locally or against a remote computer.

Finding Name Resolution Problems

Alan — Mon, 12 Dec 2011 21:01:18 +0000

Name resolution problems are a plague for system administrators. If you think you are working on ComputerA but instead are working on ComputerB much gnashing of teeth may result. In our environment we have NetBIOS and FQDNs to resolve. The correct name, of course, is the one in the registry.

NameResolution.wsf checks the registry, FQDN and NBTName for computers. Any name mismatches are noted. This is an example of a WSF front end that I wrote. The front end lets you run the script against a single file name, a list of files typed, a file of computer names, or a list from AD. When you run it against more than one computer it is multi-threaded. The results are written to a file on your desktop. I had some strange issues with the .Run and .Exec methods for wshShell on a Windows 7 x64 system. These returned “file not found” when I tried to run NBTSTAT. I rewrote the script to use the WMI Win32_Process create method instead. (Later I discovered that this was related to an old version of PrimalScript.)

Soon I will post a script to fix PTR errors.

Can’t find %HOMESHARE%\Favorites\.

Alan — Mon, 12 Dec 2011 20:58:13 +0000

I tried to update Adobe Reader using my domain based admin account for elevation, and got this installer error “Can’t Find %HOMESHARE%\Favorites\.” To fix it I opened RegEdit, navigated to HKCU\Environment, and added a new expandable string value named HOMESHARE, and pointed it to the proper location.

Scriptomatic on Windows 7 Solved

Alan — Sun, 20 Nov 2011 22:36:40 +0000

I have been writing HTA files for a years as a way to improve the appearance of some of my scripts. MSDN has a good article from May of 2011 about the power and utility of HTA files. After moving to Windows 7 x64, I was disappointed to find that one of my favorite HTA files, Scriptomatic, had quit working. Scriptomatic is a Microsoft utility that writes WMI code. Unfortunately when running Scriptomatic I got this error on line 177, character 4: “Could not complete the operation due to error 80041003”. If you look at the code the line is: Set objWMIService = GetObject(“winmgmts:\\” & _strWMISource & “\” & strNameSpace) If you look up the error you find that it is WBEM_E_ACCESS_DENIED, “Current user does not have permission to perform the action.” If you search this problem with Scriptomatic in Google, you will find a slew of answers concluding that your only choice is to run the tool as an administrator. This is true with the code as is, but I thought it would be more interesting to fix the problem, which is ultimately an error handing issue. Scriptomatic tries to enumerate all of WMI. An ordinary user cannot access all of WMI, so it fails with a permission error. My version of Scriptomatic (Fixed_Scriptomatic.hta) adds error handling so an ordinary user can enumerate classes where there is permission to do so. Why do this? I have another project where an ordinary user uses an HTA to get computer information. I wanted to make sure that there would be no problems. My version of Scriptomatic adds error handling. I have commented all of my changes. If you are uneasy, then I suggest you download the original file and compare. If you run my fixed version as an ordinary user, it will take a really long time to open. On my system, a full two minutes. But the resulting list will be good for the current user’s credentials. Note that HTA files run best from the local computer.

(Update: 1–24–12. Fixed the embarrasing lack of a link to the file)

Floppy Drive Settings for Virtual PC

Alan — Wed, 16 Nov 2011 20:40:50 +0000

Earlier I wrote about using WinImage to preserve floppy disk data. In my continuing adventure with Windows 7 x64, I found that DOS programs will not run under x64. What DOS program do I like to use? The WordPerfect Editor from the WordPefect Shell. (Curious? You can get it here). Why? Because it allows you to easily replace formatting codes like TAB and NewLine. It also permits macros.

I figured I would be able to run the WP Editor from a floppy image. WinImage works with FileDisk to allow you to mount floppy images so they are available as drive letters within the Windows OS. So I made the bootable floppy, injected the files I wanted, and went into Virtual PC to map the floppy image to the VM. What I failed to notice was that Microsoft no longer shows the floppy drive in the Virtual PC UI. Howerver, a search of the internet found that floppy drive assignments were still available programatically with Virtual PC.

Being a scripting guy, I decided to write a script that sets and clears floppy drive settings. The result is SetVPCFloppy.vbs. Some odd things about this script. First, do not run it as an administrator. If you do, the activex control throws a permission denied error. Instead, run it with the permissions of the owner of the virtual machines. Also remember that you will need to change the boot order in the virtual BIOS if you want to boot from the floppy. Use the DELete key to enter BIOS setup.

Unfortunately, I discovered after all this work that the DOS virtual machines do not have the Virtual Machine extensions available. I found some instructions as to how to do this, but the extension failed to work on my system. When the Virtual Machine Folder Sharing begins to load I get “Folder Sharing is not supported on this host”. As a result, I am unable to cut and paste into the DOS window and cannot share data. A disappointing result. If you have been able to get the extensions working, please share your experience.

RunAs context menu item for scripts and HTAs

Alan — Wed, 16 Nov 2011 15:16:55 +0000

I have consolidated a couple of registry files that enable a RunAs context menu item for VbScript, JScript, and HTA files. Scripts_HTA_Runas_reg.txt

Fixing Windows Update problems on multiple computers

Alan — Thu, 10 Nov 2011 13:37:35 +0000

I have my WSUS Repair HTA for working on single systems, but how about multiple computers? For that I run WindowsUpdateFix.cmd. You can execute this many ways. I use SCCM or PSExec.

Upgrading to Windows 7

Alan — Wed, 09 Nov 2011 19:55:15 +0000

We have been slow at work in our adoption of Windows 7. I use my laptop as my primary workstation, and I had “cheated” when I upgraded from XP to Vista and then Window 7 32 bit. My new OS was to be Windows 7 64 bit, and there is no upgrade path. Before I kicked off the test “side by side” OSD load of Win7 64 on my laptop, I wanted to ensure that I did not lose anything.

I started by unencrypting my drive and then capturing it using disk2vhd from SysInternals. I then checked that I had done okay by mounting the drive in the existing Windows 7 32 bit OS. You can do this from the GUI in Disk Management. It looked fine, and I went ahead with the OSD load.

Side by side was supposted to use the USMT to keep my settings. It failed. I tried to use the Easy Transfer Wizard to read the USMT cabs. It failed. I then decided that the clever thing to do was to open my VHD file as a VM in Windows Virtual PC. It failed with “Unmountable_Boot_Volume. Why? There is a 127 GB size limit to VHD files. Found a good article about it here. Instead of starting from scratch, I found a utility, VHD Resizer to shrink the size of the volume. But first, I had to mount the drive in Disk Management to move files off.

Having shrunk the VHD, it mounted without incident. I used the Windows Easy Transfer Wizard to move my files from inside the VM to the host computer. Note that the network transfer failed, and I had to put the CABS on a file share.

I did end up with a working laptop. And now I hope to catch up by posting some of the scripts I have written.

Find and Reset RDP Sessions

Alan — Mon, 24 Oct 2011 20:12:34 +0000

We have a lot of servers. When I get disconnected, I may leave hanging sessions on those servers which I forget to properly close. Then, after I change the password for my admin account, I sometimes get locked out as a result of what I had open in the now disconnected session. Tracking this down is always annoying and time consuming. When users (including administrators) are terminated, a there is a similar problem — you want to find and reset those sessions, too. ServerSessions.vbs is a script for admins to run to track down sessions on servers and optionally reset them. You can list sessions on all servers in a domain, and reset either all or just disconnected sessions. I have scripts for finding users, too. LocateUsers.vbs finds a user based on their home drive sessions.

Check SCCM TempDB space

Alan — Thu, 13 Oct 2011 13:14:55 +0000

Sometimes a poorly framed query or collection can cause SCCM collections to be left forever in an evaluation state. As a result, the TempDB for the SCCM server can grow to the point where it fills the available drive space. When that happens, SQL stops, and SCCM stops.
Some advice: TempDB is recreated when the SQL Server service is restarted. This is your first action to clear the problem. You should thereafter restart the SMS Executive service.

As the tempdb begins to grow, you can find out what is going on by looking at reports. In the SQL Server Management Studio, expand system databases, right clicking on tempdb, and selecting Reports/Standard Reports. “Top Transactions By Age” will show you what has been stuck, and can give you evidence to track back from there.

In a large SCCM environment, keeping track of the tempdb can be time consuming. SCCM TempDB Space.vbs will connect to the SCCM database and report tempdb size, path, and free space from all site servers. The script has some interesting combinations of WMI queries and SQL queries.

1E Wakeup Script

Alan — Thu, 25 Aug 2011 15:23:08 +0000

Saving energy by putting systems to sleep or by turning them off can have a big return in a large enterprise. 1E’s Nightwatchman and Wakeup allow you to power on the systems with SCCM and other products. But what if you are running a batch file or a script? Or what if you want to RDP to a computer that is turned off? 1EWakeup.vbs is a modification of a script by Reto Egeter posted at MyITForum. I made a large number of changes. My version works interactively, but it also supports a command line, and pings the computer both before and after the wakeup is sent. Errorlevels are set so that the reason for failure can be determined in a batch file.