script – Blog d'Antoine Delrue

[UPDATE] PowerShell – exécution à distance avec WMI

21 janvier 201921 janvier 2019 par Antoine Delrue

Hello, Aujourd’hui j’en profite pour mettre à jour le post ‘PowerShell – exécution à distance avec WMI‘. Voici le nouveau script, avec un peu plus de logging, et plus généralisé que la première version.

function Execute-ProgramRemotely
{
    <#
        .SYNOPSIS
        This function will execute a program on a remote computer
        .DESCRIPTION
        This function uses WMI to create a process on a remote computer to run a program
        Modify the $tempPath and $installString variable to suit your needs
        .PARAMETER computername
        The computer name to query. Just one.
        .PARAMETER source
        The path of the folder containing all required executable files and dependencies that will be copied to the target computer before execution
        .PARAMETER command
        The executable or command to execute on the target computer, once the source files are copied
         .EXAMPLE
        Execute-ProgramRemotely -Computername 'mycomputer01' -source 'd:\sources\program\7zip' -command 'setup.exe /S'
    #>
 
    param(
    [Parameter(Mandatory=$true,Position=1)]
    [string]$computername,
    [Parameter(Mandatory=$true,Position=2)]
    [string]$source,
    [Parameter(Mandatory=$true,Position=3)]
    [string]$command) #end param

    # Check if the computer is up and running
    try {
        Test-Connection $computername -ErrorAction Stop | Out-Null
 
        # Define temp folder path
        $tempPath = "\\" + $computername + "\c$\temp\Execute-Program" 
               
        # Copy the setup file to the target computer
        Copy-item $source $tempPath -Recurse -Force -ErrorAction Stop
        Write-Verbose -Message "Copied $source in $tempPath" -Verbose
 
        # Define the installString then start the process on the remote computer
        #Start the process on the remote machine
        $installString = "$tempPath\$command"
        Write-Verbose -Message "Install string : $installString" -Verbose
        $process = Invoke-WMIMethod win32_process -Name create -ComputerName $computername -ArgumentList $installString
         
        #Get the process ID for the newly created process
        $id = $process.processid
        Write-Verbose -Message "Created process $process with $id" -Verbose

        #Create a DO WHILE loop to monitor for process exit
        $a = ""
 
        do {
            $a = Get-Process -ComputerName $computername | Where{$_.ID -match "$ID"}
            Start-Sleep -Seconds 2
            Write-Verbose -Message "Process $id still ongoing" -Verbose
        } 
        While ($a -ne $null)
        
        Write-Verbose -Message "Process $id finished, now cleaning temp files" -Verbose

        #Clean temp folder
        Remove-Item -Path $tempPath -Recurse -Force
        Write-Verbose -Message "Deleted $tempPath" -Verbose
    }
    catch {
        Write-Warning -Message "$($_.Exception.Message)"
        BREAK
    }
}

function Execute-ProgramRemotely

