Speaking at Omaha PowerShell User Group on PowerShell Runspaces

For those of you around the Omaha/Lincoln area, you can catch me tomorrow night speaking at the Omaha PowerShell User group where I will be talking about PowerShell runspaces and how you can use them for multithreading with your commands as well as demoing my module, PoshRSJob. There is still time to sign up if you want to hear it! Unfortunately, this will not be recorded as I will probably be walking around and that doesn’t bode too well for audio recordings.

Sign Up Here: https://www.eventbrite.com/e/july-omaha-powershell-user-group-tickets-26412769304

Posted in powershell | Tagged , , , , | Leave a comment

Scraping the Web for Water Levels using PowerShell

I like to take my bike out to ride it on trails whenever I get the time. It’s fun to see how far I can go and a lot of times the scenery is always great. The problem is when it rains, the trail that I typically use sits next to a creek that can and will flood over if we get too much rain. Fortunately for me, there is a web site that the United States Geological Survey uses to monitor and report on the water levels of streams, rivers, etc… that I can bring up to see. The default display is a nice graph that shows the history for the last couple of days of the water level so you can see where it is at and also where it is trending toward now. The link that I use for my travels is this one: http://waterdata.usgs.gov/nwis/uv?site_no=06610795

image

Now, graphs are great and all, but I want to see something with actual data because to me,that is more interesting to see because I can then look at using PowerShell to scrape it from the web. Luckily, there are other options to display the data that happens to reside near the top of the web page for me that includes a table display with the raw data to look at.

image

 

image

This is just a quick snapshot of some of the data that is available to me. I can see the exact height of the water as well as its discharge rate. Now that I know where this resides, I can grab the link and see what I can do to use it.

image

The key pieces of data here are the code for the location that I want to pull data from which is available a couple of different ways (1)From the site that I pull the data from or (2) from the following link which provides you a map that you can then locate the nearest data point and find the code: http://maps.waterdata.usgs.gov/mapper/index.html.

image

My warning here is that the way the data is collected and displayed does not follow a consistent approach. So my way of parsing the data here will be hit or miss depending on the location that you are looking at. My recommendation is that you look at the table data and see if it matches up with what is available here…if it even has this type of data (I did find some areas that just flat out didn’t have anything useful).

I have a little function that works in the instance that I want to use it for in my area, so your use may vary. The first thing that I am going to do is set up my RegEx so it will grab all of the necessary data that I need. RegEx is pretty important in the world of web scraping because it can be difficult to pull the data unless you have some other tool to do so.

[OutputType('Web.USGS.Data')]
[cmdletbinding()]
Param (
    [string]$Location = '06610795', #Ft Crook Rd in Bellevue, NE
    [datetime]$StartDate = (Get-Date).AddDays(-1),
    [datetime]$EndDate = (Get-Date)
)

If ($PSBoundParameters.ContainsKey('Debug')) {
    $DebugPreference = 'Continue'
}
If ($StartDate.Date -eq (Get-Date).Date) {
    $StartDate = $StartDate.AddDays(-1)
}

$__StartDate = $StartDate.ToString('yyyy-MM-dd')
$__EndDate = $EndDate.ToString('yyyy-MM-dd')
$URI = "http://waterdata.usgs.gov/ne/nwis/uv?cb_00065=on&cb_00060=on&format=html&site_no=$($Location)&period=&begin_date=$($__StartDate)&end_date=$($__EndDate)"
$RegEx = [regex]"^(?<DateTime>(?:\d{2}/){2}\d{4}\s\d{2}:\d{2})\s(?<TimeZone>[a-zA-Z]{1,})(?<Height>\d{1,}\.\d{2})(?:A|P)\s{2}(?<Discharge>(?:\d{1,},)*\d{1,})(?:A|P)"

 

As you can see, I am looking for the DateTime, Height and Discharge from the web page. Note that I fill in the blanks on the web page url based on the location and the starting date and time. If I do not use a starting or ending datetime, I just use default values so that way the query will not throw errors. Once that is done, we get to send out the query using Invoke-WebRequest.

 

Try {
    $Data = Invoke-WebRequest -Uri $URI
}
Catch {
    Write-Warning $_
    BREAK
}

If ($Data.ParsedHtml.body.innertext -match 'Redirecting') {
    Write-Verbose "Requesting data older or longer than 120 days, performing redirection"
    $Data = Invoke-WebRequest -Uri $Data.links.href
}

 

The query will go out and bring back all of the web page data. Sometimes, depending on what I am sending it, it may have to redirect me to a different site to get the data. I want to be sure to recognize it and handle that change so I get the data that I need by looking at the ParsedHtml.Body.InnerText for the word ‘Redirecting’ and then look at the link provided.

From there, we will take the data and begin parsing each line to get what we needed.

