Author: ck

TIL – October 22 2016

October 22, 2016

Been a few days of learning since I last wrote one of these, but I have come back to the automatic variables page on the PowerShell documentation enough times that I think I should just blog the important parts for myself.

$? – TRUE/FALSE if the last thing you did succeeded.
$_ – Something everyone uses in posh, current pipeline object.
$Args – all the undeclared params passed to a function, try to avoid.
$Error – the array of error objects that represent a stack of the most recent errors. use $Errors[0] to get the most recent error.
$Home – full path of home directory of the current user.
$Host – methods to access information about the host we are running the PowerShell command on.
$Matches – used with the -(not)match operators, is populated with a hash table of the matched values.
$MyInvocation – name, params, values, and information about the current command. Super useful for weird introspection.
$OFS – Output File Separator, allows you to set the default file separator for array operations. Generally better to set variables to send to split, this can have downstream effects if you dont change it back.
$PID – PID of the process hosting the current PowerShell process.
$PSBoundParameters – Nice shortcut to refer to all the params to another script or function.
$PsCmdlet – mostly used in advanced functions to access the current running cmdlet’s properties.
$PSCommandPath – full path and file name of the script being run.
$PSDebugContext – if $null, debugger is not running.
$PsHome – path to PowerShell install dir.
$PSScriptRoot – directory where script is being run from, avoid if you want to support ps2.
$PsVersionTable – returns read only hash table about the current ps session.
$Pwd – a path object representing the current directory.
$StackTrace – shortcut for the stack trace of the most recent error.
$This – refers to an object that is being extended in a script property or method.
$true/$false/$null – use boolean login on all of these, nothing new here.

Read (slightly) more at about_Automatic_Variables

TIL – July 7 2016

July 12, 2016

Today I learned that VS Code (while awesome) has a few kinks to work out if you want to mix old and new powershell idioms, and its not 100% straightforward how to fix it.

To get to this point I already downloaded Windows Management Framework 5.0 (the announcement on the VS Code site points to a dead link), got VS Code, upgraded Git, and installed the Posh utility when I opened my first file and it suggested I download the extension (pretty cool that.)

I started off having a blast, but as soon as I used Invoke-SQLCommand things got a bit messy:

"Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information."

I have seen this error before and it usually involves modifying an app.config to say “I am totally ok with you mixing modes or whatever, just do what magic I need.”

I found a few related errors until I stumbled on https://github.com/adamdriscoll/poshtools/issues/192 which broke down where I should be looking (~/.vscode) and that I could create a file named

C:Users\%USERNAME%.vscodeextensionsms-vscode.PowerShell-0.4.1inMicrosoft.PowerShell.EditorServices.Host.exe

and place the following content within it:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup useLegacyV2RuntimeActivationPolicy="true">
      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>

This did not work.

After a second scratching my head I found that it looks like VS Code’s new version of powershell is using a different folder name within the hidden vscode folder, so if you check

C:Users\%appdata%.vscodeextensions

and navigate to the bin folders within it before you make your file you will be fine.

Also, make sure the app.config file matches the name of the executable being invoked (plus the extension .config) in this case I had both Microsoft.PowerShell.EditorServices.Host.exe and Microsoft.PowerShell.EditorServices.Host.x86.exe, made both files, and now I can run everything without issue.

Using Flamegraph.pl to create Flame Graphs for SQL Server Profiler, Extended Events, and more

May 14, 2016

A friend of mine was recently discussing using Flame Graphs in a project of his, and it inspired me to see how I could abuse the format to present a visual model of SQL execution.

Originally I had thought of building my own interface, but apparently the author of the above site also maintains a great repo which allows you to skip some of the more difficult steps: https://github.com/brendangregg/FlameGraph.

There is a lot of detail there about profiling software with specific tools and how to convert their output to something the tool understands, but at its core the “folded” format is fairly easy to target and if you are fine with some text manipulation you can produce your own FGs and re-use the SVG generation, search, and zoom components of this tool.

What’s the format?
It doesnt take too much effort to suss out from flamegraph’s source:
From https://github.com/brendangregg/FlameGraph/blob/master/flamegraph.pl
# frame in the stack is semicolon separated, with a space and count at the end
# of the line.

Data analysis on a list of active domain names

May 2, 2016

A friend of mine had an idea for a project based on new and upcoming domain names and wanted some help transforming the Zone files into something a little more intelligible.

While we wait for confirmation, I used an existing scans.io dataset to get him his first results, feel free to download the data and run some queries with my simple domain queries github project.

Script for non-production restores

April 26, 2016

