• Doug Finke’s – ImportExcel module.
• Warren F’s – PSExcel module

When I started writing the solution, I used Doug’s module because it is as easy as it can get, I was able to write and “test” the code in under 30 minutes. Basically, all I had to do was:

• Import the module
• Call ‘ConvertFrom-ExcelSheet’ with the proper parameters

Viola! you can now convert Excel Workbooks with multiple sheets in them.

As much as I hate reinventing the wheel, customers sometimes want you to do stuff that simply does not make any sense. So, I had to write a script that did not use the modules – sigh!.

However, while working on the script I encountered two small issues and thought, it might be useful for others to know about this as well.

• Figuring out the bottom most populated cell in the sheet.
  • Because the excel sheet that I had did not have any data in the first three rows, Dimension.Rows or Dimension.Columns was not working me. This I believe is because, If a range of cells is available Dimension returns the properties for the range. However, the End property does get the row and column of the bottom right cell. The original code with the cells to be changed highlighted and then follows the changed code.

$xl = New-Object OfficeOpenXml.ExcelPackage $FullName
$workbook = $xl.Workbook
$worksheet=$workbook.Worksheets[$Sheet]
$dimension=$worksheet.Dimension
$Rows=$dimension.Rows
$Columns=$dimension.Columns

Difference when there are empty cells in worksheet

$Rows=$worksheet.Dimension.End.Row
$Columns=$worksheet.Dimension.End.Column

• Figuring out the active worksheet
  • In order to export the data, the end user wanted to use the last active sheet and not the first sheet. Based on what I have read the below code, may not work if the excel workbooks were created programatically. It worked for me because the Excel workbooks were created manually so, there was always one and only one active sheet.

$activeTabIndex = [Int]$($xl.Workbook.WorkSheets | Where-Object {$_.View.TabSelected -eq $true} | Select Index).Index
$workSheet = $workBook.Worksheets[$activeTabIndex]

References:

• C#: Getting the number of rows/columns with ExcelPackage
• Determine last active sheet using EPPlus

The impetus for this post comes from the fact that I was looking for an way to get a list of all posts that I have written in the recent past which are tagged as PowerShell. Sure, I can browse by the tag and get the results; but I wanted to have a complete list at hand. That is where the WordPress API documentation came in handy.

If you have PowerShell V3, then you can the Invoke-RestMethod cmdlet to parse the results into objects and play around with them.

The structure of the URI for GETting posts is of the form:

https://public-api.wordpress.com/rest/v1/sites/$site/posts/

Where $site is the name of your site.

For my site the query is:

https://public-api.wordpress.com/rest/v1/sites/sqlchow.wordpress.com/posts/?number=50

Where 50 is the number of posts that I want to retrieve. How about the tag, you say? The query is only slightly different:

https://public-api.wordpress.com/rest/v1/sites/sqlchow.wordpress.com/posts/?number=50&tag=PowerShell

Since Invoke-RestMethod automatically converts the returned data into objects based on their type, we need not worry about the kind of response we get from the web page.

All we need to do is query the service and get our data.

$sqlchowBlogData = Invoke-RestMethod -uri "https://public-api.wordpress.com/rest/v1/sites/sqlchow.wordpress.com/posts/?number=50&tag=PowerShell"

$sqlchowBlogData | Get-Member
TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition
----        ----------   ----------
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()
GetType     Method       type GetType()
ToString    Method       string ToString()
found       NoteProperty System.Int32 found=28
posts       NoteProperty System.Object[] posts=System.Object[]

Let us check, how many posts we found:

$sqlchowBlogData.found
28

Now, let us see we can get all the post titles and the dates they were published. The data that we need is in the posts object. Let’s get the data.

$sqlchowBlogData.Posts | Select Title, @{Name="Published Date"; Expression={Get-Date ($_.Date) -Format "MMM-dd-yyyy"}} | Format-Table -AutoSize

title                                                                              Published Date
-----                                                                              --------------
Windows Azure setup for using with PowerShell                                      Mar-30-2014
Using verbal expressions to make regex easy in PowerShell                          Sep-07-2013
Down the rabbit hole with PowerShell and Windows Azure SQL Database                Sep-02-2013
Google search results using REST (custom search api) in PowerShell                 Aug-19-2013
Filtering data from SQL*Server Errorlogs                                           Aug-18-2013
.......
.......
Reading a tweeters timeline using PowerShell-Take2                                 Dec-27-2011
Reading a tweeters timeline using PowerShell                                       Dec-26-2011

Now that we have the details, we can use ConverTo-HTML to maintain an offline copy of certain posts that we need.

RESTful services are all around us, PowerShell gives us an easy way to interact with them. The possibilities are endless .

Cheers!!

First things first, we need a Windows Azure subscription. Presently, you can register for a free trail at www.windowsazure.com.

Note: If you would like to use the management portal for Windows Azure to create the database, you can use the following tutorial.

Pre-Requisites

We need a few tools and a little bit of setup on the management portal before we can start with the automation part.

