Florida PowerShell Upcoming activities for May and June 2016

It’s a busy and a good time to learn some PowerShell.

1. Where: Florida PowerShell User Group Monthly meeting, Date: Thursday, May 26th at 6:30pm. Online
Topic: The Essential PowerShell on Error Trapping.
Description:
Do you to learn how to trap and document error while running PowerShell scripts? This session will cover the use and how to trap errors in your PowerShell script. We’ll be creating simple script providing some scenarios in trapping errors. At the same time, we are going to end up creating an error report.

Register at Eventbrite: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-may-2016-tickets-25454080841

2. Where: Idera Geek Synch, Date: Wednesday, June 1st at 12:00pm – 01:00pm, Online
Topic: The Essential PowerShell Tools for the DBA Administrator.
Description: I will covers the some of my favorite PowerShell tools I uses on a regular basis. In this session I will be showing some available tools the DBA can use along with PowerShell. I’ll be integrating Visual Studio with PowerShell and at the same time using IDERA’s PowerShellPlus editor. At the end, we’ll build an SSIS package solution to execute our PowerShell script. It will be pack with interesting thing to do as well as help you accomplish your automation tasks.

Register at: https://www.idera.com/events/geeksync

3. Where: IT Pro Camp Jacksonville. Date: Saturday, June 11th All Day Event. (In Person)
Topic: The Essentials of Tackling PowerShell Basic Functions
Description:  I will demonstrate creating a PowerShell function from a one-liner and/or an existing script file in its basic form. This is an example of the script evolution which you’ll experience while building you PowerShell skills.

Register at: http://itprocamp.com/

4. Where: SQLSaturday South Florida. Date: Saturday, June 18th, All Day Event. (In Person)
Topic: SSIS – Integrating PowerShell in a ScriptTask component
Description: This session will demostrate how you can reuse a PowerShell script in SSIS “Script Task” component as part on a ETL flow.  I’ll be showing some basic .NET Script code in both C# and VB.  I’ll be adding some useful tips when re-using existing Powershell code. Integrating different .NET technologies in a SSIS package: C#, VB.NET, XML, and PowerShell.

Register at: http://www.sqlsaturday.com/524/EventHome.aspx

Come and say Hi!

Create PowerShell Top-Down ETL Process – II

Series 2 – Building your data object

In the first part of the series we mapped to a server folder, and merge all needed log text files into a CSV type.  Now, we need to analize the data we have collected so we can determine the correct course of action.  This way we can work in creating our script solution to load this information in a SQL Server table.

Before analyzing our data we are going to create our data by creating a PowerShell object using the “Import-Csv” cmdlet. Keep in mind, if your CSV file is on a network drive then you’ll need to point to the folder.  For most cases you’ll start creating CSV file on your local drive.

Creating your CSV data object is easy. Follow the sample:

$logdata = `
(Import-Csv -Delimiter "`t" -literalPath $logfile -Encoding ASCII -Header $header) `
| Where-object{ $_.Column1 -gt "$($sqlresult.Tables.LastWriteTime)" };

The above sample gives you a look on what’s possible to do with such a command line in the script.  Basically, at the same time while  importing the data from a CSV file, I’m also querying it to look at a particular column containing a date field.  The other global variable  comes a result set from another query done against a SQL table.  The end result is to only create the $logdata with the dates we want to load into a SQL table.

PowerShell Object Column headers

If you notice, in the above cmdlet the where-clause I’m selecting to use the Column1 property instead of a reasonable label. In my scenario the data in the CSV file contain variable columns fopr its different data types such as: Info, Error, and System. So, it was easy to identify the total number of columns to be 15 columns.

Now, using the cmdlet “Import-Csv” using the parameter “-Header”, you can define a list columns when you build the $Logdata object. We create the $header variable with the column-names separated by comma.

$header= "Column1", "Column2", "Column3", ..."Column15";

Then, we can incorporate this $header variable in the *”Import-Csv” cmdlet in the following way:
*Note: Keep in mind, you must define the delimiter use in the CSV file.

#logdata = (Import-Csv -Delimiter "`t" -literalPath $logfile -Encoding ASCII -Header $header)

Next step after building the $logdata object is do the analysis on what you’re going to import into your SQL Server table. To start exploring any PowerShell object use the Get-Member cmdlet in combination with the Out-Gridview cmdlet. This combination will be use all the time while you develop and test you scripts.

