Christophe BLUTEAU's site

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

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

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

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

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=''

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