1. We need to rename our Windows Azure subscription from the default name it has. This makes it easier to manage your subscriptions, if you have multiple services.
2. We need to upload a certificate to the windows azure subscription.
3. Download latest version of Windows Azure PowerShell, from here. The link points to the standalone installer.
4. Download the latest Windows Azure SDK for .NET from here.

1. Renaming the subscription

The Windows Azure services have two kinds of accounts.

1. Windows Azure Account, through which resource usage is reported and services are billed. Each account is identified by an email account, and is associated with at least one subscription. The account owner monitors usage and manages billings through the Windows Azure Account Center.
2. Subscriptions, which governs access to and use of Windows Azure subscribed service. The subscription holder uses the Management Portal to manage services.

The reason for this kind of setup is because in a corporate enrollment, an account owner might create multiple subscriptions to give IT folk’s access to services. Since resource usage within an account is reported for each subscription, an organization can use subscriptions to track expenses for projects, departments, regional offices, and so forth.

This is explained in detail here.

You change your subscription details by using the following steps:

1. Sign into the Windows Azure Management Portal.
2. Click on ‘Account’ → ‘View my bill’; you maybe prompted to login as you will be accessing sensitive details.
   

   How to: Change Subscription details

    

3. Click on ‘Edit Subscription Details’, to change the name of your subscription.

HowTo: Change Subscription Name – 1

HowTo: Change SubscriptionName-2

2. Creating and uploading a management certificate

Certificates used in Windows Azure are x.509 v3 certificates and can be signed by another trusted certificate or they can be self-signed. For the purposes of this blog we will be creating a self-signed certificate using ‘makecert.exe’. Before we go further, let us check how different certificates are used in Windows Azure.

Windows Azure has 3 types of certificates:

1. Management certificates – Stored at the subscription level, these certificates are used to enable Windows Azure using various tools. These certificates are independent of any hosted service or deployment.
2. Service certificates – Stored at the hosted service level, these certificates are used by your deployed services.
3. SSH Keys – Stored on the Linux virtual machine, SSH keys are used to authenticate remote connections to the virtual machine.

Windows Azure uses certificates to identify a trust relationship i.e. the party to be trusted has the private key. Here is how the certificates are used:

• Management certificates (.cer certificate files): the client connecting the service needs to be trusted and has the private key. These permit clients access to resources in your Windows Azure subscription.
• Service certificates (.pfx certificate files): the service needs to be trusted by the client connecting to the service. For example, in an SSL secured service scenario they provide secure interactions for users of your web application or service.

Enough talk, let us start by creating a self-signed certificate. To create your own self-signed management certificates, open a Visual Studio command prompt as an administrator, and then run the following command:

makecert -sky exchange -r -n "CN=" -pe -a sha1 -len 2048 -ss My ".cer"

If you are like me, you would get a doubt about what the parameters mean. Especially what ‘pe’ and ‘sky’ mean?

To put simply, unless we specify the -pe command line option to MakeCert, the certificate created can only be used to decrypt data on that machine. This is not an ideal scenario because, this would prevent any other machine from being able to decrypt the payload encrypted using the public key.

The option ‘sky’ specifies the type of the subject key. It accepts two arguments (signature|exchange). What these arguments mean is:

• ‘signature’ the certificate can only be used to sign a payload.
• ‘exchange’ the certificate can be used to sign AND/OR encrypt a payload.

Now that we have created our certificate, we need to export it in order to be able to upload the file to Azure. We can do this in a few easy steps:

• Start / run / certmgr.msc
• Navigate the tree hierarchy until you find the certificate you just created. (usually under Personal→Certificates).
• On the left pane right click on the certificate and select / all tasks / export.
• Select “No, do not export the private key”
• Click on next until you are asked for the Export path. Enter the desired export path.
• Follow the prompts and finish the export.

Uploading the certificate:

Management and service certificates can be uploaded through the Windows Azure Management Portal.

HowTo: Upload Certificate

3. Download latest version of Windows Azure PowerShell, from here. The link points to the standalone installer.

    Please download and install from the link given above.

4. Download the latest Windows Azure SDK for .NET from here.

    Please download and install from the link given above.

This is it. We are now setup to work with our azure subscription from within PowerShell.

References:

1. SQL Database – Documentation.
2. Manage Certificates in Windows Azure.
3. Create and upload a certificate for Windows Azure.
4. makecert option -pe.
5. makecert option -sky.

‘Regular Expressions (regex) are my favorite programming tool’

said no one ever.

When I first came across them, I was a bit intimidated as many of us would be. They looked like complete gibberish. But, due to the nature of the code I was writing, I had to learn (or at least understand) them. Parsing SQL*Server error log files or netbackup logs, is not easy in Perl without regular expressions.

After using them a few times you realize that they are basically a tool to find specific patterns within a given text. But, you hit barriers when you try to use them to capture complex patterns. For example, consider this regex pattern:

'^(?:http)(?:s)?(?:://)(?:www\.)?(?:[^\ ]*)$'

Well what does it do? Not very easy to figure out right? But, it is the regex pattern for a valid URL; true story.