$logdata | Get-Member | Out-Gridview;

SampleBuildData01

SampleBuildData01a

Displaying the $logdata object but only the first five data object:

$logdata |  | Select-Object -First 5;

SampleBuildData03
Analyzing the data

This is the part where you’re going to spend some time.  In figuring out what possible issues you’ll find to import this data into your SQL table.  Things like having both a single and double quotes will break your SQL Insert statements. So, look carefully and take the right action to transform this piece of the data.

SampleBuildData04

The following sample code snippet have help in converting to a proper format before using the SQL Insert statement.

$SQLdata = $null;
$SQLdata = foreach ($l in $global:logdata)
{
## - Sample snippet for replacing Single or Double-quotes before SQL Inserts:
if ($l.Column12 -ne 'Admin')
{
$l.Column4 = $l.Column4.replace("''", "''''").replace("' ", '" ').Replace(" '", ' "').Replace("'", '"');
if ($l.Column3 -eq 'START')
{
#$col12 = $l.Column12.replace("('", "(''").replace("','", "'',''").Replace("')", "'')");
if ($l.Column12 -ne $null)
{
$l.Column12 = $l.Column12.replace("'", "''");
}
else
{
if (($l.Column12 -ne $null) -or ($l.Column12 -ne 'Admin'))
{
if ($l.Column3 -eq 'FAILED')
{
$l.Column12 = $l.Column12.replace("' ", '" ').Replace(" '", ' "');
}
else
{
$l.Column12 = $l.Column12.replace("''", "''''");
}
};
};
};

## - To output with reformated data:
$i;

}
};

Here’s the changed data object information in Column12 only if Column3 has a “Failed” value. I will cover more in the next blog I’ll where I show how to construct the SQL Insert statement.

SampleBuildData05

What’s Next?

Next blog I’ll be covering importing the $logdata to a SQL Server table reaching its final destination.

 

Create PowerShell Top-Down ETL Process – I

Series 1 – Collecting files

These last few months I’ve been working in create a ETL process in PowerShell to replace a DOS batch process that collect a Report Server third-party application log data. During my analysis of how this DOS process works I discover this process was losing data, The log records in contains different type of records making the number of columns varying between 12 to 15. At the same time losing data due to using the SQL Server BCP utility to bulk copy data in a SQL Server data.

Here is were the magic of PowerShell brings to the table. This is our chance to be creative.

Collecting Log Data

In its basic form, we need to connect to the report server and access their log folder. We do this by creating a network map to the drive which in must cases could be a shared folder.

New-PSDrive -name T -PSProvider FileSystem -Root $LogFolder -Persist;

You can use the Cmdlet “New-PSDrive” to define your drive letter mapped to your log folder. Yoiu need to define its parameter -PSProvider as “FileSystem” and you could make it to Persist during your PowerShell session.

Keep in mind, as you possibly going for more than one server, you will top remove and map the drive again. This is as simple as using the following Cndlet:

Remove-PSDrive T;

We created drive letter T: and so now we removed it from you session.
Looking for Log files

So after the drive gets mapped to T: drive, we need to look and collect the type of logs we want to pull. In my scenario, I’m looking for all log labeled “*.Events.*.log.*”. One caveat discovered previously, these text logs file doesn’t contains servername information. But, No Problem! This is another opportunity to be creative with PowerShell.

Here we use the Cmdlet “Get-ChildItem” with the “Sort-Object” to sort the results by its object property “LastWriteTime“. You will file this property very useful later as you progress in our data collection process. This results set wil need to be stored in PowerShell Object

$flogs = (Get-ChildItem "T:\*_events.*") | Sort-Object LastWriteTime -Descending;

Building your CSV files

Next step will be to collect all logs from the server by merging all files into one text CSV file. For this step we use the Cmdlet “ForEach” in its script block format and also the Cmdlet “Get-Content” used to append to a CSV final output file using the Cmdlet “Out-File” with the “-Append;” parameter.

foreach ($f in $flogs)
{
Get-Content $f.fullname | Out-file -FilePath "<strong><span style="color: #333399;">T:\$($s)_All.csv</span></strong>" -Append;
};

Notice that I got a PowerShell variable defined to hold the server name. *hint*

Now, here’s a comparison between sample DOS batch file previously use vs. the PowerShell basic script.

DOS batch sample

dosbATCH

PowerShell basic code sample

POWERsHELL

