TechnoGist

“Let the passengers off the train FIRST!” (Pause.) “Oh go on then,
stuff yourselves in like sardines, see if I care — I’m going home….”

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

I was sent this and it made my day.

Original was here.

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

At the height of the gale, the harbourmaster radioed a coastguard on the spot and asked him to estimate the wind speed. He replied that he was sorry, but he didn’t have a gauge. However, if it was any help, the wind had just blown his Land Rover off the cliff.

(Aberdeen Evening Express)

Today I was looking at producing a report from a SQL2005 database and exporting the SQL data into a CSV file. Once completed I would be sending the CSV file to a member of my team via Email. As this is a common request I wanted to automate this with PowerShell.

I had posted previously on basic database connectivity with PowerShell here and using that code as a basis I wrote a PowerShell script that connects to the SQL database and extracts the data into an SQLDataAdapter. The required table is selected and imported into a hash table, and then exported to a CSV file. This file is then attached to an email using the .NET SMTP Mail object and sent on its way via SMTP.

Download Script at PoshCode.org

Code:

#Connection Strings
$Database = "Database"
$Server = "SQLServer"
#SMTP Relay Server
$SMTPServer = "smtp.domain.com"
#Export File
$AttachmentPath = "C:\SQLData.csv"
# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT * FROM dbo.Test_Table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Hash Table
$objTable = $DataSet.Tables[0]
#Export Hash Table to CSV File
$objTable | Export-CSV $AttachmentPath
#Send SMTP Message
$Mailer = new-object Net.Mail.SMTPclient($SMTPServer)
$From = "email1@domain.com"
$To = "email2@domain.com"
$Subject = "Test Subject"
$Body = "Body Test"
$Msg = new-object Net.Mail.MailMessage($From,$To,$Subject,$Body)
$Msg.IsBodyHTML = $False
$Attachment = new-object Net.Mail.Attachment($AttachmentPath)
$Msg.attachments.add($Attachment)
$Mailer.send($Msg)

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

Quick Script: Converting Excel[.xls] to Comma Separated Value[.csv]

Many times my data is in .xls format and I really need it in .csv to leverage the accessibility of the Import-CSV cmdlet.

I found this code on the ‘PowerShellCommunity.org’ web forum: Link

Code:

$xlCSV=6
$Excelfilename = "file.xls"
$CSVfilename = "file.csv"
$Excel = New-Object -comobject Excel.Application
$Excel.Visible = $False
$Excel.displayalerts=$False
$Workbook = $Excel.Workbooks.Open($ExcelFileName)
$Workbook.SaveAs($CSVfilename,$xlCSV)
$Excel.Quit()
If(ps excel){kill -name excel}

I have only tested this with Office 2003 Sp2. I will try with 2007/2010 and post my findings.

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

How To: Modify VMWare Fusion 3.x Network Configurations and DHCP Configuration.

I am in the process of building a Microsoft Windows lab environment within VMWare Fusion 3 on my MAC.

I want to run an isolated “Host Based” network environment utilizing the DHCP services within VMWare Fusion.

Tasks:

1. Change VMNET Interface IP Addresses
2. Change the DHCP settings for the ‘host network only’ interface VMNET1 to enable correct DHCP based options and static leases for networking in Windows.

I found these articles and they helped a great deal;

• VMWare 3.X Virtual Network Configuration Instructions
• ‘VMWare Communities Discussion board’ inspired by Dave Parsons
• DHCP configuration article from the ThirdbIT blog

There are obviously several methods for this and I have tried to summarize the configuration steps below that I implemented for a successful Windows Network.

- Step 1)
Modify VMNET interface IP Addresses using ‘VMWare 3.x Virtual Network Configuration Instructions.pdf’

Open a Terminal window in OSX.

Change to the VMWare Fusion directory.

• cd /Library/Application\ Support/VMware\ Fusion/

Stop VMWare interfaces.

• sudo ./vmnet-apps.sh –stop

Verify VMNET interface are no longer available.

• ifconfig -a

Edit the ‘locations’ file to change the IP address values

• sudo nano locations

Example:

