SelectEtoile : Base de connaissance SGBD

Posts Tagged 'powershell'

Import Données Perfmon dans une table avec Powershell

Ce script fonctionne pour la version SQLServer 2008. Je n'ai pas tester pour d'autre version encore.

Il fonctionne pour des instances nommées. Mais peu de modifications sont necessaire pour l'adapter.

 

 

 

Param (
[string]$ServerName,
[string]$InstanceName
)




##### VARIABLES

$SQLServiceName = 'MSSQL$'+$InstanceName
$FullInstanceName = $ServerName+"\"+$InstanceName

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$ServerNameSQL = New-Object "Microsoft.SqlServer.Management.Smo.Server" $FullInstanceName
$ClusterState=0



##### FUNCTIONS 


function Test_PSSnapin {
$sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin100"}
if($sqlSnapin -eq $null)
{
Add-PSSnapin SqlServerCmdletSnapin100
}
}



function fn_imp-perfmon
{

#Build array of all counter that we want to collect
$counterPath = @()  

# get the version of the current instance
$version = $ServerNameSQL | SELECT Version
#$prefixVersion = $version.version.substring(0,$version.version.IndexOf('.'));
$prefixVersion = 10


if(($prefixVersion -eq 10)) # si SQLserver 2008/2008R2 en instance par défaut en cluster ou standalone
{
$counterType = 0;
}
elseif (($prefixVersion -eq 11) -and ($ClusterState -eq 0))# si SQLserver 2012 en instance par défaut mais pas en cluster
{
$counterType = 0;
}
elseif (($prefixVersion -eq 11) -and ($ClusterState -eq 1))# si SQLserver 2012 en instance par défaut mais  en cluster
{
$counterType = 1;
}
else
{
$counterType = 1;
}



# get the counter prefix on the instance inside PERFMON

if($counterType -eq 0)
{  
$counterPath +="\"+$SQLServiceName+":Cursor Manager by Type(_Total)\Cursor Requests/sec"
$counterPath +="\"+$SQLServiceName+":General Statistics\Processes Blocked"
$counterPath +="\"+$SQLServiceName+":General Statistics\Temp Tables Creation Rate"
$counterPath +="\"+$SQLServiceName+":General Statistics\Active Temp Tables"
$counterPath +="\"+$SQLServiceName+":General Statistics\Temp Tables For Destruction"
$counterPath +="\"+$SQLServiceName+":General Statistics\Logins/sec"
$counterPath +="\"+$SQLServiceName+":General Statistics\Logouts/sec"
$counterPath +="\"+$SQLServiceName+":General Statistics\User Connections"
$counterPath +="\"+$SQLServiceName+":General Statistics\Logical Connections"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Lock waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Log write waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Network IO waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Non-Page latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Page IO latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Page latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Wait for the worker"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Total Server Memory (KB)"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Target Server Memory (KB)"
$counterPath +="\"+$SQLServiceName+":Access Methods\Forwarded Records/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\FreeSpace Scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Full Scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Index Searches/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Mixed page allocations/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Page Splits/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Range scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Scan Point Revalidations/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Workfiles Created/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Worktables Created/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Worktables From Cache Ratio"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Buffer cache hit ratio"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Buffer cache hit ratio base"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Checkpoint pages/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Free pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Total pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Target pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Database pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Stolen pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Lazy writes/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page life expectancy"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page lookups/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page reads/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page writes/sec"
$counterPath +="\"+$SQLServiceName+":Latches\Latch Waits/sec"
$counterPath +="\"+$SQLServiceName+":Latches\Total Latch Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Latches\Average Latch Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Latches\Average Latch Wait Time Base"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Timeouts (timeout > 0)/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Requests/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Waits/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Number of Deadlocks/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Average Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Average Wait Time Base"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Memory Grants Pending"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Connection Memory (KB)"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Optimizer Memory (KB)"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\Batch Requests/sec"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\SQL Compilations/sec"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\SQL Re-Compilations/sec"
$counterPath +="\"+$SQLServiceName+":Plan Cache(_Total)\Cache Hit Ratio"
$counterPath +="\"+$SQLServiceName+":Plan Cache(_Total)\Cache Hit Ratio Base"
$counterPath +="\"+$SQLServiceName+":Plan Cache(_Total)\Cache Pages"
$counterPath +="\"+$SQLServiceName+":Transactions\Free Space in tempdb (KB)"
$counterPath +="\"+$SQLServiceName+":Databases(_Total)\Transactions/sec"
$counterPath +="\"+$SQLServiceName+":Databases(_Total)\Log Flushes/sec"
$counterPath +="\"+$SQLServiceName+":Databases(tempdb)\Data File(s) Size (KB)"
$counterPath +="\"+$SQLServiceName+":Databases(tempdb)\Log File(s) Size (KB)"
$counterPath +="\"+$SQLServiceName+":Databases(tempdb)\Log File(s) Used Size (KB)"
}
else
{
#bug : inhibit $ inside the counter name
#fixed with Aaron Bertrand's post
#http://sqlblog.com/blogs/aaron_bertrand/archive/2011/05/03/a-little-powershell-syntax-tip-dealing-with-sql-server-named-instances.aspx

$counterPath +="\"+$SQLServiceName+":Cursor Manager by Type(_Total)\Cursor Requests/sec"
$counterPath +="\"+$SQLServiceName+":General Statistics\Processes Blocked"
$counterPath +="\"+$SQLServiceName+":General Statistics\Temp Tables Creation Rate"
$counterPath +="\"+$SQLServiceName+":General Statistics\Temp Tables For Destruction"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Lock waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Log write waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Network IO waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Non-Page latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Page IO latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Page latch waits"
$counterPath +="\"+$SQLServiceName+":Wait Statistics(Average wait time (ms))\Wait for the worker"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Total Server Memory (KB)"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Target Server Memory (KB)"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Timeouts (timeout > 0)/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Forwarded Records/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\FreeSpace Scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Full Scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Index Searches/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Mixed page allocations/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Page Splits/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Range scans/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Scan Point Revalidations/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Workfiles Created/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Worktables Created/sec"
$counterPath +="\"+$SQLServiceName+":Access Methods\Worktables From Cache Ratio"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Buffer cache hit ratio"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Checkpoint pages/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Free pages"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Lazy writes/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page life expectancy"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page lookups/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page reads/sec"
$counterPath +="\"+$SQLServiceName+":Buffer Manager\Page writes/sec"
$counterPath +="\"+$SQLServiceName+":Latches\Latch Waits/sec"
$counterPath +="\"+$SQLServiceName+":Latches\Total Latch Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Requests/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Waits/sec"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Lock Wait Time (ms)"
$counterPath +="\"+$SQLServiceName+":Locks(_Total)\Number of Deadlocks/sec"
$counterPath +="\"+$SQLServiceName+":Memory Manager\Memory Grants Pending"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\Batch Requests/sec"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\SQL Compilations/sec"
$counterPath +="\"+$SQLServiceName+":SQL Statistics\SQL Re-Compilations/sec"
}


$counterPath +="\Memory\Cache Bytes"
$counterPath +="\Memory\Available MBytes"
$counterPath +="\Memory\Pages/sec"
$counterPath +="\Memory\Pages Input/sec"
$counterPath +="\Memory\System Cache Resident Bytes"
$counterPath +="\System\Processor Queue Length"
$counterPath +="\LogicalDisk(*)\Avg. Disk sec/Read"
$counterPath +="\LogicalDisk(*)\Avg. Disk sec/Write"
$counterPath +="\LogicalDisk(*)\Disk Reads/sec"
$counterPath +="\LogicalDisk(*)\Disk Writes/sec"
$counterPath +="\LogicalDisk(*)\Disk Bytes/sec"
$counterPath +="\Network Interface(*)\Output Queue Length"
$counterPath +="\Process(Idle)\% Processor Time"
$counterPath +="\Process(_Total)\% Processor Time"
$counterPath +="\Process(Idle)\% Privileged Time"
$counterPath +="\Process(_Total)\% Privileged Time"
$counterPath +="\System\Context Switches/sec"


#################
#Bug fixed : how get the process information of my current instance
# CPU Time by process
$instancePID =  get-wmiobject -class win32_service | where-object {($_.Name -like $SQLServiceName) } | SELECT ProcessID
$processName = Get-WmiObject Win32_PerfFormattedData_PerfProc_Process |  where-object{ $_.IDProcess -eq $instancePID.ProcessID} | SELECT name
$pn = $processName.Name
$counterPath +="\Process($pn)\% Processor Time"


# Collect all counters from my list $counterPath
$collectCounters=(Get-Counter  -ComputerName $ServerName -Counter $counterPath  | ForEach-Object { $_.CounterSamples});


# Fetch the result to put in database
ForEach($c in $collectCounters  | SELECT   Path,InstanceName,CookedValue) 
{
$Path =  $c.Path;
$InstanceNamePath =  $c.InstanceName;
$CookedValue =  $c.CookedValue;
$PathTab=$Path.split("\");
$ObjectName=$PathTab[4].replace("mssql`$"+$InstanceName.tolower()+":","");
$CounterName=$PathTab[5];

$verbosLog=(Invoke-Sqlcmd -ServerInstance $FullInstanceName -Database SQLMonitor -Query "INSERT INTO [dbo].[SQL_DISCOVER_PERFORMANCE_COUNTERS] SELECT getdate(), '$InstanceName', '$Path', '$CookedValue', '$InstanceNamePath', '$ObjectName', '$CounterName'");

#Verbos log
echo $verbosLog #| out-file $logFile -encoding "default" -append
}


}



function usage {
"Exemple : getPerfmon.ps1 -ServerName HOSTNAME -InstanceName INSTANCENAME"
exit 1
}




##### MAIN


if(!$ServerName){usage}
if(!$InstanceName){usage}

Test_PSSnapin

fn_imp-perfmon

Connection à SYBASE via PowerShell

Comment se connecter à sybase avec powershell ?

Pré-requis

  • Powershell version 2
  • Net framework version 2 minimum

 

Installation

  • Copier en local ADONET64 provenant du client sybase 15.5
  • Rajouter dans le path le répertoire ADONET64/dll
  • Aller dans le répertoire ADONET64/dll et taper la commande suivante qui installera le driver sybase ADO.NET :

AseGacUtility.exe -i Sybase.AdoNet2.AseClient.dll policy.1.15.Sybase.AdoNet2.AseClient.dll policy.2.155.Sybase.AdoNet2.AseClient.dll

 

Exemple de script

# Import the Sybase dll

Add-Type -AssemblyName "Sybase.AdoNet2.AseClient, Version=2.155.1015.0, Culture=neutral, PublicKeyToken=xxxxxxxxxx"

 

# Open a connection to MySybServer,6100

$SybaseConn = New-Object Sybase.Data.AseClient.AseConnection

$SybaseConn.ConnectionString = "Data Source='machine';Port=9999;Database='master';UID='luc_riou';PWD=’*******’;"

$SybaseConn.Open()

 

# Create SybaseCommand object, define command text, and set the connection

$cmd = New-Object Sybase.Data.AseClient.AseCommand

$cmd.CommandText = $query

 

# Create SybaseDataAdapter object and set the command

$da = New-Object Sybase.Data.AseClient.AseDataAdapter

$da.SelectCommand = "SELECT * from sysobjects"

$da.SelectCommand.Connection = $SybaseConn

 

# Create and fill DataTable object

$dt = New-Object System.Data.DataTable

$da.Fill($dt) | Out-Null

 

# Close the Sybase connection

$SybaseConn.Close()

 

# Use the datatable

foreach ($dr in $dt.Rows)

{

Write-Host $dr["name"]

}

 

Pour vérifier que l'assembly SYBASE s'est bien installée, il suffit de lister les assemblies du répertoire C:\Windows\assembly

 

 

Gestion des Shares avec Powershell

L'article présente quelques exemples de gestion des shares sous windows.

 

Création d'un Share :

$SharePath="Path de mon Share"
$ShareName="Nom de Mon Share"
$Type = 0
$objWMI = [wmiClass] 'Win32_share'
$objWMI.create($FolderPath, $ShareName, $Type)

 

 

 

 

Gestion d'un Cluster via Powershell

Pour commencer, il faut importer les modules relatif au cluster windows :
Import-Module FailoverClusters

 

On peut ensuite lister les commandes disponibles :
Get-Command -Module FailoverClusters

 

Pour rappel, pour avoir la description d'une commande, il suffit de taper get-help devant :
PS P:\> get-help Get-ClusterGroup

NAME
    Get-ClusterGroup

SYNOPSIS
    Get information about one or more clustered services or applications (resource groups) in a failover cluster.


SYNTAX
    Get-ClusterGroup [-InputObject <psobject>] [[-Name] <StringCollection>] [-Cluster <string>] [<CommonParameters>]


DESCRIPTION
    A resource group (a clustered service or application) is the unit of failover. During failover, all resources in th
    e resource group move together.


RELATED LINKS
    Online version: http://go.microsoft.com/fwlink/?LinkId=143785
    Add-ClusterGroup
    Move-ClusterGroup
    Remove-ClusterGroup
    Start-ClusterGroup
    Stop-ClusterGroup

REMARKS
    To see the examples, type: "get-help Get-ClusterGroup -examples".
    For more information, type: "get-help Get-ClusterGroup -detailed".
    For technical information, type: "get-help Get-ClusterGroup -full".

 

Quelques commandes pratiques :

get-cluster : retourne le nom de votre cluster

Get-ClusterNode : retourne l'état des noeuds

 

Informations globales sur le cluster :

PS P:\> Get-ClusterResource "Cluster Name" | Format-List *


Cluster                : DPRRADPRDCLUS01
IsCoreResource         : True
IsNetworkClassResource : False
IsStorageClassResource : False
OwnerNode              : srvparradp22
ResourceType           : Network Name
State                  : Online
OwnerGroup             : Cluster Group
Name                   : Cluster Name
MaintenanceMode        : False
MonitorProcessId       : 6536
Description            :
SeparateMonitor        : False
PersistentState        : 1
LooksAlivePollInterval : 4294967295
IsAlivePollInterval    : 4294967295
RestartAction          : 2
RestartThreshold       : 1
RestartDelay           : 500
RestartPeriod          : 900000
RetryPeriodOnFailure   : 3600000
PendingTimeout         : 180000
DeadlockTimeout        : 300000
ResourceSpecificStatus :
Id                     : 9584290f-7ad9-412d-9006-c6cfdbd37f39

 

Détailler les interfaces réseau :
Get-ClusterNetwork PUBLIC | Format-List *Cluster           : NOM_DU_CLUSTER
State             : Up
Name              : PUBLIC
Ipv6Addresses     : {}
Ipv6PrefixLengths : {}
Ipv4Addresses     : {xxx.xx.x.x}
Ipv4PrefixLengths : {21}
Address           : xxx.xx.x.x
AddressMask       : 255.255.248.0
Description       :
Role              : 3
AutoMetric        : True
Metric            : 10000
Id                : xxxxxxxxx

 

Pour info, le parametre 'Metric' permet d'identifier les réseaux privés et public. En effet, les valeurs '1000 et plus' correspondent aux réseaux privés et '10000 et plus' aux réseaux 'public'.

Par exemple, on peut récupérer le nom de réseau public en lançant la commande :

(Get-ClusterNetwork | where-object {$_.Metric -eq "10000"}).Name

 

Pour plus d'information, cliquer sur le lien (http://technet.microsoft.com/fr-fr/library/ff182335%28v=ws.10%29.aspx)

 

Informations sur les disques cluster :

Import-Module FailoverClusters

$objs = @()

$csvs = Get-ClusterSharedVolume
foreach ( $csv in $csvs )
{
   $csvinfos = $csv | select -Property Name -ExpandProperty SharedVolumeInfo
   foreach ( $csvinfo in $csvinfos )
   {
      $obj = New-Object PSObject -Property @{
         Name        = $csv.Name
         Path        = $csvinfo.FriendlyVolumeName
         Size        = $csvinfo.Partition.Size
         FreeSpace   = $csvinfo.Partition.FreeSpace
         UsedSpace   = $csvinfo.Partition.UsedSpace
         PercentFree = $csvinfo.Partition.PercentFree
      }
      $objs += $obj
   }
}

$objs | ft -auto Name,Path,@{ Label = "Size(GB)" ; Expression = { "{0:N2}" -f ($_.Size/1024/1024/1024) } },@{ Label = "FreeSpace(GB)" ; Expression = { "{0:N2}" -f ($_.FreeSpace/1024/1024/1024) } },@{ Label = "UsedSpace(GB)" ; Expression = { "{0:N2}" -f ($_.UsedSpace/1024/1024/1024) } },@{ Label = "PercentFree" ; Expression = { "{0:N2}" -f ($_.PercentFree

 

 

 

Articles tagged

Utilisation de psexec (pstools) sous powershell

Les tools contenus dans les package pstools sont super pratique pour lancer des traitements à distance.

Url de téléchargement : http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

 

Quelques exemples pratiques :

-- Lancement d'un .bat a distance :

D:\PsTools\psexec \\REMOTEHOST c:\admindba\scripts\monscript.bat

-- Lancement d'une commande powershell à distance :

D:\PsTools\psexec \\REMOTEHOST cmd /c 'echo .|powershell -Command "& {$host.version}"' | Out-File $_TmpFile -append

-- Lancement d'un script powershell à distance :

D:\PsTools\psexec \\REMOTEHOST cmd /c "echo .|powershell -File C:\monscript.ps1" | Out-File $_TmpFile -append

 -- Mise a jour du LockMemory sur un compte à distance

D:\PsTools\psexec \\REMOTEHOST \\sharenetwork\install\Tools\ntrights.exe -u DOMAIN\USERNAME +r SeLockMemoryPrivilege

 

 

Débuter sous Powershell

Cet article décrit quelques fonctionnalités utiles sur le powershell pour l'administration des bases de données.

Les fonctions :

Write-Host  :

  • Permet d'afficher du texte (ou des objets) à l'écran.
  • Des options existent pour changer la couleur d'affichage (foregroundcolor, backgroundcolor...)
  • Ex :  Write-Host ("Server '{0}' not found" -f $MONSERVER) -foregroundcolor red -backgroundcolor yellow

Write-Output

  • Permet d'envoyer du texte ou des objets au pipeline

 

La commande 'function' :

Gestion des paramètres :

function f([string]$param1, [string]$param2)
{
     Write-Host "a:", $param1, " b:", $param2}


f("C est", "une blague")

# Results in: param1: c est une blague param2:

f "C est"  "une blague"

# Results in param1: c 'est param2: une blague

break;

 

 

 

 

 

 

Gestion des dates sous powershell

La commande (ou applet) pour avoir la date instantanée sous powershell est Get-Date (Doc officielle)


Aide et propriétés de l'applet :

Propriétés :       PS C:\> Get-Date | Get-Member

Aide :               PS C:\> Get-Help Get-Date -full

 

 

Quelques exemples d'utilisation :

PS C:\> Get-Date -format 'd MMM yyyy'
21 May 2012

PS C:\> Get-Date -format 'yyyy/MM/dd HH:mm:ss'
2012/05/21 13:58:07

 

 

Opérations sur les dates :

PS C:\> 'Difference de ' + (([system.datetime]"20 August 2012").DayOfYear - (Get-date).DayOfYear) + ' jours entre ces 2 dates'
Difference de 91 jours entre ces 2 dates

 

Quelques commandes pour ajouter / réduire une date de n jours

PS C:\> get-date
Monday, May 21, 2012 2:31:09 PM
PS C:\> (get-date).AddDays(+1)
Tuesday, May 22, 2012 2:31:11 PM
PS C:\> (get-date).AddDays(-7)
Monday, May 14, 2012 2:31:17 PM

 

PS C:\> (get-date).DayOfWeek
Monday
PS C:\> (get-date).DayOfYear
142

 

PS C:\> (get-date).TimeOfDay
Days              : 0
Hours             : 14
Minutes           : 37
Seconds           : 57
Milliseconds      : 223
Ticks             : 526772232510
TotalDays         : 0.609690083923611
TotalHours        : 14.6325620141667
TotalMinutes      : 877.95372085
TotalSeconds      : 52677.223251
TotalMilliseconds : 52677223.251

 

Affichage / restriction d'une date :

PS C:\> (get-date "02/01/2012").ToShortDateString()
2/1/2012
PS C:\> (get-date "01 January 2012").ToShortDateString()
1/1/2012

 

Manipulation des dates :

PS C:\> $Now = Get-Date -Uformat "Nous sommes aujourd'hui %A, le %d/%m/%Y"
PS C:\> $Now
Nous sommes aujourd'hui Monday, le 21/05/2012

 

Tableau récapitulatif :

d

Day of month 1-31

dd

Day of month 01-31

ddd

Day of month as abbreviated weekday name

dddd

Weekday name

h

Hour from 1-12

H

Hour from 1-24

hh

Hour from 01-12

HH

Hour from 01-24

m

Minute from 0-59

mm

Minute from 00-59

M

Month from 1-12

MM

Month from 01-12

MMM

Abbreviated Month Name

MMMM

Month name

s

Seconds from 1-60

ss

Seconds from 01-60

t

A or P (for AM or PM)

tt

AM or PM

yy

Year as 2-digit

yyyy

Year as 4-digit

z

Timezone as one digit

zz

Timezone as 2-digit

zzz

Timezone

 

Commandes pratiques sous Powershell

 Les sites powershell :

 

Gestion d'un profile

$profile.CurrentUserAllHosts : permet d'avoir l'emplacement du fichier profile.ps1

$Profile.AllUsersAllHosts : retourne l'emplacement système par défaut de profile.ps1

 

Télécharger :

  • Télécharger un fichier via une URL (comme wget/curl) : (New-Object System.Net.WebClient).DownloadFile($URL,$File)
  • Télécharger une chaine via une URL (comme wget/curl) : (New-Object System.Net.WebClient).DownloadString($URL)

 

Affiche le service et compte de service d'un instance SQLServer :

get-wmiobject win32_service -filter "(name Like 'MSSQL$%')" | Select name,Startname

 

Comparaison des fichiers :

Fichier autoload.csv contient :

id
9231
9232
9230
9222

Fichier autoload_old.csv contient :

id
9268
9231
9232
9230
9222

Fonction de comparaison

    • compare-object .\autoload.csv .\autoload_old.csv

Résultat :

InputObject                                                                                         SideIndicator
-----------                                                                                         -------------
9268                                                                                                =>

    • diff .\autoload.csv .\autoload_old.csv (Idem au dessus)
    • compare-object (get-content .\autoload.csv) (get-content .\autoload_old.csv) (compare le contenu)
    • Comparaison avec csv (pratique pour travailler avec des données de tables par exemple)

Exemple :

$file1=import-csv -Path ".\autoload.csv"
$file2=import-csv -Path ".\autoload_old.csv"

Résultat de 'compare-object $file1 $file2 -property Id' :

Id                                                                                                  SideIndicato
--                                                                                                  ------------
9268                                                                                                =>

 

Lister les groupes AD d'un login windows

Add-Type -AssemblyName System.DirectoryServices.AccountManagement
$username = read-host -prompt "Enter a username"
$ct = [System.DirectoryServices.AccountManagement.ContextType]::Domain
$user = [System.DirectoryServices.AccountManagement.UserPrincipal]::FindByIdentity($ct, $username)
$groups = $user.GetGroups()
foreach($i in $groups){
  $i.SamAccountName
}

 

 

Remplacer et/ou supprimer les '0' d'une variable

PS P:\> '010.012.000.101' -replace '\b0+\B'
10.12.0.101

 

 

Lister les permissions sur un répertoire

$OutFile = "C:\temp\Permissions.csv"
$Header = "Folder Path,IdentityReference,AccessControlType,IsInherited,InheritanceFlags,PropagationFlags"
Del $OutFile
Add-Content -Value $Header -Path $OutFile

$RootPath = "monrepertoire"

$Folders = dir $RootPath -recurse | where {$_.psiscontainer -eq $true}

foreach ($Folder in $Folders){
    $ACLs = get-acl $Folder.fullname | ForEach-Object { $_.Access  }
    Foreach ($ACL in $ACLs){
        $OutInfo = $Folder.Fullname + "," + $ACL.IdentityReference  + "," + $ACL.AccessControlType + "," + $ACL.IsInherited + "," + $ACL.InheritanceFlags + "," + $ACL.PropagationFlags
        Add-Content -Value $OutInfo -Path $OutFile
    }
}

 

 

Gestion des comptes locaux

$adsi = [ADSI]"WinNT://$env:COMPUTERNAME"

# Listes les comptes locaux
$adsi.Children | where {$_.SchemaClassName -eq 'user'}


# Listes les groupes des comptes locaux
$adsi.Children | where {$_.SchemaClassName -eq 'user'} | Foreach-Object { $groups = $_.Groups() | Foreach-Object {$_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)} $_ | Select-Object @{n='UserName';e={$_.Name}},@{n='Groups';e={$groups -join ';'}} }

 

 

Gestion des Share Drives

Get-WmiObject -Class Win32_Share -ComputerName hostname

 

Visualisation de l'espace disque (drive et point de montage)

gwmi WIN32_Volume | select Caption, FreeSpace, Capacity

 

Gestion d'erreur avec la variable $error

$error |

  # exclude errors that are no longer in the history list:
  Where-Object { $_.InvocationInfo.HistoryID -gt 0 } |
  # combine history and error information:
  ForEach-Object {
    # get history item for current error:
    $command = Get-History $_.InvocationInfo.HistoryID
    # calculate command execution time:
    $duration = ($command.EndExecutionTime - $command.StartExecutionTime).TotalSeconds
    # get original error message:    
$errormessage = $_.Exception.Message

    # add information to history item:

    $command | Add-Member -MemberType NoteProperty -Name Error -Value $errormessage
    $command | Add-Member -MemberType NoteProperty -Name Duration -Value $duration

    # select properties to output:
    $result = $command | Select-Object -Property ID, Duration, Error, CommandLine, StartExecutionTime
    $result
} |
  # list commands with highest duration first:
  Sort-Object -Property Duration -Descending |
  Out-GridView

 

 

 Barre de progression en pourcentage

for ($i = 1; $i -lt 101; $i++ ){for ($j=0;$j -lt 10000;$j++) {} write-progress -activity "Search in Progress" -status "% Complete:" -percentcomplete $i;}

 

 Traitement de texte / variable sur des fichiers et répertoire

dir C:\batches\logs -I *20140403_22*.log -R | Select-String fail -list | % { notepad $_.path }

permet d'ouvrir les fichiers contenant le mot 'fail' avec notepad.

 

 Exemple de try catch

try 
{
    $current = $ErrorActionPreference 
    $ErrorActionPreference = 'Stop' 
    unprogramme.exe 2>&1 
    $ErrorActionPreference = $current 
}
catch 
{
   Write-Host ('Error occured: ' + $_.Exception.Message)
} 

Le paramètre ErrorActionPreference permet de gérer le comportement de la tache en cas d'erreur (stop, continue, silentcontinue...).

"2>&1" redirige la sortie d'erreur vers la sortie standard.

 

 

 

 

Comment interroger une base de registre à distance ?

Bien que l'info existe un peu partout sur le net, j'ai fait cet article pour une application SQLServer

En effet, a partir d'une liste de serveurs Windows, il fallait que je trouve si des instances SQLServer étaient installées dessus et donner la version

Je pense qu'il y a certainement plus simple ; ))

 

J'ai utilisé les commandes suivantes 'OpenSubKey', 'GetValueNames' et 'GetValue'

 

Voici un petit exemple de script réalisé sous powershell

$servers = 'host04','host05'

foreach ($server in $servers){
$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $server );
$regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server");
    foreach ($Instancename in $regkey.GetValue('InstalledInstances')){
        if($Instancename -eq "MSSQLSERVER"){
            $regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL");
            $Instancename=$regkey.GetValue($Instancename)
        }
        $regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\" + $InstanceName + "\\MSSQLServer\\CurrentVersion");
        $server + " - " + $Instancename + " - " + $regkey.GetValue('CurrentVersion');
    }
}

 

Voici le résultat :

host04 - SQLSERVER04 - 10.0.4000.0
host04 - SQLSERVERTEST - 10.0.4000.0
host05 - SQLSERVER22 - 10.0.5500.0
host05 - SQLSERVERDEV02 - 10.0.5500.0