This is some code came together as a result of straightforward requirements; a team needed to spend a significant amount of time doing manual restores, and were tired of the GUI nonsense from SSMS. But also wanted some basic flexibility due to the fact that the servers configuration may change significantly (but not during the restore.)

The code makes some assumptions:
* Assumes your SQL service account has access to the file location for the backup
* You have the permission to create new databases and select from sys.master_files (CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.)

@mobileck Are there any certs or books you’d recommend to aide in my quest to become a DBA?

July 31, 2015

It depends on if you are more of a production, developer, or devops DBA, but I would definitely read everything below:

Guide for the accidental DBA
- Brent Ozar ULTD 6 month training plan
- SQL Server Query Performance Tuning
- http://www.brentozar.com/blog/
- Aaron Bertrand
- Adam Machanic
- sqlsoldier
- MCSA Cert 461/462 for basics.

Dirty tricks in SSRS authentication

January 19, 2015

Configuring SSRS authentication to IIS can be a pain, here are some things I have done in a pinch, and almost all of them are terrible, but if you repeatedly get a 401 unauthorized with SSRS, these might be able to help.

SSRS native mode on one domain, IIS on another – DMZ

In this mode, you are normally advised to implement basic authentication, and just pass a username and password in… in some instances you may be unable to use basic, and are forced to use NTLM.

In this situation a simple method to bypass this is to create a local user on both boxes with the same password, and have that user be the one the report server grants access to, and which user is running the web server process.

Make sure this password is strong, and doesn’t change.

SSRS native mode, same domain, IIS running as local system

In some cases you may find that the system is configured to run the IIS process as local system (generally not recommended). If this is the case, you can actually grant the entire computer name in the report server permissions via its hostname and a dollar sign.

Example:

MachineHostname$

Additional Troubleshooting for Native Mode

Enable the HTTP log for the report server , it can help enormously to isolate what’s being accessed, where, when, and sometimes who.
Double check each report folder in the hierarchy’s permissions for your user, while you may think you have permission, its quite possible you don’t and that someone overrode the parent permissions.
If you get a FORBIDDEN message instead of an unauthorized, the user you are trying to run the report as may need escalated permissions on the SSRS application directory itself (on the SQL Server).
When all else fails you can grant Browser permission to “Everybody”, but be aware that means anyone can request information from your reports, and is a really really really bad idea. (If it doesn’t work, that means you are not even making the request from the same domain)

Running SQL Queries On Multiple Servers At Once Using Registered Servers In SQL Server Management Studio

September 9, 2013

I just came across a situation where I was looking at long running queries across a few production servers, I wanted to quickly and easily run my query across them all to make sure that I wasn’t missing a problem on each server where a user had left a specific query for a looong time.

Thankfully, registered servers is here to the rescue! Many people using SSMS have never even used registered servers, and if they have, they dont know about this secret context menu hiding in the GUI.

What’s a registered server?

A registered server is effectively a way to remember that you have connected to a server, and allow easy access to connecting to it again. (There is probably a MUCH more technical explanation, but this one suffices. (Want more? Check the “BOL”:page)

In SSMS you can simply right click any active server and select “Register” to bring up the prompt to register the server. If you are running more than one set of servers that should be running different queries, then you may want to create additional server groups (when we get there).

In SSMS 2008, you can simply use the shortcut CTRL+ALT+G or select “Registered Servers” under the View menu.

Once that is complete, you should be able to right click any group you have created, and select “New Query”.

When you run your query, you should notice that an additional column exists now, with the label of the server that returns the query results.

Enjoy!

Learn More

Popular Music For My Classes

September 15, 2011

This is some of the music I play for my classes, honed over a while.

Playlist 1:

Playlist 2:

Mounting ISO Images in Mac OS X Without Expensive Software

March 26, 2011

I was speaking with a colleague today about ISO files/images in Mac OS X, and when he expressed his disappointment with existing software.
I said I would show him some cheap and easy ways to mount ISO files and how to backup any CDROM using Disk Utility.

Mounting an ISO on your Mac is accomplished using the hdiutil command.
To attach an image file,

you will first need to open up your Terminal.app
type hdiutil attach -readonly filename where filename is the full unix path of your file (just to be safe). When I was testing this my command looked like “hdiutil attach -readonly /Users/ck/Downloads/cdrom-1.33.iso”.
If you run into problems or find your image type says not supported, generally because it is a .cdr file, try hdiutil attach -imagekey diskimage-class=CRawDiskImage filename.

This should mount the image in your Finder sidebar as you would expect a normal CDROM would. If we are concerned with creating images with Disk Utility, that is as simple as dragging the inserted CD onto the Disk Utility application, or selecting it within Disk Utility and selecting New Image.