Friday, October 29, 2010

Christophe BLUTEAU's site

An other interesting site on powershell and exchange, but in french, is Christophe BLUTEAU's site
http://aidexchange.fr/

Tuesday, October 26, 2010

Get Windows Network configuration Remotely

Today a 1 hour script. We need to know some network informations on a lot of computers, especially DNS search order.

In powershell, that's easy done by using

$strComputername = "localhost"
$NICs = Get-WmiObject Win32_NetworkAdapterConfiguration -Computername $strComputername | Where {$_.IPEnabled -eq "TRUE"}
foreach ($nic in $nics) {
$nic.DNSServerSearchOrder
}



Unfortunately, for a lot of reason, I can't use powershell. No time to lose. So I use one of my old vbscript and adapt it using information coming from Microsoft Site(Automating TCP/IP Networking on Clients - Part 2: Scripting Basic TCP/IP Networking on Clients)

That's the result that you can download too



'
' Get All Network Configuration
'
'
' F.RICHARD
' 2010 October
'

Option Explicit
Const ForReading = 1, ForWriting = 2, ForAppending = 8


' Detect WScript or CScript
'
If InStr(LCase(WScript.FullName), "wscript")>0 Then ' ex: FullName = C:\WINNT\system32\wscript.exe
WScript.Echo "This script must be run under CScript."
WScript.Quit
End If