$Title = ((@($Data.ParsedHtml.getElementsByTagName('Title'))[0].Text -replace '.*USGS(.*)','$1').Trim() -replace ',|\.') -replace ' ','-'
Write-Verbose "[$($Title)]"
@($Data.ParsedHtml.getElementsByTagName('Table'))[3].InnerText -split '\r\n' | ForEach {
    If ($_ -match $RegEx) {
        $Object = [pscustomobject]@{
            Location = $Title
            DateTime = [datetime]$Matches.DateTime
            Height_FT = [decimal]$Matches.Height
            Discharge = [decimal]$Matches.Discharge -replace ','
        }
        $Object.pstypenames.insert(0,'Web.USGS.Data')
        $Object
    }
    Else {
        Write-Debug "[$($_)] No match found!"
    }
}

The end result looks like this:

image

 

We now have our data in object form about the current water levels on a stream, in this case the stream where I happen to ride my bike by most days.

The function that I wrote to help me easily view this is available at https://github.com/proxb/PowerShell_Scripts/blob/master/Get-USGSWaterData.ps1

Using the map provided, you will get limited success in pulling water data depending on the data source that you use. Of course, you can update the code to make it work with other areas.

Posted in powershell | Tagged , , | Leave a comment

Quick Hits: Writing to a Read-Only Property

This post is less on doing something useful and more on just proving that I can do something even though PowerShell is trying to tell me that I cannot do it. That little thing is updating a property on an object that is set as Read-Only.

My example to show you how you can do something like this is by looking at $PSVersionTable which as we all know gives us some great information about what version of PowerShell that we are using.

$PSVersionTable

image

I am also using this as this was an example used in our previous user group meeting in which we were shown that the properties here could not be updated.

From this object, I am going to look more at the PSVersion property which of course is an object within this object.

$PSVersionTable.PSVersion

image

Looking at the properties of this object, we can see that each of the properties are shown as Read-Only by only having the Get method available to use.

$PSVersionTable.PSVersion | Get-Member

image

Furthermore, I am greeted with a nice error stating that my attempts to modify this have been foolish and that I should just give up.

$PSVersionTable.PSVersion.Major = 10

image

Yea, I guess I could just throw in the towel and move onto something more useful like updating some bugs on a module, but I think that I can do better here! And by using some reflection magic, I can make this happen!

To do this, I need to look at the hidden fields for the PSVersion object.

$PSVersionTable.PSVersion.GetType().GetFields('static,nonpublic,instance').Name

image

Here we can see that the field that we need to look at updating is _Major.  Now we need to figure out how we can set this.

$Field = $PSVersionTable.PSVersion.GetType().GetField('_Major','static,nonpublic,instance')
$Field | Get-Member

SNAGHTML32a4c8

Looking at the methods, I see a SetValue that accepts a object and a value for the object. This seems pretty simple,right? Just call the method and update the value.

$Field.SetValue($PSVersionTable.PSVersion,10)
$PSVersionTable.PSVersion

SNAGHTML59e81d

Perfect! We have now updated this “Read-Only” property to give us PowerShell V10! Now this will go away once we close and re-open PowerShell but it still a fun thing to do to show off how you can update these kinds of values.

Posted in powershell | Tagged , , , | 3 Comments

Updated Function: Get-SQLInstance

One function of mine that I use quite a bit to get information about SQL servers is Get-SQLInstance not only because it provides most of the information that I am looking for but it also is a great way to scan for all of the SQL servers on my network when combined with an Active Directory query to grab all of the servers on the domain.

This was a completely registry-centric tool that used different parts of the registry to gather a decent amount of information without having to actually connect to the SQL server itself and running some TSQL commands to learn more about the system. Now by looking at WMI, I can grab even more information while still not worrying about making a connection and running TSQL.

WMI, as you may or may not know, is an excellent repository of data that exists in Windows and lets you query for just about anything as long as you know where to look. Of course, using PowerShell means that exploring WMI is as simple as running some Get-WMIObject commands using either –List or (if you already know the class) connecting directly to the class using –Class and seeing what information is sitting there waiting to be discovered!

Some of the information that I need requires me to do a little digging around. I have to look at the Root\Microsoft\SQLServer namespace and then taking that information to then look at and then after I find what I need, I can then dig a little deeper into the namespace to unearth more information about the SQL server.

$WMIParams=@{
    Computername = $Computer
    NameSpace='root\Microsoft\SqlServer'
    Query="SELECT name FROM __NAMESPACE WHERE name LIKE 'ComputerManagement%'"
    Authentication = 'PacketPrivacy'
    ErrorAction = 'Stop'
}

I already know what properties I am looking for, so I am going to build a hash table with empty values that I can then use later on for adding items to it. This way, if there are no values added, it will still have the property names.

