Feeds:
Posts
Comments

I had a request to take data from a CSV file that was being automatically downloaded and copy a section of the data into an existing spreadsheet. The spreadsheet had 3 tabs and the copied data had to go to the tab labeled “Raw Data”. Then one of the other tabs would perform calculations and analysis on that raw data. I know I could manually copy the data but this process is already partially automated and the number of rows and columns involved were huge. There are at least 60 columns and over 10,000 rows of data that needed to be copied. So, to Powershell I go!!

I created a Powershell Script that would open both files and then select the data from the csv file and copy it.  Then the script would go to the Excel file and to the required tab before pasting the info.

Here is the final script.

$pathxls = “\\fileserver\data\myshare\sheets\workbook.xls”
$pathcsv = “\\fileserver\data\myshare\sheets\Overview.csv”
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $excel.Workbooks.open($pathcsv)
$Workbook2 = $excel.Workbooks.open($pathxls)
$Worksheet = $Workbook.WorkSheets.item(“Overview”)
$Worksheet.activate()
$range = $WorkSheet.Range(“A2:BC2”).CurrentRegion
$range.Copy() | out-null
$Worksheet2 = $Workbook2.Worksheets.item(“RAW DATA”)
$worksheet2.activate()
$range2 = $Worksheet2.Range(“A3:A3”)
$Worksheet2.Paste($range2)
$workbook2.Save()
$workbook.close($false)
$Excel.Quit()
[gc]::collect()
[gc]::WaitForPendingFinalizers()

Now let’s dissect it a bit.

First we declare the variables for the two files $pathxls and $pathcsv and assign the path and file to each variable.

Then we declare a new com object which is an excel application.

We tell it that we don’t want Excel to be visible during the run of the script.

And we then set two workbook variables for the two files.

In the first file (the CSV) we tell it to go to the worksheet labled “Overview” and then make it the activate sheet.  Once there we select a range and copy it.

Then we jump over to the second worksheet and select the tab called “Raw Data” and make it the active sheet.

Once that is done we can paste in the data we’ve copied and then we can save the Excel workbook.  We close the csv file and tell it NOT to save since we don’t need to and then we can quit Excel.

Rather simple and to the point.  It gets the job done.  Now I can either run this manually or set it as a scheduled task.

 

 

October 2015 was declared National Cyber Security Awareness Month by President Obama as a way to educate both the private and and public sectors about the importance of Cybersecurity.

What is Cybersecurity?  In a nutshell Cybersecurity is a managing cyber risks; measures taken to protect a computer or computer system against unauthorized access or attack.

In today’s interconnected world the internet is a part of almost all aspects of your daily life, whether you realize it or not.  We all have Facebook, LinkedIn, Twitter, and Instagram accounts.  Almost all of us have online banking or some sort of personal information (PI) stored online.  Perhaps, prior to a visit to a new doctors office you had to fill out a medical history in an online web portal.

At home or at your place of work you may get phishing emails asking you to click on a link to update your bank account information or to confirm your Facebook password.  You could even get a phone call from ‘Microsoft’ telling you that your personal computer has been infected with a virus and that they need remote access to help you fix the problem.

All of these and the many other ways we interact online are subject to constant attack by unscrupulous ‘hackers’.

Here are some basic rules to follow to promote good online safety habits.

Always Keep a Clean PC

  1. Keep security software up to date:  Having the latest security software, web browser and operating systems are the best defense against viruses, malware and other online threats.
  2. Automate Software updates: Many software programs can automatically update themselves to defend against known risks.  Make sure you enable these automatic updates if the option is available.
  3. Protect all devices that connect to the Internet:  Computers are not the only devices that need protection. Smart phones, gaming systems, and other web-enabled devices also need protection from viruses and malware.  Make sure to regularly check with manufacturers to ensure that you have applied the latest patches and updates for each device.
  4. Plug and Scan:  USB drives, memory sticks, and other external devices can be infected by viruses and malware.  Use your security software to automatically scan these devices when plugged into your computer.

