Category Archives: powershell

powershell goodies for Active Directory

What are my groups?

PS> Get-ADPrincipalGroupMembership lsc |
      select -ExpandProperty "name"
Domain Users

Who is member of that group ?

PS> Get-ADGroupMember oracle| 
      select -ExpandProperty "name"
Laurent Schneider
Alfred E. Newmann
Scott Tiger

What is my phone number ?

PS> (get-aduser lsc -property MobilePhone).MobilePhone
+41 792134020

This works like a charm on your Windows 7 PC.
1) Download and install Remote Server Administration Tools
2) Activate the windows feature under control panel program called “Active Directory Module for Powershell”
3) PS> Import-Module ActiveDirectory
Read the procedure there : how to add active directory module in powershell in windows 7

specify TNSNAMES for one program

Monday I wrote on tnsping.exe inconsistencies. Actually there is one good thing in having Oracle Client on Windows looking in the current directory first : you can set one tnsnames for a specific shortcut ! It is quite a viable alternative to .bat files with set TNS_ADMIN=path.

Demo :

First I create a small EXE in C#


using System;
using System.Threading;
using Oracle.DataAccess.Client;

class HelloWorld
  static void Main() {
    OracleConnection connection=
      new OracleConnection("Data Source=DB01; User Id=scott; password=tiger");
    try {
      Console.WriteLine("Msg: " + (new OracleCommand(
        "select * from global_name",connection)).ExecuteScalar());
    } catch(Exception e) {
      Console.WriteLine("Exception Occured :{0}",e.Message);
    } finally {


C:\Windows\Microsoft.NET\Framework\v4.0.*\csc.exe /R:C:\oracle\product\11.2.0\client_1\\bin\4\Oracle.DataAccess.dll HelloWorld.cs

Create a specific tnsnames and sqlnet





C:\TEMP> HelloWorld

To create a desktop icon with the correct WorkingDirectory with powershell

PS> $ws =  New-Object -comObject WScript.Shell
PS> $desktop = [Environment]::GetFolderPath("Desktop")
PS> $s = $ws.createshortcut($desktop+"\HelloWorld.lnk")
PS> $s.TargetPath = "C:\TEMP\HelloWorld.exe"
PS> $s.WorkingDirectory = "C:\TEMP"
PS> $s.Save()

unreadable output file in powershell

If you redirect a not-completly-string output (like a spfile) to a file in powershell, you may not see the same in the file as in the output

  • without redirection
    PS C:\> Select-String "compatible" .\spfileDB01.ora
  • with redirection
    PS> Select-String "compatible" .\spfileDB01.ora > compatible.txt
    PS> vim -b .\compatible.txt
  • With redirection and conversion to ascii
    PS> Select-String "compatible" .\spfileDB01.ora | 
      Out-File -Encoding ASCII .\compatible.txt
    PS> vim .\compatible.txt

With Out-File (instead of >), you can specify the encoding, which does the trick

[Hello World] run c# from powershell

How to run csharp code from command line with powershell. One line

PS> add-type 'public class c{public const string s="hello world";}';[c]::s
hello world

You can also execute visual basic directly from powershell.

PS> Add-Type -language visualbasic 'public class v
>> public const s as string = "Hello World"
>> end class'
PS> [v]::s
Hello World

and also jscript

PS> add-type -language jscript -name j -memberdefinition 'class x{public const s="hello world";}'
PS> [j]::s
hello world

[Windows] Email of current user

I hate having to type my email address, so I created a long one-liner to do the trick of getting my email from Exchange and copying it in my clipboard

powershell -noprofile -command "$o=New-Object DirectoryServices.DirectorySearcher; $o.SearchRoot=New-Object DirectoryServices.DirectoryEntry;$o.Filter='samaccountname='+$ENV:USERNAME;write-host ($o.FindOne().Properties.mail)" | clip

save this as “C:\WINDOWS\E.BAT”

Then, when you have to enter your email in a form or document or login screen,
[⊞+R] [e] [enter] [CTRL+V]
4 keystrokes (+ 2 mod)

Explanation :

C:\>powershell -noprofile
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\> $o=New-Object DirectoryServices.DirectorySearcher;
### new object to browse Microsoft AD
PS C:\> $o.SearchRoot=New-Object DirectoryServices.DirectoryEntry;
### the base dn
PS C:\> $o.Filter='samaccountname='+$ENV:USERNAME;
### the search filter, your Username
PS C:\> $o.FindOne().Properties.mail
### find one (not necessarly 100% safe) and print the mail property

weekinmonth in powershell starts on Sunday…

This April month is full of surprises! Not only I did not have the opportunity to trick my colleagues on Sunday April first, but I had some bug of my code due to the start of the week.

[int](get-date).dayofweek and (Get-WmiObject Win32_LocalTime).dayofweek both refer Sunday as day 0.

PS> get-date
Wednesday, 11. April 2012 18:31:52
PS C:\svn> [int](get-date).dayofweek
PS C:\svn> (Get-WmiObject Win32_LocalTime).dayofweek

Consistently and unfortunately for me, the week does start on Sunday. And not on Monday.

To get week ending on Sunday, lets add 6 for Monday (5 for Tuesday, and so on) to dayofweek and get the 7-modulo

PS> (6+(get-date).dayofweek)%7

Ok, now Mon=0, Tue=1, Wed=2, etc…

Substract it from day of month to truncate to Monday

PS> (get-date).day - (6+(get-date).dayofweek)%7

First day of current week is Monday 9th

Now add 5 to get the first Day of month between 0 and 6
Divide by 7…
add 1 to get first week=1 (and not 0).


PS> [math]::floor(((get-date).day - (6+(get-date).dayofweek)%7 + 5)/7)+1

Which slicely differs from weekinmonth when 1st of month is Sunday !

PS> (Get-WmiObject Win32_LocalTime).weekinmonth


to ADO or to ODP ?

I just read on System.Data.OracleClient Namespace that System.Data.OracleClient are deprecated.
The types in System.Data.OracleClient are deprecated. The types are supported in version 4 of the .NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider.

The alternative is to use ODP (from Oracle) instead of ADO (from Microsoft). More powerful of course.

ADO (within powershell):

PS C:\> [Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_32\\\

PS C:\> $connection=New-Object DATA.OracleClient.OracleConnection("Data Source=DB01;User Id=scott;Password=tiger")
PS C:\> $connection.Open()
PS C:\> (new-Object DATA.OracleClient.OracleCommand("select 'Hello World' from dual",$connection)).ExecuteScalar()
Hello World
PS C:\> $connection.Close()


PS C:\> [Reflection.Assembly]::LoadFile("C:\oracle\product\11.1.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll")

GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_32\Oracle.DataAccess\\Oracle.DataAccess.dll

PS C:\> $connection=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")
PS C:\> (new-object Oracle.DataAccess.Client.OracleCommand("select 'helloworld' from dual",$connection)).executescalar()
PS C:\> $connection.close()
PS C:\> $connection.dispose()

Read more : Oracle Data Provider for .NET Developer’s Guide

PS: also possible

PS> $dataset = New-Object Data.dataset
PS> (New-Object Oracle.DataAccess.Client.OracleDataAdapter("select * from emp",$connection)).fill($dataset)
PS> ($dataset.tables[0]|where{ $_.empno -eq 7902 }).ename

on Windows Management Instrumentation

I have a bit versed into powershell over the last months, I just cannot stop discovering new gems.

Ex : Win32 Classes

Self-explanatory examples :

PS> (get-wmiobject win32_processor).currentclockspeed
PS> (get-wmiobject win32_operatingsystem).version
PS> (get-wmiobject win32_proxy).ProxyServer
PS> (get-wmiobject win32_proxy).ProxyPortNumber

it goes on and on and on… the ways those things are accessible from the prompt in interactive command line mode or within a script is amazing me

Unix timestamp in powershell

I was a bit playing with powershell. I wanted to know the week of month, day of weeks, and other gems. It is chockingly easy !

Unix Timestamp :

PS> get-date -uformat %s

do not miss this important update : Powershell and Dates
To get the day of week as number, you can chose between [int](get-date).dayofweek and get-date -uformat %u

What does uformat stand for ? Unix Format ! This is probably why powershell is so good, they integrated all unix and .net goodies in one tool :-)

Ok, one more, get the week of month :

PS> (Get-WmiObject Win32_LocalTime).weekinmonth

change system wide PATH variable in Windows

This is utterly simple and mostly you did it with the mouse.

To do it with command line and powershell, proceed this way.

Open a powershell window


or if you have no right to edit the machine-wide environment, runas admin

runas /user:yourdomain\youruserplusadmin powershell

then access the environment assembly

[environment]::setEnvironmentVariable("PATH","c:\oracle\product\11.2.0\client_1\bin;C:\WINDOWS\;C:\WINDOWS\system32;C:\Program Files\Putty;C:\Program Files\Perforce;C:\Program Files\TortoiseSVN\bin;C:\WINDOWS\system32\WindowsPowerShell\v1.0","MACHINE")

MACHINE is a for a Machine-wide setting and USER is for a USER-wide setting.

I hope it will be of some use

Completly unrelated, Enterprise Manager 12c for AIX is out. Do not wait it for HPUX, it is not planned. For Windows 64bit, it will come “between March and May 2012″ according to note 793512.1 on metalink

xml and powershell : using XPATH

I wrote about powershell [xml] yesterady : xml and powershell

Let’s see how to use XPATH expressions in Powershell

  <emp no="1">
  <emp no="2">

With the [xml] datatype, we create a navigator :

(([xml](GC foo.xml)).psbase.createnavigator().evaluate(


I have not been seduced by a Microsoft product since ages, but I must say I felt with love in this goody much more than in perl, cygwin, or whatever python, dos, java, vb…

It is simply great to use on the command line and can do my work.

1:0 for Microsoft