remove_answer VNET_1_HOSTONLY_HOSTADDR
remove_answer VNET_1_HOSTONLY_NETMASK
answer VNET_1_HOSTONLY_HOSTADDR 10.0.1.1
answer VNET_1_HOSTONLY_NETMASK 255.255.255.0
remove_answer VNET_1_DHCP
answer VNET_1_DHCP yes
remove_answer VNET_8_HOSTONLY_HOSTADDR
remove_answer VNET_8_HOSTONLY_NETMASK
answer VNET_8_HOSTONLY_HOSTADDR 10.0.8.1
answer VNET_8_HOSTONLY_NETMASK 255.255.255.0
remove_answer VNET_8_NAT
answer VNET_8_NAT yes
remove_answer VNET_8_DHCP
answer VNET_8_DHCP yes

Once edited successfully, type Ctrl-O and then Enter to save. Ctrl-X to exit.

Start VMWare Interfaces.

• sudo ./vmnet-apps.sh –start

Verify VMNET interfaces are now available with the new IP addresses.

• ifconfig -a

- Step 2)
Editing the DHCP configuration for VMNET1(host only) to issue the correct domain-name, domain-name-server options and static leases.

• cd /library/application\ support/vmware\ fusion/vmnet1

Backup your dhcpd.conf file.

• cp dhcpd.conf dhcpd.conf.backup

Edit your dhcpd.conf file.

• sudo nano dhcpd.conf

To allow name resolution in Active Directory you want to set the DHCP option domain-name-servers and domain-name for the DHCP scope and the for the VMNET1 adapter.
Options to set for the DHCP scope and VMNET1 adapter. (This would probably be your 1st DC in the domain.).

option domain-name-servers 10.1.0.1;
option domain-name "addomain.com";

Adding a reservation for a VM Guest. You will need to know the systems Host Name, MAC and TCP/IP Address.

Add the below text for each server after the “####### VMNET DHCP Configuration. End of “DO NOT MODIFY SECTION” #######” statement. When assigning a TCP/IP address to a guest ensure that it is outside of the DHCP scope.

Example: (reservation for Server01)

host Server01 {
hardware ethernet 00:00:00:00:00:00;
fixed-address 10.1.0.1;
option domain-name-servers 10.1.0.1;
option domain-name "addomain.com";
}

Example: (completed dhcpd.conf file)

# Configuration file for ISC 2.0 vmnet-dhcpd operating on vmnet1.
#
# This file was automatically generated by the VMware configuration program.
# See Instructions below if you want to modify it.
#
# We set domain-name-servers to make some DHCP clients happy
# (dhclient as configured in SuSE, TurboLinux, etc.).
# We also supply a domain name to make pump (Red Hat 6.x) happy.
#
###### VMNET DHCP Configuration. Start of "DO NOT MODIFY SECTION" #####
# Modification Instructions: This section of the configuration file contains
# information generated by the configuration program. Do not modify this
# section.
# You are free to modify everything else. Also, this section must start
# on a new line
# This file will get backed up with a different name in the same directory
# if this section is edited and you try to configure DHCP again.
# Written at: 01/11/2010 00:31:51
allow unknown-clients;
default-lease-time 1800;                # default is 30 minutes
max-lease-time 7200;                    # default is 2 hours

subnet 10.1.0.0 netmask 255.255.255.0 {
range 10.1.0.128 10.1.0.254;
option broadcast-address 10.1.0.255;
option domain-name-servers 10.1.0.1;
option domain-name "addomaincom";
default-lease-time 1800;                # default is 30 minutes
max-lease-time 7200;                    # default is 2 hours
}
host vmnet1 {
hardware ethernet 00:50:56:C0:00:01;
fixed-address 10.1.0.1;
option domain-name-servers 10.1.0.1;
option domain-name "addomain.com";
}
####### VMNET DHCP Configuration. End of "DO NOT MODIFY SECTION" #######
host Server01 {
hardware ethernet 00:00:00:00:00:00;
fixed-address 10.1.0.1;
option domain-name-servers 10.1.0.1;
option domain-name "addomain.com";
}

Once edited successfully, type Ctrl-O and then Enter to save. Ctrl-X to exit.

Restart VMWare daemon’s.

• sudo /library/application\ support/vmware\ fusion/boot.sh –restart

Once restarted you should restart your Virtual Machines that you have assigned the reserved leases to.

If you have problems with failures when restarting the VMWare daemon’s you may have a badly formatted dhcpd.conf file, restore the file from your backup and try again.

If you have issues getting the reserved IP addresses, try renewing their leases.

• C:\> IPConfig /release
• C:\> IPConfig /renew

The above process worked for me but if you have issues either post a comment and I will try to help or i recomend the VMWare community web site mentioned above.

Note: Please backup all files before editing them!

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.