This is where verbal expressions steps in. Verbal Expressions is an open-source project that aims to simplify the task of writing difficult, complex regular expressions by providing a means of specifying regular expression patterns using easily readable text.

What it means is that, we write ‘Four times X plus 3 equals seven’, and get ‘4x+3=7’. That example, could be a little misleading so let try it again, a valid url would consist of http at the begining of the line, maybe s if you are accessing a secure site, then it will have ://, maybe www. if you are so inclined to type and anything but a space. The aim of verbal expressions is to let you type the above and get a regular expression that matches a URL which is this: this:‘^(?:http)(?:s)?(?:://)(?:www.)?(?:[^\ ])$’.

Now, we need not be afraid of regular expressions.

How to start

It’s easy, just import the module.

Import-Module -Name Poverb

Note:This is currently a partial implementation only.

How To use it

Currently the output is only a string that has the regular expression you desire.

	Get-RegexFromVerbex -Verbex {
		startofline
		then "http"
		maybe "s"
		then "://"
		maybe "www."
		anythingbut " "
		endofline
	}

the output is:

	'^(?:http)(?:s)?(?:://)(?:www\.)?(?:[^\ ]*)$'

How do I get it

You can get it from github, PowerShellVerbalExpressions

Other implementations

You can view all implementations on VerbalExpressions.github.io

References

1. Jim Hollenhorst – The 30 Minute Regex Tutorial
2. VerbalExpressions.github.io
3. Peder Søholt
4. Karl Prosser for writing Invoke-Ternary filter
5. Doug Finke
   • For tweeting about Verbal expressions
   • For writing about DSLs in his book

1. Part-1: Getting ready
2. Trapping the IP Address for creating firewall rule
3. Part-2: Getting to use PowerShell

If you are looking for really cool ways to interact with, or use ‘Invoke-RestMethod’ cmdlet then, I would suggest reading through Doug Finke’s (B|T) post on getting the newest powershell questions from stackoverflow.

• Getting newest powershell questions from stackoverflow

Or, my own post on getting google search results using the custom search API

• Google search using custom search api using REST API

Note: Both Doug and Max are PowerShell MVPs and have great blogs that have a ton of information on them.

If you are already set with a Windows Azure subscription and have your details at hand, let’s start the journey.

While I was reading the first part of Max’s blog, I was reading through msdn to catch up on some of the fundamentals of Windows Azure, why we need to upload a certificate, what are the different types of certificates and what SQL database was etc… you know, basic stuff; so that I do not make silly mistake and have to rework from scratch. So, anyways while I was url-hopping I found Management REST API reference. Since, I was fresh out of writing blog about REST API and Invoke-RestMethod, this piqued my interest a lot. But, I had to resist the temptation to branch out. After finishing through Max’s blog and exploring on my own for a couple of hours, I was able to setup a SQL database server and SQL database.

Although, it is easier to use the ‘Windows Azure PowerShell’ to setup and manage your Azure services; the allure doing it using web API finally caught up with me.

The Database Management API is a REST API. All operation requests are sent encrypted over the Secure Sockets Layer (SSL) and authenticated using X.509 v3 certificates. Database Management API requests can be initiated from within Windows Azure, or directly over the internet from any application that can send HTTPS requests and receive HTTPS responses. All requests are sent to the SQL Database Management Service on port 8443 using the following base URL: https://management.database.windows.net:8443

Since, I had the REST API URL reference I started working through the URLs they had given. I was naive to think that I could get away easily :-). One thing to remember however is that, ‘The SQL Database Management Service does not support asynchronous REST API calls’; do not know what it means but, it seemed important to point out (actually, just saw it on the reference page and was trying to be funny).

The approach is straight forward, there were a few gotchas and there is risque behavior with blatant disregard for security; flame shield on as this a proof-of-concept implementation. Keeping that in mind, let us start with:

Creating a new SQL Database Server.

The url for this is:

https://management.database.windows.net:8443/{SubscriptionID}/servers

Doesn’t look like much but, let us look at what the documentation says. It says, we need a pre-formatted request body that looks like this

<?xml version="1.0" encoding="utf-8"?>
<Server xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <AdministratorLogin>MyAdminAccount</AdministratorLogin>
  <AdministratorLoginPassword>MyAdminPassword</AdministratorLoginPassword>
  <Location>East US | North Central US | South Central US | West US | North Europe | West Europe | East Asia | Southeast Asia</Location>
</Server>

OK, not bad. We have here strings and we know PowerShell loves XML for more than one reason. Let us quickly hack a request togehter and throw it at the server.

#The setup
$subscriptionid = "xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx"
$thumbprint = Get-ChildItem Cert:\CurrentUser\My  | Where-Object {$_.Subject -LIKE "CN=sqlchow80it032"} | Select-Object Thumbprint
$certificate = Get-Item Cert:\CurrentUser\My\$($thumbprint.Thumbprint)
$admindetails = Get-Credential -Message "What credentials do you want to use while accessing Windows Azure"
$location = "Southeast Asia"