{

.SYNOPSIS

This function will execute a program on a remote computer

.DESCRIPTION

This function uses WMI to create a process on a remote computer to run a program

Modify the $tempPath and $installString variable to suit your needs

.PARAMETER computername

The computer name to query. Just one.

.PARAMETER source

The path of the folder containing all required executable files and dependencies that will be copied to the target computer before execution

.PARAMETER command

The executable or command to execute on the target computer, once the source files are copied

.EXAMPLE

Execute-ProgramRemotely -Computername 'mycomputer01' -source 'd:\sources\program\7zip' -command 'setup.exe /S'

param(

[Parameter(Mandatory=$true,Position=1)]

[string]$computername,

[Parameter(Mandatory=$true,Position=2)]

[string]$source,

[Parameter(Mandatory=$true,Position=3)]

[string]$command) #end param

# Check if the computer is up and running

try {

Test-Connection $computername -ErrorAction Stop | Out-Null

# Define temp folder path

$tempPath = "\\" + $computername + "\c$\temp\Execute-Program"

# Copy the setup file to the target computer

Copy-item $source $tempPath -Recurse -Force -ErrorAction Stop

Write-Verbose -Message "Copied $source in $tempPath" -Verbose

# Define the installString then start the process on the remote computer

#Start the process on the remote machine

$installString = "$tempPath\$command"

Write-Verbose -Message "Install string : $installString" -Verbose

$process = Invoke-WMIMethod win32_process -Name create -ComputerName $computername -ArgumentList $installString

#Get the process ID for the newly created process

$id = $process.processid

Write-Verbose -Message "Created process $process with $id" -Verbose

#Create a DO WHILE loop to monitor for process exit

$a = ""

do {

$a = Get-Process -ComputerName $computername | Where{$_.ID -match "$ID"}

Start-Sleep -Seconds 2

Write-Verbose -Message "Process $id still ongoing" -Verbose

}

While ($a -ne $null)

Write-Verbose -Message "Process $id finished, now cleaning temp files" -Verbose

#Clean temp folder

Remove-Item -Path $tempPath -Recurse -Force

Write-Verbose -Message "Deleted $tempPath" -Verbose

}

catch {

Write-Warning -Message "$($_.Exception.Message)"

BREAK

}

Hth ! 🙂

[Version 3.0] Powershell – ADK & MDT installation silencieuse automatique

29 novembre 201828 novembre 2018 par Antoine Delrue

Bonjour, Encore une petite mise à jour de ce script d’installation et configuration automatique de MDT et ADK, afin de prendre en charge les dernières version de Windows 10 (1809). Ce script ne tourne que sur Windows Server 2012 ou ultérieur, et ne fonctionnera pas entièrement si le rôle ADDS est installé. Voici la liste ordonnée … Lire la suite[Version 3.0] Powershell – ADK & MDT installation silencieuse automatique

Powershell – Storage Space Direct (S2D) rapport de l’utilisation des volumes

18 juillet 201818 juillet 2018 par Antoine Delrue

Bonjour, Voici un autre script pour S2D, permettant cette fois-ci d’avoir une vue complète de l’utilisation des Volumes :

$credential = Get-Credential -Prompt "Please enter your administrative credentials"

$scriptBlock = {

    Function ConvertTo-PrettyCapacity {

        Param (
            [Parameter(
                Mandatory=$True, 
                ValueFromPipeline=$True
                )
            ]
        [Int64]$Bytes,
        [Int64]$RoundTo = 0 # Default
        )

        If ($Bytes -Gt 0) {
            $Base = 1024 # To Match PowerShell
            $Labels = ("bytes", "KB", "MB", "GB", "TB", "PB", "EB", "ZB", "YB") # Blame Snover
            $Order = [Math]::Floor( [Math]::Log($Bytes, $Base) )
            $Rounded = [Math]::Round($Bytes/( [Math]::Pow($Base, $Order) ), $RoundTo)
            [String]($Rounded) + $Labels[$Order]
        }
        Else {
            0
        }
        Return
    }

    Function ConvertTo-PrettyPercentage {

        Param (
            [Parameter(Mandatory=$True)]
                [Int64]$Numerator,
            [Parameter(Mandatory=$True)]
                [Int64]$Denominator,
            [Int64]$RoundTo = 0 # Default
        )

        If ($Denominator -Ne 0) { # Cannot Divide by Zero
            $Fraction = $Numerator/$Denominator
            $Percentage = $Fraction * 100
            $Rounded = [Math]::Round($Percentage, $RoundTo)
            [String]($Rounded) + "%"
        }
        Else {
            0
        }
        Return
    }

    ### SCRIPT... ###

    $Output = @()

    # Query Cluster Shared Volumes
    $Volumes = Get-StorageSubSystem Cluster* | Get-Volume | ? FileSystem -Eq "CSVFS"

    ForEach ($Volume in $Volumes) {

        # Get MSFT_Volume Properties
        $Label = $Volume.FileSystemLabel
        $Capacity = $Volume.Size | ConvertTo-PrettyCapacity
        $Used = ConvertTo-PrettyPercentage ($Volume.Size - $Volume.SizeRemaining) $Volume.Size

        If ($Volume.FileSystemType -Like "*ReFS") {
            $Filesystem = "ReFS"
        }
        ElseIf ($Volume.FileSystemType -Like "*NTFS") {
            $Filesystem = "NTFS"
        }

        # Follow Associations
        $Partition   = $Volume    | Get-Partition
        $Disk        = $Partition | Get-Disk
        $VirtualDisk = $Disk      | Get-VirtualDisk

        # Get MSFT_VirtualDisk Properties
        $Footprint = $VirtualDisk.FootprintOnPool | ConvertTo-PrettyCapacity
        $Efficiency = ConvertTo-PrettyPercentage $VirtualDisk.Size $VirtualDisk.FootprintOnPool

        # Follow Associations
        $Tiers = $VirtualDisk | Get-StorageTier

        # Get MSFT_VirtualDisk or MSFT_StorageTier Properties...

        If ($Tiers.Length -Lt 2) {

            If ($Tiers.Length -Eq 0) {
                $ReadFrom = $VirtualDisk # No Tiers
            }
            Else {
                $ReadFrom = $Tiers[0] # First/Only Tier
            }

            If ($ReadFrom.ResiliencySettingName -Eq "Mirror") {
                # Mirror
                If ($ReadFrom.PhysicalDiskRedundancy -Eq 1) { $Resiliency = "2-Way Mirror" }
                If ($ReadFrom.PhysicalDiskRedundancy -Eq 2) { $Resiliency = "3-Way Mirror" }
                $SizeMirror = $ReadFrom.Size | ConvertTo-PrettyCapacity
                $SizeParity = [string](0)
            }
            ElseIf ($ReadFrom.ResiliencySettingName -Eq "Parity") {
                # Parity
                If ($ReadFrom.PhysicalDiskRedundancy -Eq 1) { $Resiliency = "Single Parity" }
                If ($ReadFrom.PhysicalDiskRedundancy -Eq 2) { $Resiliency = "Dual Parity" }
                $SizeParity = $ReadFrom.Size | ConvertTo-PrettyCapacity
                $SizeMirror = [string](0)
            }
            Else {
                Write-Host -ForegroundColor Red "What have you done?!"
            }
        }

        ElseIf ($Tiers.Length -Eq 2) { # Two Tiers

            # Mixed / Multi- / Hybrid
            $Resiliency = "Mix"

            ForEach ($Tier in $Tiers) {
                If ($Tier.ResiliencySettingName -Eq "Mirror") {
                    # Mirror Tier
                    $SizeMirror = $Tier.Size | ConvertTo-PrettyCapacity
                    If ($Tier.PhysicalDiskRedundancy -Eq 1) { $Resiliency += " (2-Way" }
                    If ($Tier.PhysicalDiskRedundancy -Eq 2) { $Resiliency += " (3-Way" }
                }
            }
            ForEach ($Tier in $Tiers) {
                If ($Tier.ResiliencySettingName -Eq "Parity") {
                    # Parity Tier
                    $SizeParity = $Tier.Size | ConvertTo-PrettyCapacity
                    If ($Tier.PhysicalDiskRedundancy -Eq 1) { $Resiliency += " + Single)" }
                    If ($Tier.PhysicalDiskRedundancy -Eq 2) { $Resiliency += " + Dual)" }
                }
            }
        }

        Else {
            Write-Host -ForegroundColor Red "What have you done?!"
        }

        # Pack

        $Output += [PSCustomObject]@{
            "Volume" = $Label
            "Filesystem" = $Filesystem
            "Capacity" = $Capacity
            "Used" = $Used
            "Resiliency" = $Resiliency
            "Size (Mirror)" = $SizeMirror
            "Size (Parity)" = $SizeParity
            "Footprint" = $Footprint
            "Efficiency" = $Efficiency
        }
    }

    $Output | Sort Efficiency, Volume | FT

    [string]$body = $Output | Sort Efficiency, Volume | ConvertTo-Html -Head "<style>table {border-collapse: collapse;padding:5px;}table, th, td {border: 1px solid black;}th{white-space: nowrap;background-color:lightgrey;}</style>" -PreContent "<p>Dear,<br /><br />Please find bellow the list of all S2D volumes and their current usage on the S2D-infra1 cluster :</p>" -PostContent '<p><br />Regards,<br /><br />Your IT Server Team</p>'

    Send-MailMessage -Body $body -BodyAsHtml -From "no-reply@your-domain.com" -To "your_mail@your-domain.com" -Subject "[S2D] Volumes usage on S2D-infra1" -Priority High -SmtpServer "your-mail-server@your-domain.com"
}

Invoke-Command -ScriptBlock $scriptBlock -ComputerName "your-s2d-cluster" -Credential $credential

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

$credential = Get-Credential -Prompt "Please enter your administrative credentials"

$scriptBlock = {

Function ConvertTo-PrettyCapacity {

Param (

[Parameter(

Mandatory=$True,

ValueFromPipeline=$True

)

]

[Int64]$Bytes,

[Int64]$RoundTo = 0 # Default

)

If ($Bytes -Gt 0) {

$Base = 1024 # To Match PowerShell

$Labels = ("bytes", "KB", "MB", "GB", "TB", "PB", "EB", "ZB", "YB") # Blame Snover

$Order = [Math]::Floor( [Math]::Log($Bytes, $Base) )

$Rounded = [Math]::Round($Bytes/( [Math]::Pow($Base, $Order) ), $RoundTo)

[String]($Rounded) + $Labels[$Order]

}

Else {

}

Return

}

Function ConvertTo-PrettyPercentage {

Param (

[Parameter(Mandatory=$True)]

[Int64]$Numerator,

[Parameter(Mandatory=$True)]

[Int64]$Denominator,

[Int64]$RoundTo = 0 # Default

)

If ($Denominator -Ne 0) { # Cannot Divide by Zero

$Fraction = $Numerator/$Denominator

$Percentage = $Fraction * 100

$Rounded = [Math]::Round($Percentage, $RoundTo)

[String]($Rounded) + "%"

}

Else {

}

Return

}

### SCRIPT... ###

$Output = @()

# Query Cluster Shared Volumes

$Volumes = Get-StorageSubSystem Cluster* | Get-Volume | ? FileSystem -Eq "CSVFS"

ForEach ($Volume in $Volumes) {

# Get MSFT_Volume Properties

$Label = $Volume.FileSystemLabel

$Capacity = $Volume.Size | ConvertTo-PrettyCapacity

$Used = ConvertTo-PrettyPercentage ($Volume.Size - $Volume.SizeRemaining) $Volume.Size

If ($Volume.FileSystemType -Like "*ReFS") {

$Filesystem = "ReFS"

}

ElseIf ($Volume.FileSystemType -Like "*NTFS") {

$Filesystem = "NTFS"

}

# Follow Associations

$Partition = $Volume | Get-Partition

$Disk = $Partition | Get-Disk

$VirtualDisk = $Disk | Get-VirtualDisk

# Get MSFT_VirtualDisk Properties

$Footprint = $VirtualDisk.FootprintOnPool | ConvertTo-PrettyCapacity

$Efficiency = ConvertTo-PrettyPercentage $VirtualDisk.Size $VirtualDisk.FootprintOnPool

# Follow Associations

$Tiers = $VirtualDisk | Get-StorageTier

# Get MSFT_VirtualDisk or MSFT_StorageTier Properties...

If ($Tiers.Length -Lt 2) {

If ($Tiers.Length -Eq 0) {

$ReadFrom = $VirtualDisk # No Tiers

}

Else {

$ReadFrom = $Tiers[0] # First/Only Tier

}

If ($ReadFrom.ResiliencySettingName -Eq "Mirror") {

# Mirror

If ($ReadFrom.PhysicalDiskRedundancy -Eq 1) { $Resiliency = "2-Way Mirror" }

If ($ReadFrom.PhysicalDiskRedundancy -Eq 2) { $Resiliency = "3-Way Mirror" }

$SizeMirror = $ReadFrom.Size | ConvertTo-PrettyCapacity

$SizeParity = [string](0)

}

ElseIf ($ReadFrom.ResiliencySettingName -Eq "Parity") {

# Parity

If ($ReadFrom.PhysicalDiskRedundancy -Eq 1) { $Resiliency = "Single Parity" }

If ($ReadFrom.PhysicalDiskRedundancy -Eq 2) { $Resiliency = "Dual Parity" }

$SizeParity = $ReadFrom.Size | ConvertTo-PrettyCapacity

$SizeMirror = [string](0)

}

Else {

Write-Host -ForegroundColor Red "What have you done?!"

}

ElseIf ($Tiers.Length -Eq 2) { # Two Tiers

# Mixed / Multi- / Hybrid

$Resiliency = "Mix"

ForEach ($Tier in $Tiers) {

If ($Tier.ResiliencySettingName -Eq "Mirror") {

# Mirror Tier

$SizeMirror = $Tier.Size | ConvertTo-PrettyCapacity

If ($Tier.PhysicalDiskRedundancy -Eq 1) { $Resiliency += " (2-Way" }

If ($Tier.PhysicalDiskRedundancy -Eq 2) { $Resiliency += " (3-Way" }

}

ForEach ($Tier in $Tiers) {

If ($Tier.ResiliencySettingName -Eq "Parity") {

# Parity Tier

$SizeParity = $Tier.Size | ConvertTo-PrettyCapacity

If ($Tier.PhysicalDiskRedundancy -Eq 1) { $Resiliency += " + Single)" }

If ($Tier.PhysicalDiskRedundancy -Eq 2) { $Resiliency += " + Dual)" }

}

Else {

Write-Host -ForegroundColor Red "What have you done?!"

}

# Pack

$Output += [PSCustomObject]@{

"Volume" = $Label

"Filesystem" = $Filesystem

"Capacity" = $Capacity

"Used" = $Used

"Resiliency" = $Resiliency

"Size (Mirror)" = $SizeMirror

"Size (Parity)" = $SizeParity

"Footprint" = $Footprint

"Efficiency" = $Efficiency

}

$Output | Sort Efficiency, Volume | FT

[string]$body = $Output | Sort Efficiency, Volume | ConvertTo-Html -Head "<style>table {border-collapse: collapse;padding:5px;}table, th, td {border: 1px solid black;}th{white-space: nowrap;background-color:lightgrey;}</style>" -PreContent "<p>Dear,<br /><br />Please find bellow the list of all S2D volumes and their current usage on the S2D-infra1 cluster :</p>" -PostContent '<p><br />Regards,<br /><br />Your IT Server Team</p>'

Send-MailMessage -Body $body -BodyAsHtml -From "no-reply@your-domain.com" -To "your_mail@your-domain.com" -Subject "[S2D] Volumes usage on S2D-infra1" -Priority High -SmtpServer "your-mail-server@your-domain.com"

}

Invoke-Command -ScriptBlock $scriptBlock -ComputerName "your-s2d-cluster" -Credential $credential

Pour l’utiliser, n’oubliez par d’adapter les variables selon votre environnement (pour le Send-MailMessage et la cible du Invoke-Command). Le mail reçu ressemble à ceci : Hope this helps ! 🙂

Powershell : Storage Space Direct (S2D) rapport de l’état des disques physiques

16 juillet 201816 juillet 2018 par Antoine Delrue

Hello, Voici un petit script à planifier chaque jour, permettant d’obtenir un rapport de l’état des disques physiques d’un cluster Storage Space Direct (S2D) :

$credential = Get-Credential -Message "Please fill in your credentials :"

$scriptBlock = {
    $allDisks = Get-PhysicalDisk *ATA*
    $countDisk = $allDisks.count
    $failedDisks = ($allDisks | ? {$_.HealthStatus -inotlike "*Healthy*"}).count

    [string]$body = $alldisks | Select FriendlyName, SerialNumber, OperationalStatus, HealthStatus, Usage, @{Name="Size(GB)";Expression={[math]::Round([decimal]$_.Size/1gb,0)}} | Sort "Size(GB)" | ConvertTo-Html -Head "<style>table {border-collapse: collapse;padding:5px;}table, th, td {border: 1px solid black;}th{white-space: nowrap;background-color:lightgrey;}</style>" -PreContent "<p>Dear,<br /><br />Please find bellow the list of all physical disks and their current health status on the S2D cluster :</p>" -PostContent '<p><br />Regards,<br /><br />Your IT Server Team</p>'

    Send-MailMessage -Body $body -BodyAsHtml -From "no-reply@your-domain.com" -To "your-email@your-domain.com" -Subject "[S2D] $failedDisks physical disk failed over $countDisk" -Priority High -SmtpServer "mailserver.your-domain.com"
}

Invoke-Command -ScriptBlock $scriptBlock -ComputerName "s2d-cluster" -Credential $credential

$credential = Get-Credential -Message "Please fill in your credentials :"

$scriptBlock = {

$allDisks = Get-PhysicalDisk *ATA*

$countDisk = $allDisks.count

$failedDisks = ($allDisks | ? {$_.HealthStatus -inotlike "*Healthy*"}).count

[string]$body = $alldisks | Select FriendlyName, SerialNumber, OperationalStatus, HealthStatus, Usage, @{Name="Size(GB)";Expression={[math]::Round([decimal]$_.Size/1gb,0)}} | Sort "Size(GB)" | ConvertTo-Html -Head "<style>table {border-collapse: collapse;padding:5px;}table, th, td {border: 1px solid black;}th{white-space: nowrap;background-color:lightgrey;}</style>" -PreContent "<p>Dear,<br /><br />Please find bellow the list of all physical disks and their current health status on the S2D cluster :</p>" -PostContent '<p><br />Regards,<br /><br />Your IT Server Team</p>'

Send-MailMessage -Body $body -BodyAsHtml -From "no-reply@your-domain.com" -To "your-email@your-domain.com" -Subject "[S2D] $failedDisks physical disk failed over $countDisk" -Priority High -SmtpServer "mailserver.your-domain.com"

}

Invoke-Command -ScriptBlock $scriptBlock -ComputerName "s2d-cluster" -Credential $credential

N’oubliez pas de modifier les variables pour que ce script fonctionne dans votre environnement. 🙂 Le mail reçu devrait ressembler à ceci : Bonne journée !

Powershell : rapport des derniers backups MSSQL

16 juillet 2018 par Antoine Delrue

Bonjour, Voici un petit script qui permet d’afficher les DB d’une instance MSSQL, ainsi que le dernier backup effectué pour celles-ci. Il est possible de planifier l’exécution de ce script afin d’obtenir un rapport quotidien pour vérification. Adaptez simplement les variables à vos besoin, et c’est partis !

# Variables and functions
$DB_Server = "dbserver.your-domain.com"
$SMTP = "mailserver.your-domain.com"
$to = "you_email@your-domain.com"
$from = "no-reply@your-domain.com"
 
Function Invoke-SQL {
    param(
        [string] $dataSource,
        [string] $database,
        [string] $sqlCommand = $(throw "Please specify a query.")
        )
 
    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"
 
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
 
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
 
    $connection.Close()
    $dataSet.Tables
}

$SQLCommand = "SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name"

$SQLCheck = Invoke-SQL -dataSource $DB_Server -sqlCommand $SQLCommand

[string]$body = $SQLCheck.DefaultView | Select DatabaseName, LastBackUpTime | ConvertTo-Html -Head "<style>table {border-collapse: collapse;padding:5px;}table, th, td {border: 1px solid black;}th{white-space: nowrap;background-color:lightgrey;}</style>" -PreContent "<p>Dear,<br /><br />Please find bellow the list of all databases on the your_instance instance, and their last backup :</p>" -PostContent '<p><br />Regards,<br /><br />Your IT Team</p>'

Send-MailMessage -Body $body -BodyAsHtml -From $from -To $to -Subject "[SQL] Last Database Backups on MSSQLPROD" -Priority High -SmtpServer $smtp

# Variables and functions

$DB_Server = "dbserver.your-domain.com"

$SMTP = "mailserver.your-domain.com"

$to = "you_email@your-domain.com"

$from = "no-reply@your-domain.com"

Function Invoke-SQL {

param(

[string] $dataSource,

[string] $database,

[string] $sqlCommand = $(throw "Please specify a query.")

)

$connectionString = "Data Source=$dataSource; " +

"Integrated Security=SSPI; " +

"Initial Catalog=$database"

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

}

$SQLCommand = "SELECT sdb.Name AS DatabaseName,

COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime

FROM sys.sysdatabases sdb

LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

GROUP BY sdb.Name"

$SQLCheck = Invoke-SQL -dataSource $DB_Server -sqlCommand $SQLCommand

[string]$body = $SQLCheck.DefaultView | Select DatabaseName, LastBackUpTime | ConvertTo-Html -Head "<style>table {border-collapse: collapse;padding:5px;}table, th, td {border: 1px solid black;}th{white-space: nowrap;background-color:lightgrey;}</style>" -PreContent "<p>Dear,<br /><br />Please find bellow the list of all databases on the your_instance instance, and their last backup :</p>" -PostContent '<p><br />Regards,<br /><br />Your IT Team</p>'

Send-MailMessage -Body $body -BodyAsHtml -From $from -To $to -Subject "[SQL] Last Database Backups on MSSQLPROD" -Priority High -SmtpServer $smtp

Hope this helps !

[UPDATE] PowerShell – ADK & MDT installation silencieuse automatique

6 novembre 20176 novembre 2017 par Antoine Delrue

Bonjour, Petit update de mon script PowerShell ‘ADK & MDT installation silencieuse automatique‘, dont l’original se trouve ici : https://obilan.be/powershell-adk-mdt-installation L’update permet la compatibilité avec Windows Server 2016 et Windows 10 version 1709. Ce script ne tourne que sur Windows Server 2012 ou ultérieur, et ne fonctionnera pas entièrement si le rôle ADDS est installé. Voici … Lire la suite[UPDATE] PowerShell – ADK & MDT installation silencieuse automatique

Utiliser SUBINACL pour définir qui peut interagir avec un service Windows

25 avril 201725 avril 2017 par Antoine Delrue

Bonjour, Dans ce post je partage une découverte récente : SubInACL, un outil en ligne de commande édité par Microsoft. Cet outil permet, parmi d’autres fonctions, d’interagir avec les services Windows afin de définir qui (user/group) peut les commander. Il m’a été utile afin de configurer certains services applicatifs pour autoriser un groupe d’utilisateurs non-admin à … Lire la suiteUtiliser SUBINACL pour définir qui peut interagir avec un service Windows

PowerShell : minuteur (countdown/timer) pour le fun

23 septembre 20168 septembre 2016 par Antoine Delrue

Hello, Une petite fonction faisant office de minuteur, juste pour le fun (quoique ça pourrait être utile aux cuisiniers-geeks) 🙂 Il y a même le son à la fin 😀

function Start-Timer
{
    param (
        [Parameter(Mandatory=$true,Position=1)]
        [ValidateSet(“minutes”,”seconds”)] 
        [string]$type,
        [Parameter(Mandatory=$true,Position=2)]
        [int]$value,
        [string]$message
    )

    if (!$message)
    {
        $message = "Timer started and set to $value $type"
    }

    switch ($type)
    {
        seconds {
        
            $minutes = [math]::Round(($value/60),2)

            foreach ($Count in (1..$value))
            {
        
                [int32]$remaining = $value - $Count
                $remainingMinutes = [math]::Round(($remaining/60),2)
                Write-Progress -Id 1 -Activity $message -Status "Waiting for $value seconds ($minutes minutes), $remaining seconds remaining ($remainingMinutes minutes)" -PercentComplete (($Count / $value) * 100)
                sleep -Seconds 1
 
                if ($remaining -lt 5)
                {
                    [console]::beep(500,100)
                } 
            }        
        }

        minutes {
               
            $seconds = $value * 60
            foreach ($Count in (1..$seconds))
                {
        
                    [int32]$remaining = $seconds - $Count
                    $remainingMinutes = [math]::Round(($remaining/60),2)
                    Write-Progress -Id 1 -Activity $message -Status "Waiting for $seconds seconds ($value minutes), $remaining seconds remaining ($remainingMinutes minutes)" -PercentComplete (($Count / $seconds) * 100)
                    sleep -Seconds 1
 
                    if ($remaining -lt 5)
                    {
                        [console]::beep(500,100)
                    } 
                }           
        }

        default {"Please choose a type of counter : minutes or seconds"}
    }  
 
    Write-Progress -Id 1 -Activity $Message -Status "Completed" -PercentComplete 100 -Completed
    [console]::beep(500,1250)
}

function Start-Timer

{

param (

[Parameter(Mandatory=$true,Position=1)]

[ValidateSet(“minutes”,”seconds”)]

[string]$type,

[Parameter(Mandatory=$true,Position=2)]

[int]$value,

[string]$message

)

if (!$message)

{

$message = "Timer started and set to $value $type"

}

switch ($type)

{

seconds {

$minutes = [math]::Round(($value/60),2)

foreach ($Count in (1..$value))

{

[int32]$remaining = $value - $Count

$remainingMinutes = [math]::Round(($remaining/60),2)

Write-Progress -Id 1 -Activity $message -Status "Waiting for $value seconds ($minutes minutes), $remaining seconds remaining ($remainingMinutes minutes)" -PercentComplete (($Count / $value) * 100)

sleep -Seconds 1

if ($remaining -lt 5)

{

[console]::beep(500,100)

}

minutes {

$seconds = $value * 60

foreach ($Count in (1..$seconds))

{

[int32]$remaining = $seconds - $Count

$remainingMinutes = [math]::Round(($remaining/60),2)

Write-Progress -Id 1 -Activity $message -Status "Waiting for $seconds seconds ($value minutes), $remaining seconds remaining ($remainingMinutes minutes)" -PercentComplete (($Count / $seconds) * 100)

sleep -Seconds 1

if ($remaining -lt 5)

{

[console]::beep(500,100)

}

default {"Please choose a type of counter : minutes or seconds"}

}

Write-Progress -Id 1 -Activity $Message -Status "Completed" -PercentComplete 100 -Completed

[console]::beep(500,1250)

}

Pour l’essayer :

Start-Timer -type seconds -value 120 -message "Test my awsome PowerShell timer"

1	Start-Timer -type seconds -value 120 -message "Test my awsome PowerShell timer"

En image :

PowerShell – ADK & MDT installation silencieuse automatique

26 janvier 201729 août 2016 par Antoine Delrue

Bonjour, Voici un petit script créé la semaine dernière pour un besoin personnel. Ce script effectue le téléchargement, l’installation et la configuration basique de MDT (Microsoft Deployment Toolkit) et de sa dépendance ADK (Windows Assessment and Deployment Kit) pour le déploiement de Windows 10, version 1607. Ce script ne tourne que sur Windows Server … Lire la suitePowerShell – ADK & MDT installation silencieuse automatique

PowerShell – Self Elevated script (= exécution automatique en admin)

29 août 201629 août 2016 par Antoine Delrue

Bonjour, Un petit peu de code PowerShell fort intéressant, créé par Benjamin ARMSTRONG trouvé sur le MSDN. Le but de ce code est de faire en sorte que le script soit automatiquement exécuté en tant qu’administrateur. Ce code lance une nouvelle instance PowerShell en admin. Très pratique pour éviter d’avoir à lancer une console en … Lire la suitePowerShell – Self Elevated script (= exécution automatique en admin)

avril 2019
L	M	M	J	V	S	D
« Jan
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30