' Get Path
'
Dim strScriptFullName, strScriptPath, strScriptName
strScriptName = WScript.ScriptName
strScriptFullName = WScript.ScriptFullName ' ex: C:\Program Files\MyFolder\MyProg.exe
strScriptPath = Left(strScriptFullName, InStrRev(strScriptFullName, "\") - 1) ' ex: C:\Program Files\MyFolder


' Variables For File
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")


' Input parameter
'
Dim objInputFile, strInputFile
If WScript.Arguments.Named.Exists("input") Then
strInputFile = WScript.Arguments.Named("input")

If Not isFileExist(strInputFile) Then
If Not isFileExist(strScriptPath & "\" & strInputFile) Then
WScript.Echo strInputFile & " File Not Exist !"
WScript.Echo strScriptPath & "\" & strInputFile & " File Not Exist !"
WScript.Quit
End If
strInputFile = strScriptPath & "\" & strInputFile
End If
Set objInputFile = objFso.OpenTextFile(strInputFile, ForReading)
Else
WScript.Echo "You must defined an argument /input ex: /input:input.txt !"
WScript.Quit

End If


' Output parameter
Dim strOutputFile, objOutputFile
If WScript.Arguments.Named.Exists("output") Then
strOutputFile = WScript.Arguments.Named("output")
Else
WScript.Echo "You must defined an argument /output ex: /input:output.txt !"
WScript.Quit

End If
On Error Resume Next
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, ForWriting, True) ' delete file
If (Err.Number <> 0) Then
WScript.Echo strOutputFile & " file - permission denied or file already opened"
On Error GoTo 0
WScript.Quit
End If
On Error GoTo 0




' Put servers in Dictionary
'
Dim objItem, objDictionary, objTextFile, strNextLine, i
Set objDictionary = CreateObject("Scripting.Dictionary")
Set objTextFile = objFSO.OpenTextFile (strInputFile, ForReading)
i = 0
Do Until objTextFile.AtEndOfStream
strNextLine = Trim(objTextFile.Readline)
If (Len(strNextLine)>1) Then
If Not Left(strNextLine, 1) = "#" Then
objDictionary.Add i, strNextLine
i = i + 1
End If
End If
Loop

Dim line
line = "Hostname;NICDescription;MACAddress;IPAddress;IPSubnet;DefaultIPGateway;DHCPEnabled;DHCPServer;DNSDomain;DNSDomainSuffixSearchOrder;WINSPrimaryServer;WINSSecondaryServer;DNSServerSearchOrder1;DNSServerSearchOrder2"
WScript.Echo line
objOutputFile.WriteLine(line)

Dim objWMIService, objWMIServiceRemote, colAdapters, objAdapter
Dim strComputerName, computers, objComputer
Dim DNSHostname, NICDescription, MACAddress, IPAddress, IPSubnet, DefaultIPGateway
Dim DHCPEnabled, DHCPServer, DNSDomain, DNSDomainSuffixSearchOrder, WINSPrimaryServer, WINSSecondaryServer
Dim DNSServerSearchOrder
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
For Each objItem in objDictionary
strComputerName = objDictionary.Item(objItem)
WScript.Echo strComputerName
Set Computers = objWMIService.ExecQuery ("Select * from Win32_PingStatus Where Address = '" & strComputerName & "'")
For Each objComputer in Computers
If objComputer.StatusCode = 0 Then
' suppress errors in case of errors
On Error Resume Next
Set objWMIServiceRemote = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputerName & "\root\cimv2")

' Only query Enabled NIC
Set colAdapters = objWMIServiceRemote.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
'Wscript.echo Err.Number & ";" & Err.Description

Select Case Err.number
'Case "-2147022676" :
' line = strComputerName & ";" & strGroupToCheck & " group NOT EXIST"
' WScript.Echo line
' objOutputFile.WriteLine(line)

Case "-2147024843" :
line = strComputerName & ";INVALID Network Path OR insufficient access rights"
WScript.Echo line
objOutputFile.WriteLine(line)

'Case "-2147463168" :
' line = strComputerName & ";INVALID ADSI WinNT://" & strComputerName & "/" & strGroupToCheck & ",group"
' WScript.Echo line
' objOutputFile.WriteLine(line)

Case Else :
For Each objAdapter in colAdapters
' DNS Host name
DNSHostname =""
DNSHostname = objAdapter.DNSHostName


' NIC Description
NICDescription = objAdapter.Description


' MAC Address
MACAddress = ""
MACAddress = objAdapter.MACAddress


' IP Address
IPAddress = ""
If Not IsNull(objAdapter.IPAddress) Then
For i = 0 To UBound(objAdapter.IPAddress)
If (IPAddress = "") Then
IPAddress = objAdapter.IPAddress(i)
Else
IPAddress = IPAddress & "_" & objAdapter.IPAddress(i)
End If
Next
End If


' IP Subnet
IPSubnet = ""
If Not IsNull(objAdapter.IPSubnet) Then
For i = 0 To UBound(objAdapter.IPSubnet)
If (IPSubnet = "") Then
IPSubnet = objAdapter.IPSubnet(i)
Else
IPSubnet = IPSubnet & "_" & objAdapter.IPSubnet(i)
End If
Next
End If


' Default Gateway
DefaultIPGateway = ""
If Not IsNull(objAdapter.DefaultIPGateway) Then
For i = 0 To UBound(objAdapter.DefaultIPGateway)
If (DefaultIPGateway = "") Then
DefaultIPGateway = objAdapter.DefaultIPGateway(i)
Else
DefaultIPGateway = DefaultIPGateway & "_" & objAdapter.DefaultIPGateway(i)
End If
Next
End If


' DHCP Enabled
DHCPEnabled = ""
DHCPEnabled = objAdapter.DHCPEnabled


' DHCP server
DHCPServer = ""
DHCPServer = objAdapter.DHCPServer


' DNS Domain
DNSDomain = objAdapter.DNSDomain


' DNS Domain Suffix Search Order
DNSDomainSuffixSearchOrder = ""
If Not IsNull(objAdapter.DNSDomainSuffixSearchOrder) Then
For i = 0 To UBound(objAdapter.DNSDomainSuffixSearchOrder)
If (DNSDomainSuffixSearchOrder = "") Then
DNSDomainSuffixSearchOrder = objAdapter.DNSDomainSuffixSearchOrder(i)
Else
DNSDomainSuffixSearchOrder = DNSDomainSuffixSearchOrder & "_" & objAdapter.DNSDomainSuffixSearchOrder(i)
End If
Next
End If

WINSPrimaryServer = objAdapter.WINSPrimaryServer
WINSSecondaryServer = objAdapter.WINSSecondaryServer

' DNS Search Order
DNSServerSearchOrder = ""
If Not IsNull(objAdapter.DNSServerSearchOrder) Then
For i = 0 To UBound(objAdapter.DNSServerSearchOrder)
DNSServerSearchOrder = DNSServerSearchOrder & ";" & objAdapter.DNSServerSearchOrder(i)
Next
End If


line = DNSHostname & ";" & NICDescription & ";" & MACAddress & ";" & IPAddress & ";" & IPSubnet & ";" & DefaultIPGateway & ";" & DHCPEnabled & ";" & DHCPServer & ";" & DNSDomain & ";" & DNSDomainSuffixSearchOrder & ";" & WINSPrimaryServer & ";" & WINSSecondaryServer & DNSServerSearchOrder
WScript.Echo line
objOutputFile.WriteLine(line)
Next

End Select
On Error Goto 0
Else
line = strComputerName & ";NOT available"
WScript.Echo line
objOutputFile.WriteLine(line)
End If
Next
Next


'--------------------


' Help
'
' Help File
'

Sub Help(ByRef strScriptName)
WScript.Echo strScriptName & " Arguments" & vbCrLf _
& vbTab & "/input:""filename_for_server_list""" & vbCrLf _
& vbTab & "/output:""filename_for_output""" & vbCrLf _
& vbTab & "/? or nothing for this" & vbCrLf _
& vbCrLf & "Example:" & vbCrLf _
& "cscript " & strScriptName & " /input:""servers.txt"" /input:""result.csv"" " & vbCrLf
WScript.Quit(0)
End Sub


'--------------------

' isFileExist
'
' Test if File Exist
'
Function IsFileExist(ByRef strInputFile)
Dim iReturn
iReturn = False

Dim objFSO
On Error Resume Next
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strInputFile) Then
iReturn = True
End If
Set objFSO = Nothing
On Error Goto 0