#The request body, uri and headers
$xmlBody = @"
<?xml version="1.0" encoding="utf-8"?>
<Server xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <AdministratorLogin>$($admindetails.UserName)</AdministratorLogin>
  <AdministratorLoginPassword>$($admindetails.GetNetworkCredential().password)</AdministratorLoginPassword>
  <Location>$location</Location>
"@

$resturi = "https://management.database.windows.net:8443/$subscriptionid/servers"
[byte[]]$bytebody = [System.Text.Encoding]::UTF8.GetBytes($xmlBody)
$contenttype = "application/xml;charset=utf-8"
$headers = @{"x-ms-version" = "1.0"}

#Lift-off
[xml]$createresponse = $(Invoke-RestMethod -Method POST -Uri $resturi -Certificate $certificate -Headers $headers -Body $bytebody -ContentType $contenttype -Verbose).Remove(0,1)

$createresponse.ServerName
xmlns                                                       #text
-----                                                       -----
http://schemas.microsoft.com/sqlazure/2010/12/              vo0iyrxe4i

It looks like we have a lot going on in this one request. So, let us break it down by region.

• In the setup:
  • We are getting the subscription ID that uniquely identifies our subscription.
  • We are also getting the certificate that is used to authenticate our requests to the management service.
  • We then get the service administrator credentials and set the location where our server will be created.
• In the request body, uri and headers:
  • we are creating a request body as a here-string.
  • we are preparing the URL and then encoding the request body.
  • we are then setting the content type and headers.
    • One important thing to note is that the header is mandatory and it’s value is always ‘1.0’.
• In Lift-Off, we just send the request to the server and check the response.
  • yes, the lift off is not quite straight-forward you may wonder why the hell is this guy doing it like this. I will bring this up in a little bit.

As expected (lucky, there were no errors :)), we have a response that conforms with the expected response body:

<?xml version="1.0" encoding="utf-8"?>
<ServerName xmlns="http://schemas.microsoft.com/sqlazure/2010/12">
    TheNewServerName
</ServerName>

Our new server name is “vo0iyrxe4i”. As much as I would love to control the naming, at this point it is pretty random. The question that needs to be answered now is, how do we know if the database server was actually created. Let us find out.

Getting the SQL Database Servers

The request URL is the same as, the one we used for creating the Server.

https://management.database.windows.net:8443/{SubscriptionID}/servers

The major difference is that we do not have a request body and the response is going to look different.

The expected response is in the following format:

<Servers xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <Server>
    <Name>ServerName</Name>
    <AdministratorLogin>AdminAccount</AdministratorLogin>
    <Location>North Central US | South Central US | North Europe | West Europe | East Asia | Southeast Asia</Location>
  </Server>
</Servers>

The code for this would be:

$subscriptionid = "xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx"
$thumbprint = Get-ChildItem Cert:\CurrentUser\My  | Where-Object {$_.Subject -LIKE "CN=sqlchow80it032"} | Select-Object Thumbprint
$certificate = Get-Item Cert:\CurrentUser\My\$($thumbprint.Thumbprint)
Invoke-RestMethod -Method Get -Uri "https://management.database.windows.net:8443/$subscriptionid/servers" -Certificate $certificate

VERBOSE: GET https://management.database.windows.net:8443/03xxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx/servers with 0-byte payload
Invoke-RestMethod : 
<Error xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
	  <Code>40643</Code>
	  <Message>The specified x-ms-version header value is invalid.</Message>
	  <Severity>16</Severity>
	  <State>1</State>
	</Error>

Oh! no. But, this was expected and you trudge back a ‘page-up’ you will see that the header was a mandatory parameter and had to have a specific value of 1.0 assigned to it. Let us behave and send the request in proper format.

$subscriptionid = "xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx"
$thumbprint = Get-ChildItem Cert:\CurrentUser\My  | Where-Object {$_.Subject -LIKE "CN=sqlchow80it032"} | Select-Object Thumbprint
$certificate = Get-Item Cert:\CurrentUser\My\$($thumbprint.Thumbprint)
$headers = @{"x-ms-version" = "1.0"}

Invoke-RestMethod -Uri "https://management.database.windows.net:8443/$subscriptionid/servers" -Certificate $certificate -Headers $headers -Method "GET"

VERBOSE: GET https://management.database.windows.net:8443/xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx/servers with 0-byte payload
VERBOSE: received 354-byte response of content type application/xml; charset=utf-8
?<Servers xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <Server>
    <Name>gobbl3go0k</Name>
    <AdministratorLogin>sqlchow</AdministratorLogin>
    <Location>West US</Location>
    <Features>
      <Feature>
        <Name>Premium Mode</Name>
        <Value>false</Value>
      </Feature>
    </Features>
  </Server>
</Servers> 

We got our response but, if you look closely the response has a ‘?’ on it. I did not find a reason why the response content has a ‘?’ (in this case it was 65279) in it. Reading through a few stackoverflow questions I thought adding the ‘@{“Accept”=”application/xml”}’ would solve it but, that results in an error:

Invoke-RestMethod : The 'Accept' header must be modified using the appropriate property or method.
Parameter name: name
At line:1 char:1
+ Invoke-RestMethod -Uri "https://management.database.windows.net:8443/$subscripti ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