Protect your Personal Information

  1. Secure your accounts: Look for multi-factor authentication.  Many account providers now offer additional ways for you verify who you are before you conduct business on that site.  It could be a secondary image, or a security question or even a single use token code.  If it is available make sure you enable it for additional security.
  2. Make passwords long and strong: A longer password using a seemingly random combination of capital and lowercase letters with numbers and symbols is more secure and harder to crack.
  3. Don’t reuse passwords: Separate passwords for every online account helps to thwart cybercriminals.
  4. Keep your passwords safe: More complex passwords are easier to forget. If you must keep a list, store it in a safe, secure place away from your computer.
  5. Be responsible with your online presence:  You should always review your privacy and security settings for all online accounts.  Set the levels to a level you are comfortable with for sharing personal information.  It is far better to limit with whom you share information than to grant everyone access.

Connect Carefully

  1. When in doubt, throw it out: Links in email, tweets, posts, and online advertising are often the way cybercriminals compromise your computer. If it looks suspicious, even if you know the source, it’s best to delete or if appropriate, mark as junk email.
  2. Wi-Fi hotspots and you:  When you are connecting to Wi-Fi hotspots in public places you should always limit the type of online activities and business you conduct.  Make sure the security settings on your device limit the access to your machine.
  3. Protect your finances: When shopping or banking online, check to make sure that the site is secured with a valid SSL certificate.  Make sure the web address starts with with “https://” or “shttp://”, which means the site takes extra measures to encrypt your information. A web address that starts with “http://” is not secure.

Web Wisdom

  1. Back it up: Protect your valuable work, photos, music or other digital information by making sure you have a copy and storing it safely.  You could use the operating systems built in tools or third party applications to back up your computer or device and store it on another electronic medium.
  2. Think before you act:  Be wary of communications that implore you to “ACT NOW”, or offers something that sounds too good to be true.  Another red flag is anything that asks for personal information.
  3. Stay Current:  Always keep abreast of the latest security information to ensure that you know the newest ways to stay safe online.  Share this information among friends, family and colleagues.

The U.S. Department of Homeland Security has a dedicated section on their website for Cybersecurity (DHS Cybersecurity) with sections for all sorts of different areas of focus.

Another great place for resources and tips is the website StaySafeOnline.org they have sections on personal security, as well as business resources.

By using common sense and a little ‘healthy caution’ you should be able to stay safe while being online.

Out-printer cmdlet

Recently I was asked if you could use PowerShell to print to a specific printer.  I have written posts on how to print to your default printer but had never tried printing to a specific printer.  So I decided I should see if this is possible.

And what do you know there is a built in cmdlet called Out-Printer that accepts the -Name parameter.  Using this optional parameter allows you to specify an alternate printer.

OK so now that I’ve got that lets try it out.  I have a printer here in my office which is an HP 2600 color laserjet printer.  It is NOT my default printer so I would like to test printing to it using that cmdlet.

First lets just test the cmdlet without the optional parameter.

get-process | Out-Printer

Sure enough that prints the get-process output directly to my default printer.

Now lets try

get-process | Out-Printer -name \\Svr1\it-2600n

Sure enough this printed out to the HP 2600 color laser.  PERFECT!!!

Now there are a few problems with this cmdlet, the first being that if you specify the -name parameter and you are trying to print to a shared printer you need to use the UNC name of that printer.

The second problem is that this is a cmdlet that prints output.  It does not print the CONTENT of a file.  So for example if you wanted to print a txt document and you wrote Out-Printer “c:\test.txt” it would actually print out a page with c:\test.txt on it.

You could use the Get-Content cmdlet and pipe that into the Out-Printer cmdlet like this Get-Content “c:\test.txt” | Out-Printer

This would give you a printout of the actual contents of the test.txt file.  However, if you wanted to print a word document or a pdf document this would not work.  It will give you tons of garbage and not the actual file contents.

So you are back to the drawing board again.  To print a word or pdf document you need to use this command.

Start-Process -FilePath “C:\Test.Docx” -Verb Print