IsFileExist = iReturn
End Function


'--------------------

' pingComputer
'
' Ping Test
'
Function pingComputer(strComputerName)

Dim pingStatus, objPing, objStatus
pingStatus = false
Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}")
Set objPing = objPing.ExecQuery("select * from Win32_PingStatus where address = '" & strComputerName & "'")
For Each objStatus in objPing
If IsNull(objStatus.StatusCode) or objStatus.StatusCode<>0 Then
pingComputer = false
Else
pingComputer = true
End If
Next


End Function

'--------------------

Friday, October 15, 2010

Powershell with MySql

To do Mysql select with powershell, you first must download on MySQL site the MySQL Connector .NET

For information, on my computer, I have a "CAQuietExec Failed" error with mysql connector v6.2.5. No problem with v6.1.5 (MySQL Connector Previous Version)

To use Mysql connector, you must load assembly then use MySql connection. You can download script here


#
#
# Export MySQL Infos
#
# by F.Richard 2010-09
#

set-psdebug -strict


# Script Directory
$strCurDir = Split-Path -parent $MyInvocation.MyCommand.Path
Set-Location $strCurDir | Out-Null
#Write-Host "Current Dir: $strCurDir"

#$debug = $True
$debug = $False

# need MySQL Connector
#
# in PS2.0 Add-Type -AssemblyName
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

# ***********************************************