Keep in mind, that you’re building a puzzle. There’s a lot more logic to be added to this code.
Be creative!
What’s Next?

Next blog I’ll be covering reading, and importing the CSV file to a PowerShell object before reaching its final destination on a SQL server..

PowerShell and BASH setup walkthru

Yes! I finally got the latest Windows 10 Build 14328 which includes BASH Ubuntu Linux subsystem.  I took me few days since my organization did a Windows 7 upgrade to Windows 10 Version 1511. Then, thru Windows Insider, getting the latest version with Bash.

WindowsBash_00

If it worth knowing that in order to get Bash, it’s a feature you need to installed it first. The following is the series of steps I use to enabled and install Bash on my desktop. And, after enabling Bash, I started using it under the PowerShell Console.

Set bash Feature On

Go to Control Panel, under Programs click “Uninstall a program” then click on “Turn Windows features on or off“.  Under the “Windows Features” popup windows Look and Enable “Windows Subsystem for Linux (beta)”. Click OK to accept changes.

WindowsBash_01

Using PowerShell Console and Set Developer Mode

Open PowerShell Console but make sure you already have set the “Execution Policy” to at least “RemoteSigned“.  Type and execute “bash” but it won’t work because you need to have your Windows 10 set to Developer Mode.

WindowsBash_02

The following screen show how to go thru the “Setting” panel to enable Windows Developer Mode.

WindowsBash_03 WindowsBash_04 WindowsBash_05 WindowsBash_06

Ready for Bash

At the PowerShell prompt type “bash” and press enter. This will ask to enter “y” to initiate the installation of Ubuntu on Windows.

WindowsBash_07 WindowsBash_09

After the installation is completed the next step is to create a User-ID and Password.

WindowsBash_10

Now, you can start using Linux within PowerShell.

WindowsBash_11

To get back to PowerShell just type “exit“. How cool is that!!

WindowsBash_12

 

PowerShell at the Orlando Code Camp 2016

Orlandocc

Orlando Code Camp 2016, Saturday April 2..

I had the pleasure to once again be a speaker at this activity since (I think) I started speaking in 2008. I have to say this is one of my favorite event I love to attend.

20160402_085402

I appreciate all 22 attendees that came to my session “The Essentials of Tackling PowerShell Basic Functions”.  Here’s where I show everyone how they can evolve their scripting skills. Showing how from a single cmdlet evolves to script and the a possible function.  But, still so much to learn about PowerShell and leting them know the “Get-Help” cmdlet is a good start.

20160402_100228

At the same time, I showcase Sapien Technologies “PowerShell Studio 2016” and gave them a taste of a useful Windows application built in PowerShell.

PowershellStudio

PowershellStudio2

Here’s the link to both the presentation and sample scripts:
https://onedrive.live.com/redir?resid=545C43857DEBA04A!588&authkey=!AKtBq5rmFCvsbgg&ithint=file%2czip

And, we got a winner in the raffle at the end of my presentation.

20160402_105606

Please check out my “The Essentials” series at Idera’s Geek Sync: (make sure to select “Webcast”)
https://www.idera.com/resourcecentral/resourceresults?rt=webcast&s=sm_twitter_spwc

 

Windows Management Framework 5.0 RTM is HERE!

Finally! The Windows Management Framework version 5.0 RTM is available for download for all down level Operating systems: Windows 7, Windows 8.1, Windows Servers 2008 R2, Windows Server 2012, and Windows Servers 2012 R2.

WMF50_01_12-18-2015 10-42-32 PM

Download link here: https://www.microsoft.com/en-us/download/details.aspx?id=50395

WMF50_02_12-18-2015 10-42-32 PM

You’ll notice that there are no download files for Windows 7 SP1 x64 and Windows 8.1 x64.  In order the install either of these you can use the following:

1. For Windows 7 SP1 x64 – Download and install: W2K8R2-KB3094176-x64.msu

2. For Windows 8.1 x64 – Download and install: W2K12-KB3094175-x64.msu

