Yanking and sorting lines matching a pattern

One of the best investments I’ve ever made is to be proficient with a good cross-platform editor, in my case Vim. It took me a good few months before I really became comfortable with it, but those few months’ struggle yielded huge dividend since then!

So after years of Vim usage, I consider myself a power user. Yet from time to time, I come across some nifty tips that remind me why I fall in love with it in the first place: the sense of wonder, awe, beauty, and intelligence of its creators!

Here are two things I learned recently:

  • Sort based on a pattern
    I use sort and sort u all the time. sort does what the word implies, sorting all lines in the buffer. sort u (unique) does the sort, but in addition to that, removes duplicate lines. Those two commands are extremely useful.

    Yesterday I was doing some email log analysis, and had a bunch of email addresses in my file. And I thought, wouldn’t it be nice if I could sort those addresses based on domain names? So I searched the web, then looked through :help sort. Sure enough, I can absolutely do that.

    Say you’ve got the following lines:

    person1@b.com
    person2@a.com
    person3@a.com
    person4@c.com
    person5@a.com

    To sort them based on domain names, type :sort /.\+@/ in normal mode will do just that.

  • Yank all matching lines into a register
    I use :g/pattern/d fairly often. What that line does is to delete all lines inside the document that match the pattern. Since you can use regex with pattern, this can be pretty powerful.

    However, before deleting them, sometime it is a good idea to save them away. To do that, run
    :g/pattern/yank CapitalLetter

    This command will put matching lines into a register. Let use X as an example. At a different buffer, you can run

    "Xp

    And it’ll paste those lines!

Grant full permission to Windows folders and files

For a Windows application or service, it is often necessary for either the user or service startup account to have full control to its folders and files. In most cases, this is transparent to the user. It is generally being taken care of by various delegation mechanisms behind the scene. However, this can be a problem during application upgrade or migration, when one rearranges disk drive letters or mount point names, or moves things from one server to another. To resolve this, one could manually go to each file and folder property page through Windows Explorer, then the Security tab, then the Edit… button, then the Add… button, etc. This method isn’t too bad if only a handful of files and folders are involved. But it is easy to see that manual method gets old really quickly, is error prone, and does not scale.

For example, say you are doing SQL Server migration for a data warehouse instance, where there are multiple mount points and hundred of database files due to filegroup usage and table partitions. During the migration process, you’ll move them to a new location and attach those database files. To ensure SQL Server service SID has proper permission is no small task and we need an automated way.

Given this is Windows, I started searching around for a PowerShell solution. You’d think this is a common problem, and therefore it shouldn’t be too difficult to find a solution. But that’s not the case so I wrote my own. Have fun with it!

Notes:

  • Make sure you start PowerShell with “Start as Administrator”. This is to get around the issue that the creator and permission granter are different identities;
  • The first thing the script does is to give ownership to the Administrators group. During execution, you’ll see output like:
    SUCCESS: The file (or folder): “x:\MountPoints\A1” now owned by the administrators group.
    that is to be expected.
  • The script’s execution is recursive, meaning all sub-folders and files are covered.

Without further ado, here is the PowerShell function:

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

As an example, to grant proper permission to 3 folders listd in the code, you can run the following script as administrator:

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

$folders = @("x:\MountPoints\A1", "x:\MountPoints\A2\", "x:\MountPoints\B1")

$folders | foreach {
    grantFullPermission $_ 'NT SERVICE\MSSQLSERVER' }

You could also make this a short program that takes two parameters: a folder and an account. Remember to quote the parameters if they have space character.

param(
[parameter(Mandatory=$true)]$DirectoryPath,
[parameter(Mandatory=$true)]$AccountName)

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

grantFullPermission $DirectoryPath $AccountName

JiMetrics now gathers SQL Server startup account

During the last few days, I’ve refactored JiMetrics and added a new function:

  • Used Pester to create more test cases for PowerShell functions I wrote;
  • Enhanced the design and code so JiMetrics also gathers SQL Server instance’s startup account, which can be useful.

If you don’t know what JiMetrics is, go to this page to find out. It’s is a tool that uses SQL Server to gather important SQL Server metrics in your enterprise. No additional software install, no registry change, no files to copy and move around that pollute your system, it only uses SQL Server, which you already have and it just works! It has been pretty useful to me and my co-workers. JiMetrics is open source and free to use. Check it out and let me know what you think.

I will keep improving this. My next objective is to play with SQL Server 2014 column-store and see if I can convert the collection database tables to column-store.

Collecting Windows BIOS and Host Serial Number

After imporving JiMetrics yesterday so it tries to determine if the host is a VM or not, I made another improvement today: collecting host server BIOS related information and serial number.

I don’t know about you, in the past when I needed to gather a Windows server’s BIOS or serial number for troubleshooting, I typically rebooted the machine, pressed whatever function key was necessary to get into the BIOS, and coped things down. Wouldn’t it be nice if we can get it programmatically and store that somewhere for easy reference?

So today I made another enhancement to JiMetrics to address that need. Here is what I did:

  1. Added additional columns to the Windows.Host table: SMBIOSVersion, BIOSReleaseDate, SerialNumber;
  2. I made improvement to the PowerShell script, so it now uses Win32_BIOS to gather and store that in the JiMetrics database.

Together with HardwareVendor and HardwareModel, it will be easy to find the latest BIOS software for your server host. In addition, based on SMBIOSVersion and BIOSRealeaseDate, you can determine if your BIOS is out of date and decide if you want to update the host BIOS accordingly. Based on my past experience, a lot of companies have servers whose BIOS is way out of date. Armed with this information, they won’t be.

With this improvement, it is even more easier for system admins to be on top of things. JiMetrics is not just for SQL Server DBAs, a Windows admin will also find it extremely valuable. What are you waiting for? Go to jimetrics.com and get it 🙂

Determining if a Windows host is a VM in JiMetrics

I’ve been using my own SQL Server metrics collection package called JiMetrics for a couple of years. It is easy to set up. All you need are just two things: SQL Server instance and an account that has admin access to both the servers and instances you care about.

JiMetrics doesn’t do anything that will be hard to undo: no binary install, no registry changes, no cookies and temp files to store states and all that junk that pollutes your environment. Its footprint is small and just quietly collects important metrics data at an interval of your own choosing. Follow steps here and start today. As a system administrator, you will be glad to have the metrics data for analysis and decision making.

Today I made the database a bit better: I changed the database schema by adding a new column, IsVM, to the Windows.Host table. Here is the idea:

  1. IsVM tells you if a particular Windows host is a virtual machine or not;
  2. IsVM can have only one of two values: “Y” and “N”, and is not NULLable;
  3. IsVM is a persisted computed column. The computation is done via the Manufacture property of Win32_ComputerSystem class;
  4. Currently, if the Manufacure is one of “microsoft”, “xen”, or “innotek GmbH”, then IsVM value is “Y”, otherwise it is “N”.

Here is the relevant portion of the computed column definition:

	[IsVM] AS (CASE HardwareVendor 
			WHEN 'innotek GmbH' THEN 'Y'
			WHEN 'microsoft' THEN 'Y'
			WHEN 'xen' THEN 'Y'
			ELSE 'N'
		  END) PERSISTED,

Enjoy! If you have any comments on either of these questions, I’d love to hear them:
1. What other values of Manufacture in Win32_ComputerSystem indicates a VM, other than the three I listed above?
2. Are there better ways to detect if a Windows host is a VM?