Function executeMYSQLQuery {
Param ([hashtable]$options)
$strServer = $(if ($options.ContainsKey("strServer")) {$options["strServer"]} else {"(localhost)"})
$strDatabase = $(if ($options.ContainsKey("strDatabase")) {$options["strDatabase"]} else {"default"})
$strCommandTimeout = $(if ($options.ContainsKey("strCommandTimeout")) {$options["strCommandTimeout"]} else {"15"})
$strPort = $(if ($options.ContainsKey("strPort")) {";Port=" + $options["strPort"]} else {""})

$strUser = $(if ($options.ContainsKey("strUser")) {";User Id=" + $options['strUser']} else {""})
$strPassword = $(if ($options.ContainsKey("strPassword")) {";Password=" + $options['strPassword']} else {""})
$strAuthentication = $(if ($strUser -and $strPassword) {"$strUser$strPassword"} else {""})
$strMisc = $(if ($options.ContainsKey("strMisc")) {";" + $options['strMisc']} else {""})

$strQuery = $(if ($options.ContainsKey("strQuery")) {$options['strQuery']} else {""})

If ($debug) {
Write-Host "DEBUG: strServer:" $strServer
Write-Host "DEBUG: strDatabase:" $strDatabase
Write-Host "DEBUG: strCommandTimeout:" $strCommandTimeout
Write-Host "DEBUG: strPort:" $strPort
Write-Host "DEBUG: strUser:" $strUser
Write-Host "DEBUG: strPassword:" $strPassword
Write-Host "DEBUG: strAuthentication:" $strAuthentication
Write-Host "DEBUG: strMisc:" $strMisc
Write-Host "DEBUG: strQuery:" $strQuery
}

# Create SqlConnection
$conn = New-Object ('MySql.Data.MySqlClient.MySqlConnection')
If (!$conn) {
Write-Host "ERROR: MySql Connection could not be created!"
Exit
}
$connString = "Server=$strServer$strAuthentication;Database=$strDatabase$strPort$strMisc"
If ($debug) {
Write-Host "DEBUG: Sql Connection String:" $connString
}

# more information http://www.connectionstrings.com/mysql
$conn.ConnectionString = $connString
$dtResult = New-Object "System.Data.DataTable"
$conn.Open()
If ($conn.State -eq 1) {
$sqlCmd = New-Object "MySql.Data.MySqlClient.MySqlCommand"
If ($sqlCmd) {
$sqlCmd.CommandTimeout = $strCommandTimeout
$sqlCmd.CommandText = $strQuery
$sqlCmd.Connection = $conn

# INSERT, UPDATE or DELETE ExecuteNonQuery()
# SELECT ExecuteReader()
$data = $sqlCmd.ExecuteReader()
$dtResult.Load($data) # fill
$data.Dispose()
$sqlCmd.Dispose() # frees all resources that were used by the object.
} Else {
Write-Host "ERROR: Cannot create SqlCommand object!";
}
} Else {
Write-Host "ERROR: Connection cannot be opened!";
}
$conn.Close()
$conn = $Null

#$dtResult | Format-Table -autosize
return $dtResult
}


# ***********************************************

Function getInfos {
$query = "SELECT * FROM YourTable"

# Here Table name is YourTable
# and fields name are Field1, Field1 and Field3

$dtResult = New-Object "System.Data.DataTable"

$hashOptions = @{ }
$hashOptions["strServer"] = "localhost"
$hashOptions["strDatabase"] = "db_name"
$hashOptions["strUser"] = "user"
$hashOptions["strPassword"] = "password"
$hashOptions["strQuery"] = $query
$dtResult = executeMYSQLQuery($hashOptions)

If ($dtResult -ne $Null) {
Foreach ($row in $dtResult){
$result = $row.Field1 + ";" + $row.Field2 + ";" + $row.Field3
Write-Host $result
}

}
$dtResult = $Null
}

# ***********************************************

getInfos

Friday, October 8, 2010

ODBC backslash problem error with MySQL

Today, I must use a software from CA names DSM reporter that permit me to export by ODBC some information to a MySQL database
All informations displayed in DSM reporter. But when I schedule this report to export datas to MySQL, it doesn't work.
Problem: I use the same ODBC , the same user than an other report that works very well. Moreover, all tables are well created, but no insertion was done.