Check out the list of new and updated features:  (extracted from the “Detail” section)

  • Develop with classes in Windows PowerShell
  • Just Enough Administration (JEA)
  • Extract and parse structured object out of string content
  • More control in Windows PowerShell Remote Debugging
  • PowerShell Information Stream
  • New and updated cmdlets based on community feedback
  • Generate Windows PowerShell cmdlets based on an OData endpoint with ODataUtils
  • Manage .ZIP archives through new cmdlets
  • Interact with symbolic links using improved Item cmdlets
  • DSC authoring improvements in Windows PowerShell ISE
  • 32-bit support for the configuration keyword in DSC
  • Audit Windows PowerShell usage by transcription and logging
  • Configure DSC’s Local Configuration Manager with the meta-configuration attribute
  • Configure piece by piece with partial configurations in DSC
  • Manage with cross-computer dependencies in DSC
  • More control over configurations in DSC
  • Find more detail about configuration status in DSC
  • Support for -? during DSC configuration compilation
  • Support for DSC RunAsCredential
  • Rich information for DSC LCM State
  • Side-by-Side installation of DSC Resources and PowerShell Modules
  • PSDesiredStateConfiguration Module version updated to 1.1
  • Report configuration status from DSC to a central location
  • Discover and install software with PackageManagement
  • Discover PowerShell Modules, PowerShell Scripts and DSC resources with PowerShellGet
  • Network Switch management with Windows PowerShell
  • Software Inventory Logging (SIL)

Go ahead, download, and start using PowerShell version 5.0.

WMF50_03_12-18-2015 10-42-32 PM

SAPIEN PowerShell Studio 2015 is PowerShell v5.0 ready: https://www.sapien.com/software/powershell_studio

SAPIEN_WMF50_02_12-18-2015 10-42-32 PM

FLPSUG – Max Trinidad on “PowerShell Deep Dive to Excel” video

Back in October 2014, I presented and recorded on my topic about working with Excel and PowerShell. I thought this video was lost but I founded it on a backup drive.  Well its loaded with good samples using my favorite tool: SAPIEN’s PowerShell Studio. These tools are an essential for building .NET scripting and applications with PowerShell.

Check out the video at:

FLPSUG – Tim Warner on “Mastering Regular Expression in Windows PowerShell” video

The week before Thanksgiving we got Mr. Tim Warner presenting for our FLPSUG group on the topic of working with “Regular Expressions” in PowerShell.  It was a GREAT presentation loaded with information.

We recorded the video, and he provided his contact information and a link for this demo scripts:
1. Contact Tim Warner at:  timothywarner316@gmail.com
2. Demo scripts: http://timwarnertech.com/flpsug.zip

Check out the video at:

Azure PowerShell Preview 1.0 is here

Welcome to PowerShell Azure Resource Manager (RM)!

Microsoft has introduce and made available Azure PowerShell Preview 1.0.  Please read all about it in the following Azure Blog site:

Also, feel free to search for it at usign the following link:

Some Interesting links 

Well, within the search there are more interesting blog articles to start doing some PowerShell scripting:

How to install and configure Azure PowerShell: https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

Using Azure PowerShell with Azure Resource Manager: https://azure.microsoft.com/en-us/documentation/articles/powershell-azure-resource-manager/

Manage Azure SQL Database with PowerShell: https://azure.microsoft.com/en-us/documentation/articles/sql-database-command-line-tools/

Integrating SQL AlwaysOn with Azure Site Recovery: https://azure.microsoft.com/en-gb/blog/integrating-sql-alwayson-with-azure-site-recovery/

Get it at PowerShell Gallery

Now, my question here is, did you found Azure PowerShell 1.0 Preview? Probably not, but in fact, it’s AzureRM 1.0.1 to be installed. Although, I did found Azure version 0.9.11 and, Yes! I did download that one just in case.

To download these new bits from the PowerShell Gallery, check out the “Get Started with the PowerShell Gallery” link: http://www.powershellgallery.com/pages/GettingStarted

AzureRM_01_10-16-2015

Search for both “Azure” and AzureRM”.

AzureRM_02_10-16-2015

AzureRM_03_10-16-2015

During my quest to download Azure PowerShell 1.0, I realized that its AzureRM the one I need to download using: “Install-Module AzureRM” followed by “Install-AzureRM -force“.

AzureRM_04_10-16-2015

To start using the AzureRM Cmdlets, just run the “Import-AzureRM” command.

AzureRM_07_10-16-2015

Now, we are ready to play with Azure Resource Manager and Azure SQL databases.   There’s a total of 653 *AzureRM* cmdlets.

AzureRM_08_10-16-2015

AzureRM_09_10-16-2015

Run the following command to list all *AzureRM* commands:

Import-AzureRM; Get-Command *AzureRM*;

 

In my next blog article I’ll be converting my classic Azure SQL script to use the Azure RM new paradigm.