So, as a temporary workaround; I put the response into a text file and then read from it. This time there is no ‘?’ and I get xml object.

Invoke-RestMethod -Uri "https://management.database.windows.net:8443/$subscriptionid/servers" -Certificate $certificate -Headers @{"x-ms-version"="1.0"} -OutFile "C:\temp\response.txt"
$data = [xml](get-Content C:\temp\response.txt)

$data.Servers.GetElementsByTagName('Server')
Name                          AdministratorLogin            Location                      Features
----                          ------------------            --------                      --------
gobbl3go0k                    sqlchow                       West US                       Features

$data.Servers.GetElementsByTagName('Server').features.feature
Name                                                        Value
----                                                        -----
Premium Mode                                                false

But, again we could put the response into a variable and used Remove() to get rid of the ‘?’, like we did for the create server part.

Note: if anyone knows why we have a ? in the response please let me know. I would really appreciate it.

After running the create scripts a couple of times, I ended up with three servers:

Invoke-RestMethod -Uri "https://management.database.windows.net:8443/$subscriptionid/servers" -Certificate $certificate -Headers @{"x-ms-version"="1.0"} -OutFile "C:\temp\response.txt"
$data = [xml](get-Content C:\temp\response.txt)
$data.Servers.Server

Name                          AdministratorLogin            Location                      Features
----                          ------------------            --------                      --------
gkbbwrq8ew                    sqlchow                       West US                       Features
hk5r8andyn                    sqlchow                       Southeast Asia                Features
vo0iyrxe4i                    sqlchow                       Southeast Asia                Features

The only other operation we can do with these database servers are delete and update. Both of them do not have response body :). Let us check these out.

Update administrator password

The URL for managing the password of a database server is a little different than the previous two we saw:

https://management.database.windows.net:8443//servers/?op=ResetPassword

The request body also differs a bit, as it only has the AdministratorLoginPassword node in it.

<?xml version="1.0" encoding="utf-8"?>
<AdministratorLoginPassword xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  TheNewPassword
</AdministratorLoginPassword>

The drill is pretty much the same, with the only difference being that we do not have a response body. If we do not get any errors then we are good to go.

$subscriptionid = "xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx"
$thumbprint = Get-ChildItem Cert:\CurrentUser\My  | Where-Object {$_.Subject -LIKE "CN=sqlchow80it032"} | Select-Object Thumbprint
$certificate = Get-Item Cert:\CurrentUser\My\$($thumbprint.Thumbprint)
$headers = @{"x-ms-version" = "1.0"}

$password = "v3ed!kip)wEr$hellP1cchi" #it just means that this guy is mad about powershell.

$servername = "vo0iyrxe4i"
$resturi = "https://management.database.windows.net:8443/$subscriptionid/servers/$servername" + "?op=ResetPassword"
$contenttype = "application/xml;charset=utf-8"
$xmlBody = @"
<?xml version="1.0" encoding="utf-8"?>
<AdministratorLoginPassword xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
    $password
</AdministratorLoginPassword>
"@
[byte[]]$bytebody = [System.Text.Encoding]::UTF8.GetBytes($xmlBody)
[xml]$delresponse = Invoke-RestMethod -Method POST -Uri $resturi -Certificate $certificate -Headers $headers -Body $bytebody -ContentType $contenttype -Verbose

VERBOSE: POST https://management.database.windows.net:8443/xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx/servers/vo0iyrxe4i?op=ResetPassword with 165-byte payload
VERBOSE: received 0-byte response of content type

That’s it snap! the password is changed. But, if we want a response code then we can use Invoke-WebRequest which gives a status code for each request. We shall check how, in a bit after we look at deleting the server.

Delete SQL Database Server

The url for deleting a server is very simple.

https://management.database.windows.net:8443/{SubscriptionID}/servers/{ServerName}

The code:

$servername = "hk5r8andyn"
$resturi = "https://management.database.windows.net:8443/$subscriptionid/servers/$servername"
$contenttype = "application/xml;charset=utf-8"

$deleteresponse = Invoke-RestMethod -Method DELETE -Uri $resturi -Certificate $certificate -Headers $headers -ContentType $contenttype -Verbose
VERBOSE: DELETE https://management.database.windows.net:8443/xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx/servers/hk5r8andyn with 0-byte payload
VERBOSE: received 0-byte response of content type

Remove-Item "C:\temp\response.txt"
Invoke-RestMethod -Uri "https://management.database.windows.net:8443/$subscriptionid/servers" -Certificate $certificate -Headers @{"x-ms-version"="1.0"} -OutFile "C:\temp\response.txt"
$data = [xml](get-Content C:\temp\response.txt)
$data.Servers.Server
Name                          AdministratorLogin            Location                      Features
----                          ------------------            --------                      --------
gkbbwrq8ew                    sqlchow                       West US                       Features
vo0iyrxe4i                    sqlchow                       Southeast Asia                Features

Using Invoke-WebRequest