So I use tracing. For this, click on start / Settings / Control Panel / Administrative Tools / Data Sources (ODBC). Select your System or User Data sources then click on Configure button. In details there is a Debug tab. Just enable "log queries to myodbc.sql"


Run your MySQL ODBC export again (for me,here, with DSM Reporter export / ODBC Database Table on a result). Now just to analyse C:\myodbc.sql file.
Finally, I found my problem come from \ (backslash) which is considered in MySQL like an Escape character. So I resolve problem as indicated in http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html, using NO_BACKSLASH_ESCAPES mode.

You can see your current sql mode by

SELECT @@SESSION.sql_mode;

or globally by

SELECT @@GLOBAL.sql_mode;


Now, to change sql mode in your current connection:

SET sql_mode='NO_BACKSLASH_ESCAPES';

To set this mode globally any connections:

SET GLOBAL sql_mode='NO_BACKSLASH_ESCAPES'


And if you don't want any sql mode options in your current connection:

SET sql_mode='';

Anf globally for all connections:

SET GLOBAL sql_mode=''

Wednesday, October 6, 2010

Get prod or dev environment by IP address

I have some DMZ with same computer name (...). These computers use one of my schedule script to retrieve some computers informations and put them by ssh on a secure ftp server. As these computers don't have same IP address, I can differentiate computer environnement (Production or Dev) using a list I get before using the same secure ftp server.

So here, script part to get prod or dev environment by IP address (Download it)

You need at least psftp from http://www.chiark.greenend.org.uk/~sgtatham/putty/ (Alternative Download). Put these files in the same directory than you will put GetEnvByIP.ps1 file.



#
# Get Env By IP Address
#
# by F.Richard 2010-10
#

set-psdebug -strict

# Script Directory
$strCurDir = Split-Path -parent $MyInvocation.MyCommand.Path
Set-Location $strCurDir | Out-Null


# Computername
$Computername = Get-Content env:COMPUTERNAME


# get IP file by FTP
# Ip File format
# ip,csv
$ftp_cmdline = "
get ip.csv
quit
"
$ftp_cmdline | out-file -encoding OEM ".\ftp-get.txt"
$cmdline = $strCurDir + "\psftp yourftpserver -l user -pw password -b `"" + $strCurDir + "\ftp-get.txt`""
cmd /c $cmdline


# Get IP/env into Hash
$hashIP = @{ }
$content = import-csv "$strCurDir\ip.csv"
foreach ($line in $content) {
$IPAddress = $line.ip.Trim()
If ($IPAddress.length -gt 0) {
$env = $line.env.Trim()
$hashIP["$IPAddress"] = "$env"
}
}

# Detect IPs and display env
$StringToAdd="unknown"
$NetworkAdapterConfigurations = Get-WMIObject -Class Win32_NetworkAdapterConfiguration -computername $Computername
foreach ($NetworkAdapterConfiguration in $NetworkAdapterConfigurations) {
$IPAddress = $NetworkAdapterConfiguration.IPAddress
If ($hashIP.ContainsKey("$IPAddress")) {
$StringToAdd = $hashIP["$IPAddress"]
}
}
Write-Host "This computer is in $StringToAdd environment"



Ip.csv file should be like this

ip,csv
192.168.1.10,dev
192.168.1.20,prod


Do not forget to change variables yourftpserver user and password by your ftp, your user and your password. And put an ip.csv file on your ftp server.

This script use psftp for secure ftp but if you don't need secure ftp, you can use windows ftp by replacing psftp part by these lines


# get IP file by FTP
# Ip File format
# ip,csv
$ftp_cmdline = "
open yourftpserver
ftpuser
ftppassword
prompt
get ip.csv
quit
"
$ftp_cmdline | out-file -encoding OEM ".\ftp-get.txt"
$cmdline = "ftp -i -s:`"" + $strCurDir + "\ftp-get.txt`""
cmd /c $cmdline