$PropertyHash = [ordered]@{
    Computername = $Computer
    Instance = $Null
    SqlServer = $Null
    WmiNamespace = $Null
    SQLSTATES = $Null
    VERSION = $Null
    SPLEVEL = $Null
    CLUSTERED = $Null
    INSTALLPATH = $Null
    DATAPATH = $Null
    LANGUAGE = $Null
    FILEVERSION = $Null
    VSNAME = $Null
    REGROOT = $Null
    SKU = $Null
    SKUNAME = $Null
    INSTANCEID = $Null
    STARTUPPARAMETERS = $Null
    ERRORREPORTING = $Null
    DUMPDIR = $Null
    SQMREPORTING = $Null
    ISWOW64 = $Null
    BackupDirectory = $Null
    AlwaysOnName = $Null
}

Now we can start out with our query to first find out the namespace that I want to connect to and then to start looking at the SQL properties using a different filter. In this case SqlServiceType = 1 means that I am only looking for the  SQL Server service (more info on that here).

$Namespace = (Get-WMIObject @WMIParams).Name
$Filter = "SELECT * FROM SqlServiceAdvancedProperty WHERE SqlServiceType=1" 
$PropertyHash['WMINamespace'] = $Namespace
$WMIParams.NameSpace="root\Microsoft\SqlServer\$Namespace"
$WMIParams.Query=$Filter
$WMIResults = Get-WMIObject @WMIParams

The results of this query provides us with a nice amount of information in both a string and number format.

image

From here it is a matter of adding each of these to their respected name in the hash table and then displaying the results at the end.

$WMIResults | ForEach {
    $Name = "{0}{1}" -f ($_.PropertyName.SubString(0,1),$_.PropertyName.SubString(1).ToLower())    
    $Data = If ($_.PropertyStrValue) {
        $_.PropertyStrValue
    }
    Else {
        If ($Name -match 'Clustered|ErrorReporting|SqmReporting|IsWow64') {
            [bool]$_.PropertyNumValue
        }
        Else {
            $_.PropertyNumValue
        }        
    }
    $PropertyHash[$Name] = $Data
}

The end result is some nice additions to my function that you can see below.

Get-SQLInstance –Computername vSCOM

image

Be sure to download the updated function below and if you want to help make this better, be sure to fork my repo and submit a pull request!

Download the updated Get-SQLInstance

https://gallery.technet.microsoft.com/scriptcenter/Get-SQLInstance-9a3245a0

Help contribute to Get-SQLInstance

https://github.com/proxb/PowerShell_Scripts

Posted in powershell | Tagged , , , | 2 Comments

A Look at a RegExHelper Tool for PowerShell Regular Expressions

I don’t know about you, but writing a regular expression can sometimes be a lesson in patience when putting a pattern together to parse a log file or provide some sort of validation of input. Each time I need to write a RegEx pattern, I tend to find myself taking a string that I want to use as an example and then going to another ISE tab and begin working out the details of the pattern to ensure that it works like I want it to work. Given, this isn’t really that bad to do, but I wanted a way to show the results as I am working on the pattern so I can knock out what I am looking to do.

With that, I decided to write a UI using WPF (using XAML as the front end) that that would meet my requirements of having a place to input the string that I wanted to write a RegEx for and then another text box that would be used for my RegEx pattern. The next part is the DataGrid that would display the matches as I begin writing the pattern. Lastly, because I wanted to provide all of the options that are available when using RegEx for .Net, there is a pane at the very bottom that gives you all of the options like what you see below:

[System.Text.RegularExpressions.RegexOptions].GetEnumNames()

image

More information about these can be found at the following link: https://msdn.microsoft.com/en-us/library/yd1hzczs(v=vs.110).aspx

My main project for the RegEx Helper is here: https://github.com/proxb/RegExHelper. This UI only has a single tab which is used for checking a string to find a match.

This does run as a function, so you will need to dot source the script file to load the function into the console.

. .\Invoke-RegExHelper.ps1

Invoke-RegExHelper

RegExHelper

I do have a Dev branch that has a version of this project that includes another tab that allows you to put a log file in (or part of a log) so you can write a pattern that can parse out a specific item or items and will highlight the results when you click on the Hightlight button. I haven’t made this part of the Master branch as I want to solve the performance issues when the size of the log is large and the number of matches being highlighted are fairly large as well. This is where I hope to have some community participation to help iron out this issue to really make this a great tool. The link to that branch is here: https://github.com/proxb/RegExHelper/tree/Dev

image

Anyways, I hope that you find this useful and if you want to help contribute to it, then you know what to do! Fork the repo and do a Pull Request of course! Smile

I plan to write a follow up post on just how I built this and my thought process on the design, but today I just wanted to get it out in the open and start gathering feedback on this tool.

Posted in powershell | Tagged , , , | 1 Comment