Using Invoke-WebRequest is not much different than using Invoke-RestMethod. One important advantage of Invoke-WebRequest is that you get the status code and the raw content actually contains x-ms-request-id, which will come in handy in case there is an issue you have to contact ms support.

Invoke-WebRequest -Uri $resturi -Certificate $certificate -Headers @{"x-ms-version"="1.0"} -Method GET -Verbose
VERBOSE: GET https://management.database.windows.net:8443/xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx/servers with 0-byte payload
VERBOSE: received 636-byte response of content type application/xml; charset=utf-8

StatusCode        : 200
StatusDescription : OK
Content           : <Servers xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
                      <Server>
                        <Name>gkbbwrq8ew</Name>
                        <AdministratorLogin>sqlchow</AdministratorLogin>
                        <Location>West US</Location>
                        ...
RawContent        : HTTP/1.1 200 OK
                    x-ms-request-id: be318ebc-bf8a-4e6d-bfdd-e204dd01efff
                    Content-Length: 636
                    Content-Type: application/xml; charset=utf-8
                    Date: Sun, 01 Sep 2013 14:51:11 GMT
                    Server: Microsoft-HTTPAP...
Forms             : {}
Headers           : {[x-ms-request-id, be318ebc-bf8a-4e6d-bfdd-e204dd01efff], [Content-Length, 636], [Content-Type, application/xml; charset=utf-8], [Date, Sun, 01 Sep 2013 14:51:11 GMT]...}
Images            : {}
InputFields       : {}
Links             : {}
ParsedHtml        : mshtml.HTMLDocumentClass
RawContentLength  : 636

If you reached here, I appreciate your patience and time. I would like to let you know that I am working on a new post using the HttpClient to manage these services. It may take sometime before it will be ready.

Find all people with word #powershell in their headline (bio) with Google: allintitle:”on twitter” “powershell” site:twitter.com

Since, I had the PowerShell console open I put the query in there to see how the data from the web request looked like. As you would have guessed, not much going on there. A quick google search revealed that they have a custom search api which uses REST and returns the results as JSON. Bingo!

Before you run off to get you mandatory API key, there are a couple of things I need to point out about the restrictions, although your query can return 1000’s of results, you can only:

1. You can get only 10 results per query.
2. You can only make 100 requests per day.

Small consolation is that, you can still get the total number of search results returned.

Here is the quick and dirty code:

$gglquery = 'https://www.googleapis.com/customsearch/v1?key={MANDATORY-API-KEY}&cx=013036536707430787589:_pqjad5hr1a&q=allintitle%3A+PowerShell+site%3Atwitter.com&oq=all&alt=json'
$result = Invoke-RESTMethod -Uri $gglquery
$result.items | Select @{name='twitter_handle';expression={$($_.link.tostring()).replace('https://twitter.com/','@')}}

twitter_handle
--------------
@PowerShellMag
@PowerTip
@PhillyPoSH
@PoshCode
@PowerShellQuiz
@MTLPowershell
@cspshell
@flpsug
@AppVPowerShell
@SANSInstitute/status/361939924188008449 <--don't why this one got returned.

And the search information can be retrieved by doing:

$result.searchInformation | Select searchTime, formattedSearchTime, totalResults

searchTime formattedSearchTime totalResults
---------- ------------------- ------------
  0.067613 0.07                31

Cool stuff! Thanks ‘Peter Kriegel’ for posting the google query.

How to filter line + following line using Get-SqlErrorLog? Something like Select-String -Context 1

From the PowerShell help:

The Context parameter captures the specified number of lines before and after the line with the match. This allows you to view the match in context.

If you enter one number as the value of this parameter, that number determines the number of lines captured before and after the match. If you enter two numbers as the value, the first number determines the number of lines before the match and the second number determines the number of lines after the match.

I was not sure if Get-SqlErrorLog supported a parameter like Context. So, I decided to check if we can do something like this, without too much code.

Add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.ErrorLogPath

Output is something like so, on my machine:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

From here it is pretty straight forward:

Get-ChildItem -Path $server.ErrorLogPath -Filter ERRORLOG* |
ForEach-Object -Process { Select-String -Path $_ -Pattern "Starting up database 'sqlchow'" -Context 1}

The output is something like:

  C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG:54:2013-08-17 08:33:58.26 spid52
Using 'dbghelp.dll' version '4.0.5'
> C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG:55:2013-08-17 12:55:59.64 spid53
Starting up database 'sqlchow'.
  C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG:56:2013-08-17 12:56:01.78 spid53
Setting database option COMPATIBILITY_LEVEL to 100 for database sqlchow.

Note: If you want to search only in the first error-log file then, just remove change the code to:

Get-ChildItem -Path $server.ErrorLogPath -Filter ERRORLOG |
ForEach-Object -Process { Select-String -Path $_ -Pattern "Starting up database 'sqlchow'" -Context 1}

Interesting stuff eh!

I presented about visualizing data using Microsoft Chart Controls. I feel most administrators understand the benefits of automation but, fail to showcase these improvements in an effective manner. This is where MS Chart Controls step-in, as I believe that visual medium is the most effective way to drive a point; like they say, ‘A picture is worth a thousand words’.