Which brings you back to the original issue that was made, how do I print to a printer that isn’t the default one?

And for the moment I have to say I don’t see a way.  I think you need to set the default printer first, then you can use the Start-Process cmdlet and print the document.

I was working on a project and needed to remotely check a servers available disk space so I started trying to come up with a simple reusable function to check the disk space.  The following is what I came up with. 

Function GetDiskSpace

{
<#
.SYNOPSIS
GetDiskSpace displays the Total Size and Total Free Space of a Remote Computer Drive
.DESCRIPTION
See synopsis
.EXAMPLE
To run – enter GetDiskSpace
#>
$computer = Read-host "Please enter the computer name"
$drive = Read-host "Please enter the drive letter including the colon (c:)"
$disk = Get-WmiObject Win32_LogicalDisk -ComputerName $computer -Filter "DeviceID=’$drive’" | Select-Object Size,FreeSpace
"Remote computer: $computer drive letter $drive has {0:#.0} GB free of {1:#.0} GB Total" -f ($disk.FreeSpace/1GB),($disk.Size/1GB) | write-output

}
Set-alias GDS GetDiskSpace

I recently got involved with a new website project and was trying to determine the best set up for our situation. I needed a Content Management System (CMS) so the end user could make the changes to the site without my intervention. So, I started looking into different solutions. My first attempt was DotNetNuke community edition. Since I am familiar with both MS SQL and ASP.NET I thought this would be the most logical choice. So, I set up a virtual machine running Windows Server 2008 R2 and set to work configuring DotNetNuke.

After a bit of wrangling I was able to get the site up and running with the DNN sample site running. It was pretty slick but there were a lot of confusing configuration settings. I was able to get a basic sample site running and let the end user mock up a few pages. It worked but it wasn’t great.

So, I remembered that I also have a BlogEngine.net site running on MSSQL and ASP.NET so I looked into that as well. Again not bad, but nothing I would say I absolutely loved.

Now, I am setting up another virtual machine and going through the steps of setting up a Windows installation of WordPress.

I found a great step by step tutorial from someone else that has helped me get up and running. http://www.vsysad.com/2012/04/create-a-wordpress-blog-on-windows-server-2008-r2-iis-7-5-and-mysql/

Now that the site is up and running I can choose a template and begin to customize the site.

So far, I have to say I am more comfortable with WordPress as a CMS and it seems easier to work with than the other systems I have tried. Of course the next big trick would be to create my own custom theme.

Merry Christmas

Merry Christmas to all! Hope it was a special day for everyone.

This past weekend I was rebuilding a storage array to increase the storage capacity and also reconfigure the drives to optimize the arrangement for better performance on our SQL Server.  The original arrangement was two Dell PowerEdge 1950 servers connected to a  Dell MD3000 Storage array.  This setup was using Windows Server 2003 Clustered for High Availability.  We had a single Raid 5 array in the MD3000 and on it were three partitions, one for the Quorum, One for Data and one for Log files.

Our new configuration was designed to increase the drive space and also split off the partitions onto separate raid arrays.  So, we created a RAID 10 for the Log Files, a RAID 5 for the Databases, and then another Raid 10 which contained the Quorum partition and also a partition for the Temp DB.

When we brought up the server after the reconfiguration we discovered that the Cluster Service would not start.  This was due to the fact that the Quorum drive had been moved and was no longer at the location that Cluster Service expected it.

After several minutes of searching I found this TechNet article http://technet.microsoft.com/en-us/library/cc738770(v=ws.10).aspx.  Basically, in order to start up the Cluster Service without the Quorum resource you need to edit the service and put in a switch.

Edit the ClusterService and add “/fixquorum” as a startup parameter and then start the service.  Once the service has started up with the /fixquorum option you can then run clusterrecovery.exe.  Clusterrecovery.exe will allow you to substitute a new disk for the Quorum disk.  Once you have done that and its replaced the quorum disk you will then be able to restart your ClusterService with the /fixquorum option and it should start up correctly.

Cheers!