I started off the presentation with a simple example, looked at the underlying components of the chart objects and then moved onto building charts from CSV files and XML files. We closed off the presentation with a small demo on SeeShell.

The slides for the presentation are embedded here but, can be picked up from slideshare as well. The scripts used for the demo can be found here.

After the monthly patching cycle completes, we used to spend a considerable amount of time figuring out if all the clustered SQL*Server instances were running on their preferred owners or not. During the latest cycle of patch deployment, I was hard pressed for time and wanted to see if I could put something together, quick and dirty, to display the information easily.

Basically, what I did was improve an old script that I had written back in Jan, 2012; which got me the preferred owner names of a given cluster group. I made some changes to that and started building around it. The script presented below is essentially a simple looped re-write of the original. The script works in the following way:

1. Connect to a cluster and get a list of nodes that belong to it.
2. For each node in the cluster check which groups are available on a given node.
3. For each cluster group on the node get the preferred node details and check if the current node is its preferred node.

Function Search-ClusterNodesForStacking
{
   <#
		.SYNOPSIS
		This script provides options for an administrator to check if any cluster
		services are stacked i.e. not running on their preferred nodes.

		.DESCRIPTION
			 The script works in the following way:
			 1. Connect to a cluster and get a list of nodes that belong to it.

			 2. For each node in the cluster check which services are available on a
				given node.

			 3. For each clustered service on the node, check if the current node is
				its preferred node.

		.PARAMETER ClusterName
			 Name of the Cluster which needs to be validated.

			 Required                         true
			 Position                         named
			 Default value
			 Accept pipeline input            false
			 Accept wildcard characters       false

		.EXAMPLE
			Search-ClusterNodesForStacking -ClusterName SKYNETCL
			------------------------------------------------------------
			ClusterName: SKYNETCL
			------------------------------------------------------------
					NodeName:  SKYNETA
					NodeState:  ONLINE
							ActiveGroup: Available Storage
							GroupState: OFFLINE
							Preferred Node:<-StackingDetected
							PhysicalNode:  SKYNETA
							ActiveGroupName:  Available Storage
							ActiveGroupDescription:
							PreferredNode:
							ActiveGroup: SKYSQL014
							GroupState: ONLINE
							Preferred Node: SKYNETA <- Good
							ActiveGroup: SKYSQL015
							GroupState: ONLINE
							Preferred Node:SKYNETB<-StackingDetected
							PhysicalNode:  SKYNETA
							ActiveGroupName:  SKYSQL015
							ActiveGroupDescription:
							PreferredNode: SKYNETB
			------------------------------------------------------------
					NodeName:  SKYNETB
					NodeState:  ONLINE
							ActiveGroup: Cluster Group
							GroupState: ONLINE
							Preferred Node:SKYNETA<-StackingDetected
							PhysicalNode:  SKYNETB
							ActiveGroupName:  Cluster Group
							ActiveGroupDescription:
							PreferredNode: SKYNETA
			------------------------------------------------------------
					NodeName:  SKYNETC
					NodeState:  ONLINE
							ActiveGroup: SKYSQL017
							GroupState: ONLINE
							Preferred Node: SKYNETC <- Good
							ActiveGroup: SKYSQL018
							GroupState: ONLINE
							Preferred Node: SKYNETC <- Good
							ActiveGroup: SKYSQL019
							GroupState: ONLINE
							Preferred Node: SKYNETC <- Good
							ActiveGroup: SKYSQL016
							GroupState: ONLINE
							Preferred Node: SKYNETC <- Good
			------------------------------------------------------------

		.NOTES
			 Since, the function shows the information on screen we will not accept
			 pipeline input.
   #>
	[CmdletBinding()]
	param(
		[Parameter(Mandatory = $true, HelpMessage="Enter cluster name which needs to be validated.")]
		[ValidateNotNullOrEmpty()]
		[string]$ClusterName
	)
	BEGIN
	{
		Function Get-ClusterState
		{
			param(
				[int]$state = $( Throw "Please specify cluster state.")
			)
			$getState = "";
			switch($state){
				-1 { $getState = "STATE_UNKNOWN"}
				0 { $getState = "ONLINE"}
				1 { $getState = "OFFLINE"}
				2 { $getState = "FAILED"}
				3 { $getState = "PARTIAL_ONLINE"}
				4 { $getState = "PENDING"}
				default { $getState = "ERROR GETTING STATE"}
			}
			$getState
		}
	}
	PROCESS
	{
		[string] $virtualClusName = "";
		[boolean] $bStacking, $bFound = ($False, $False);
		[string] $sPrefNode = "";

		#Get the cluster nodes and name.
		$Cluster = Get-WmiObject -namespace "Root\MSCluster" -Class "MSCluster_Cluster" -computerName $ClusterName -Authentication "PacketPrivacy" -Impersonation "Impersonate";
		$virtualClusName = $Cluster.Name.ToString();
		$clusterNodes = Get-WmiObject -namespace "Root\MSCluster" -query "Select * from MSCluster_Node" -computerName "$($virtualClusName)" -Authentication "PacketPrivacy" -Impersonation "Impersonate";
		$clusNodeNames = $clusterNodes | Select-Object Name, Description, @{Name="ClusState"; Expression={Get-ClusterState $_.State}}

		Write-Host ("-" * 60) -ForegroundColor Yellow -BackgroundColor Black
		Write-Host "ClusterName: $($virtualClusName)" -ForegroundColor Green
		Write-Host ("-" * 60) -ForegroundColor Yellow -BackgroundColor Black

		#Loop through the collection of physical cluster nodes
		$bStacking = $False
		Foreach ($node in $clusNodeNames)
		{

			Write-Host "`tNodeName: " $node.Name -ForegroundColor Yellow
			Write-Host "`tNodeState: " $node.ClusState -ForegroundColor Yellow

			#Get the Active Group for each node
			$qryStr = "ASSOCIATORS OF {MSCluster_Node='" + $node.Name + "'} WHERE AssocClass=MSCluster_NodeToActiveGroup"
			$clusNodeAssoc = Get-WmiObject -namespace "Root\MSCluster" -query $qryStr -computerName $virtualClusName -Authentication "PacketPrivacy" -Impersonation "Impersonate"
			$clusGroupNames = $clusNodeAssoc | Select-Object Name, Description, @{Name="ClusState"; Expression={Get-ClusterState $_.State}}

			#Loop through each Active group
			Foreach ($group in $clusGroupNames)
			{
				If($group.Name -ne $null)
				{
					If ( ($group.Description -ine "") -or ($group.Description -eq $null)){
						Write-Host "`t`tActiveGroup: $($group.Name)" -ForegroundColor Magenta
						Write-Host "`t`tDescription: $($group.Description)" -ForegroundColor Magenta
					} Else{
						Write-Host "`t`tActiveGroup: $($group.Name)" -ForegroundColor Magenta
					}
					Write-Host "`t`tGroupState: $($group.ClusState)" -ForegroundColor Magenta

					#Get the preferred node for this Active Group.
					#If there isn't one we just skip this because we can't detect stacking.
					$qryStr = "ASSOCIATORS OF {MSCluster_ResourceGroup='" + $group.Name + "'} WHERE AssocClass=MSCluster_ResourceGroupToPreferredNode"
					$clusGroupAssoc = Get-WmiObject -namespace "Root\MSCluster" -query $qryStr -computerName $virtualClusName -Authentication "PacketPrivacy" -Impersonation "Impersonate"
					$clusPrefNodes = $clusGroupAssoc | Select-Object Name, Description, @{Name="ClusState"; Expression={Get-ClusterState $_.State}} -First 1

					#Loop through this collection to determine if this Active Group is on a preferred node
					Foreach ($prefNode in $clusPrefNodes){
						if($prefNode -ne $null)
						{
							$sPrefNode = [string]$prefNode.Name
							If ([string]$prefNode.Name.ToUpper() -eq [string]$node.Name.ToUpper()){
								#$sPrefNode = [string]$prefNode.Name
								$result = "`t`tPreferred Node: " + $sPrefNode + " <- Good"
								Write-Host $result -ForegroundColor Green
								$bFound = $True
							}else{
								$bFound = $False
							}
						}
					}

					If ((-not $bFound) -and ($clusGroupNames.Count -ine 0)){
						$stckDet = "`t`tPreferred Node:" + $sPrefNode + "<-StackingDetected"
						Write-Host $stckDet -ForegroundColor Red
						$bStacking = $True
						Write-Host "`t`tPhysicalNode: " $node.Name  -ForegroundColor Red
						Write-Host "`t`tActiveGroupName: " $group.Name -ForegroundColor Red
						Write-Host "`t`tActiveGroupDescription: "  $group.Description -ForegroundColor Red
						Write-Host "`t`tPreferredNode:" $sPrefNode -ForegroundColor Red

					}
					$bFound = $False
				}else{
					Write-Host "`t`tActiveGroup: Unable to get group name" -ForegroundColor Cyan
				}
			}
			Write-Host ("-" * 60) -ForegroundColor Yellow -BackgroundColor Black
		}
	}
	END
	{
	}
}

As always, feedback and comments are welcome.

So if you have more than a few consoles open continuously you might consider lowering the refresh rate of the consoles.

The registry key that controls this behavior is: “HKCU\Software\Microsoft\Microsoft Operations Manager\3.0\console\CacheParameters\ PollingInterval”

The values it accepts are in the range 0 – 10, where 0 turns off automatic refreshes and requires manual refresh via F5. The values 1 through 10 increment the refresh interval by 15 seconds.

Setting this property in powershell is easy

#check current setting
Get-ItemProperty -LiteralPath 'HKCU:\Software\Microsoft\Microsoft Operations Manager\3.0\Console\CacheParameters' -Name PollingInterval

#change current setting
Set-ItemProperty -LiteralPath 'HKCU:\Software\Microsoft\Microsoft Operations Manager\3.0\Console\CacheParameters' -Name PollingInterval -Value 5