PI Services

Le script ci-dessous requete la base SQL de SCCM pour lister et exporter en CSV, les points de distribution SCCM

########################################################################################################
### REQUETE LA BASE SQL DE SCCM POUR OBTENIR LA LISTE DES POINTS DE DISTRIBUTION SCCM.
### EXPORT DES RESULTAT EN FICHIER CSV  #####
######################################################################################################## 

# AUTHOR: CJOURDAN

<# 

    .SYNOPSIS 
        REQUETE LA BASE SQL DE SCCM POUR OBTENIR LA LISTE DES POINTS DE DISTRIBUTION SCCM
        EXPORT DU RESULTAT EN FICHIER CSV.

    .PARAMETER  
        SQLInstance : Instance SQL
        SQLDB : Instance SQL
        SQLQuery : Requete SQL
        ExportFolder : Dossier d'export du fichier CSV
        LogFolder : Chemin du dossier où creer le log du script

 
    .EXAMPLE 
     .\SCCM_SCCM_Distribution_Points.ps1 -SQLInstance SQLSCCM\SCCM -SQLDB CM_BIM -ExportFolder C:\MyExport -LogFolder C:\MyLogs
#>


[CmdletBinding()]
param(
[Parameter(Mandatory=$true,HelpMessage="Instance SQL")]
[string]$SQLInstance,

[Parameter(Mandatory=$true,HelpMessage="Base SQL")]
[string]$SQLDB,

[Parameter(Mandatory=$false,HelpMessage="Requete SQL")] 
[string]$SQLQuery= $("/* --- ALL SCCM DISTRIBUTION POINTS --- */

Declare @UserSIDs As Varchar(25); 
Set @UserSIDs = 'Disabled'

SELECT DISTINCT                 
        dp.ServerName AS Distribution_Point 
        
from fn_rbac_SystemResourceList(@UserSIDs)  as sys 
join fn_rbac_DistributionPointInfo(@UserSIDs)  as dp 
on sys.NALPath = dp.NALPath 
where sys.RoleName = 'SMS Distribution Point'

"),


[Parameter(Mandatory=$true,HelpMessage="Dossier d'export du fichier CSV")]
[string]$ExportFolder,

[Parameter(Mandatory=$true,HelpMessage="Chemin du dossier où creer le log du script")] 
[string]$LogFolder

)


# SCRIPT NAME
$ScriptName = "SCCM_SCCM_Distribution_Points.ps1"


# LogName = ScriptName without extension
$Log = $ScriptName.Split('.')[0]


### FUNCTIONS

# Function Write-Log



function Write-Log 
{ 
    <# 
    .SYNOPSIS 
        This function creates or appends a line to a log file. 
 
    .PARAMETER  Message 
        The message parameter is the log message you'd like to record to the log file. 
 
    .EXAMPLE 
        PS C:\> Write-Log -Message 'Value1' 
        This example shows how to call the Write-Log function with named parameters. 
    #> 
    [CmdletBinding()] 
    param ( 
        [Parameter(Mandatory)] 
        [string]$Message,
        [Parameter(Mandatory)] 
        [string]$LogPath, 
        [Parameter(Mandatory)] 
        [string]$LogName
        
    ) 
     
    try 
    { 
        $DateTime = Get-Date -Format ‘MM-dd-yy HH:mm:ss’ 
        Add-Content -Value "$DateTime # $Message" -Path "$LogPath\$LogName.log" 
    } 
    catch 
    { 
        Write-Error $_.Exception.Message 
    } 
} 


Function GetSQLData {

<# 
    .SYNOPSIS 
        This function query SQL Database and get Data 
 
    .PARAMETER  
        SQLInstance: Instance SQL.
        SQLDB: Base SQL.
        SQLQuery: Requete SQL.

 
    .EXAMPLE 
        GetSQLData -SQLInstance "MyInstance" -SQLDB "MyDB" -SQLQuery "Select * from MyView"
    #> 


[CmdletBinding()] 
    Param( 
        [Parameter(Mandatory=$false)] 
        [string[]] 
        $SQLInstance,
        [Parameter(Mandatory=$false)] 
        [string[]] 
        $SQLDB,
        [Parameter(Mandatory=$false)] 
        [string[]] 
        $SQLQuery
        
        )

$connectionString = "Data Source=$SQLInstance;"+"Integrated Security=SSPI;"+"Initial Catalog=$SQLDB"

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($SQLQuery,$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

}






# EXECUTE Query ($SQLQuery)
    Write-Log -Message "Execution of GetSQLData on $SQLDB" -LogPath $LogFolder -LogName $Log
    $Result = 
    Try {
        GetSQLData -SQLInstance $SQLInstance -SQLDB $SQLDB -SQLQuery $SQLQuery
        }
    Catch
        {
        $Message = "ERROR DURING EXECUTION OF QUERY"
        Write-Host -F Red $Message
        Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
        Exit 1
        }


########################################
# SOUS-REGROUPEMENTS
########################################


# All SCCM DP
$AllSCCMDP = $Result | ConvertTo-Csv -Delimiter ';' -NoTypeInformation | foreach {$_.replace('"','')}

$AllSCCMDP

# EXPORTS TO TXT FILES
$AllSCCMDP  |  Out-File -FilePath "$ExportFolder\All_SCCMDP.txt" -Force



# DISPLAY SUCCESS
$Message = "--- EXECUTION OK ---"
Write-Host -F Green $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log

Sous ce titre, le cas de l'utilisation du Widget Powershell Grid dans la console Monitoring de SCOM pour afficher certaines alertes spécifiques.

On commence par creer une vue Dashboard

On choisit le template Grid Layout

On nomme le dashboard

On selectionne 1 cell

Cliquer Create

Cliquer "Click to add widget"

Selectionner Powershell Grid Widget

Nommer le widget, par exemple avec le meme nom que le dashboard

Copier coller le script suivant dans le champ Script

# Get SCOM Alert Related to Discovery and Monitoring Error
$Alerts = Get-SCOMAlert -Criteria "ResolutionState <> 255 AND Name matches '.*(Discovery|Monitoring) (failed|error).*'" #| select name,MonitoringObjectDisplayName,MonitoringObjectPath,LastModified,Description | ft -AutoSize

foreach ($alert in $Alerts)
{

$AlertName = $alert.name

$AlertLastModified = $alert.lastmodified

$MonObjDisplayName = $alert.MonitoringObjectDisplayName.ToString()

$MonObjPath = $alert.MonitoringObjectPath.ToString()

$AlertDesc = $alert.Description.ToString()

$dataObject = $ScriptContext.CreateInstance('xsd://foo!bar/baz')

#Create an ID used for sorting the output - Must be a string value - Used to sort the grid
$dataObject['Id'] = $MonObjDisplayName
$dataObject['name'] = $AlertName
$dataObject['MonitoringPath'] = $MonObjPath
$dataObject['LastModified'] = $AlertLastModified
$dataObject['Description'] = $AlertDesc


# Add the data object to be displayed in the dashboard
  $ScriptContext.ReturnCollection.Add($dataObject)

}

Dans ce script on recupere les alertes dont le nom correspond a des erreur de Discovery ou Monitoring.

Ne pas hesiter a faire évoluer les critères pour inclures d'autres cas propre a votre environnement.

Get-SCOMAlert -Criteria "ResolutionState <> 255 AND Name matches '.*(Discovery|Monitoring) (failed|error).*'"

Cliquer Create

Cliquer Close

La liste des alertes s'affiche

Le script ci-dessous est une version modifiée d'un script qui propose une version ligne de commande de l'outil TreeSize pour afficher les tailles récursives d'une arborescence de dossier/Fichiers.

Le script original est disponible ici: http://aka.ms/directory-treesize.ps1

- Une fonction GetSizeToCSV a été ajouté pour generer un export CSV

- Un bloc ValidateScript a été ajouté pour le paramètre Directory

<#
.SYNOPSIS
    powershell script to to enumerate directory summarizing in tree view directories over a given size

.DESCRIPTION
    
    .\directory-treesize.ps1 c:\windows\system32

    To enable script execution, you may need to Set-ExecutionPolicy Bypass -Force
         
.NOTES
    File Name  : directory-treesize.ps1
    Version    : 1.0
    History    : 
                CJOURDAN --> Add Function to collect and generate CSV Files (GetSizeToCsv)
                CJOURDAN --> Add ValidateScript for 'directory' parameter validation


.EXAMPLE
    .\directory-treesize.ps1
    enumerate current working directory

.PARAMETER depth
    number of directory levels to display

.PARAMETER detail
    display additional file / directory detail
    output: path, total size of files in path, files in current directory / sub directories, directories in current directory / sub directories 
    example: g:\ size:184.209 GB files:5/98053 dirs:10/19387

.PARAMETER directory
    directory to enumerate

.PARAMETER logFile
    log output to log file

.PARAMETER minSizeGB
    minimum size of directory / file to display in GB

.PARAMETER noColor
    output in default foreground color only

.PARAMETER noTree
    output complete directory and file paths

.PARAMETER quiet
    do not display output

.PARAMETER showFiles
    output file information

.PARAMETER showPercent
    show percent graph

.PARAMETER uncompressed
    for windows file length is used instead of size on disk. this will show higher disk used but does *not* use pinvoke to kernel32
    uncompressed switch makes script pwsh compatible and is enabled by default when path contains '/'
    
.PARAMETER createperfcsv
    Cree un fichier CSV "date;folder;size"

.PARAMETER CsvPerfFile
    Chemin du fichier CSV


#>

[cmdletbinding()]
param(
    
    [Parameter(Mandatory = $true,
               ValueFromPipeline = $true,
               HelpMessage = 'Provide Directory Full Path (Ex: "C:\Temp") ')]
                [ValidateScript({
                                $regex='^\w(:)(\\{1})[^\\].*$'
                                if($_ -notmatch $regex)
                                {
                                $false
                                write-host -B white -F red "$_ N'EST PAS UN CHEMIN DE DOSSIER VALIDE (Ex: 'C:\Temp')"
                                EXIT 1
                                }
                                elseif(!(test-path $_))
                                {
                                $false
                                write-host -B white -F red "LE REPERTOIRE $_ EST INTROUVABLE - VERIFIER LE CHEMIN DU REPERTOIRE"
                                EXIT 1
                                }
                                Else
                                {
                                $true
                                }
                                })]         
                [string]$directory,
    
    [float]$minSizeGB = .01,
    [int]$depth = 99,
    [switch]$detail=$false,
    [switch]$noColor,
    [switch]$notree,
    [switch]$showFiles,
    [string]$logFile,

    [switch]$quiet,
    [switch]$showPercent=$true,
    [switch]$uncompressed,
    [switch]$createperfcsv,
    [string]$CsvPerfFile


)


$timer = get-date
$error.Clear()
$ErrorActionPreference = "silentlycontinue"
$drive = Get-PSDrive -Name $directory[0]
$writeDebug = $DebugPreference -ine "silentlycontinue"
$script:logStream = $null
$script:directories = @()
$script:directorySizes = @()
$script:foundtreeIndex = 0
$script:progressTimer = get-date
$pathSeparator = [io.path]::DirectorySeparatorChar
$isWin32 = $psversiontable.psversion -lt [version]"6.0.0" -or $global:IsWindows

function main()
{
    log-info "$(get-date) starting"
    log-info "$($directory) drive total: $((($drive.free + $drive.used) / 1GB).ToString(`"F3`")) GB used: $(($drive.used / 1GB).ToString(`"F3`")) GB free: $(($drive.free / 1GB).ToString(`"F3`")) GB"
    log-info "enumerating $($directory) sub directories, please wait..." -ForegroundColor Yellow

    $uncompressed = !$isWin32
    [dotNet]::Start($directory, $minSizeGB, $depth, [bool]$showFiles, [bool]$uncompressed)
    $script:directories = [dotnet]::_directories
    $script:directorySizes = @(([dotnet]::_directories).totalsizeGB)
    $totalFiles = (($script:directories).filesCount | Measure-Object -Sum).Sum
    $totalFilesSize = $script:directories[0].totalsizeGB
    log-info "displaying $($directory) sub directories over -minSizeGB $($minSizeGB): files: $($totalFiles) directories: $($script:directories.Count)"

    # si le parametre $createperfcsv est $true Ajout de la collecte au format CSV des données directory et taille avec la date du jour    
    if($createperfcsv)
    {
    GetSizeToCsv -directory $directory -directories $directories
    }



    $sortedBySize = $script:directorySizes -ge $minSizeGB | Sort-Object
        
    if ($sortedBySize.Count -lt 1)
    {
        log-info "no directories found! exiting" -foregroundColor Yellow
        exit
    }

    $categorySize = [int]([math]::Floor([math]::max(1, $sortedBySize.Count) / 6))
    $redmin = $sortedBySize[($categorySize * 6) - 1]
    $darkredmin = $sortedBySize[($categorySize * 5) - 1]
    $yellowmin = $sortedBySize[($categorySize * 4) - 1]
    $darkyellowmin = $sortedBySize[($categorySize * 3) - 1]
    $greenmin = $sortedBySize[($categorySize * 2) - 1]
    $darkgreenmin = $sortedBySize[($categorySize) - 1]
    $previousDir = $directory.ToLower()
    [int]$i = 0

    for ($directorySizesIndex = 0; $directorySizesIndex -lt $script:directorySizes.Length; $directorySizesIndex++)
    {

        $previousDir = enumerate-directorySizes -directorySizesIndex $directorySizesIndex -previousDir $previousDir
        

    }

    log-info "$(get-date) finished. total time $((get-date) - $timer)"
}

function enumerate-directorySizes($directorySizesIndex, $previousDir)
{
    $currentIndex = $script:directories[$directorySizesIndex]
    $sortedDir = $currentIndex.directory
    log-info -debug -data "checking dir $($currentIndex.directory) previous dir $($previousDir) tree index $($directorySizesIndex)"
    [float]$totalSizeGB = $currentIndex.totalsizeGB
    log-info -debug -data "rollup size: $($sortedDir) $([float]$totalSizeGB)"

    switch ([float]$totalSizeGB)
    {
        {$_ -ge $redmin}
        {
            $foreground = "Red"; 
            break;
        }
        {$_ -gt $darkredmin}
        {
            $foreground = "DarkRed"; 
            break;
        }
        {$_ -gt $yellowmin}
        {
            $foreground = "Yellow"; 
            break;
        }
        {$_ -gt $darkyellowmin}
        {
            $foreground = "DarkYellow"; 
            break;
        }
        {$_ -gt $greenmin}
        {
            $foreground = "Green"; 
            break;
        }
        {$_ -gt $darkgreenmin}
        {
            $foreground = "DarkGreen"; 
        }

        default
        {
            $foreground = "Gray"; 
        }
    }

    if (!$notree)
    {
        while (!$sortedDir.Contains("$($previousDir)$($pathSeparator)"))
        {
            $previousDir = "$([io.path]::GetDirectoryName($previousDir))"
            log-info -debug -data "checking previous dir: $($previousDir)"
        }

        $percent = ""

        if ($showPercent)
        {
            if ($directorySizesIndex -eq 0)
            {
                # set root to files in root dir
                $percentSize = $currentIndex.sizeGB / $totalFilesSize
            }
            else 
            {
                $percentSize = $totalSizeGB / $totalFilesSize
            }

            $percent = "[$(('X' * ($percentSize * 10)).tostring().padright(10))]"
        }

        $output = $percent + $sortedDir.Replace("$($previousDir)$($pathSeparator)", "$(`" `" * $previousDir.Length)$($pathSeparator)")
    }
    else
    {
        $output = $sortedDir
    }

    if ($detail)
    {
        log-info ("$($output)" `
            + "`tsize:$(($totalSizeGB).ToString(`"F3`")) GB" `
            + " files:$($currentIndex.filesCount)/$($currentIndex.totalFilesCount)" `
            + " dirs:$($currentIndex.directoriesCount)/$($currentIndex.totalDirectoriesCount)") -ForegroundColor $foreground
    }
    else
    {
        log-info "$($output) `t$(($totalSizeGB).ToString(`"F3`")) GB" -ForegroundColor $foreground
    }

    if ($showFiles)
    {
        foreach ($file in ($currentIndex.files).getenumerator())
        {
            log-info ("$(' '*($output.length))$([int64]::Parse($file.value).tostring("N0").padleft(15))`t$($file.key)") -foregroundColor cyan
        }
    }

   return $sortedDir
}

function log-info($data, [switch]$debug, $foregroundColor = "White")
{
    if ($debug -and !$writeDebug)
    {
        return
    }

    if ($debug)
    {
        $foregroundColor = "Yellow"
    }

    if($noColor)
    {
        $foregroundColor = "White"
    }

    if (!$quiet)
    {
        write-host $data -ForegroundColor $foregroundColor
    }

    if($InformationPreference -ieq "continue")
    {
        Write-Information $data
    }

    if ($logFile)
    {
        if ($script:logStream -eq $null)
        {
            $script:logStream = new-object System.IO.StreamWriter ($logFile, $true)
        }

        $script:logStream.WriteLine($data)
    }
}


function GetSizeToCsv($directory,$directories,$CsvPerfFolder)
{
# Date actuelle
$Now = get-date -Format "dd-MM-yyyy"
# On crée un fichier avec le nom des colonnes. 
$CsvFile = New-Item -Path $CsvPerfFile -Force


"date;folder;size" | Add-Content -Path $CsvFile.FullName
#Pour chacune des lignes de la variable $directories on construis une ligne CSV "date,repertoire,size" qu'on ajoute au fichier
$directories | foreach {Add-Content -Value "$Now;$($_.directory);$($_.totalSizeGB)`n" -Path $CsvFile.FullName} 
}


$code = @'
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;

public class dotNet
{
    [DllImport("kernel32.dll")]
    private static extern uint GetCompressedFileSizeW([In, MarshalAs(UnmanagedType.LPWStr)] string lpFileName,
        [Out, MarshalAs(UnmanagedType.U4)] out uint lpFileSizeHigh);

    [DllImport("kernel32.dll", SetLastError = true, PreserveSig = true)]
    private static extern int GetDiskFreeSpaceW([In, MarshalAs(UnmanagedType.LPWStr)] string lpRootPathName,
       out uint lpSectorsPerCluster, out uint lpBytesPerSector, out uint lpNumberOfFreeClusters,
       out uint lpTotalNumberOfClusters);

    public static uint _clusterSize;
    public static int _depth;
    public static List<directoryInfo> _directories;
    public static float _minSizeGB;
    public static bool _showFiles;
    public static List<Task> _tasks;
    public static DateTime _timer;
    public static bool _uncompressed;
    public static string _pathSeparator = @"\";

    public static void Main() { }
    public static void Start(string path, float minSizeGB = 0.01f, int depth = 99, bool showFiles = false, bool uncompressed = false)
    {
        _directories = new List<directoryInfo>();
        _timer = DateTime.Now;
        _showFiles = showFiles;
        _tasks = new List<Task>();
        _uncompressed = uncompressed;
        _minSizeGB = minSizeGB;

        if(path.Contains("/"))
        {
            _pathSeparator = "/";
        }

        _depth = depth + path.Split(_pathSeparator.ToCharArray()).Count();

        if (!_uncompressed)
        {
            _clusterSize = GetClusterSize(path);
        }

        // add 'root' path
        directoryInfo rootPath = new directoryInfo() { directory = path.TrimEnd(_pathSeparator.ToCharArray()) };
        _directories.Add(rootPath);
        _tasks.Add(Task.Run(() => { AddFiles(rootPath); }));

        Console.WriteLine("getting directories");
        AddDirectories(path, _directories);
        Console.WriteLine("waiting for task completion");

        while (_tasks.Where(x => !x.IsCompleted).Count() > 0)
        {
            _tasks.RemoveAll(x => x.IsCompleted);
            Thread.Sleep(100);
        }

        Console.WriteLine(string.Format("total files: {0} total directories: {1}", _directories.Sum(x => x.filesCount), _directories.Count));
        Console.WriteLine("sorting directories");
        _directories.Sort();
        Console.WriteLine("rolling up directory sizes");
        TotalDirectories(_directories);
        Console.WriteLine("filtering directory sizes");
        FilterDirectories(_directories);

        // put trailing slash back in case 'root' path is root
        if (path.EndsWith(_pathSeparator))
        {
           _directories.ElementAt(0).directory = path;
        }

        Console.WriteLine(string.Format("Processing complete. minutes: {0:F3} filtered directories: {1}", (DateTime.Now - _timer).TotalMinutes, _directories.Count));
        return;
    }

    private static void AddDirectories(string path, List<directoryInfo> directories)
    {
        try
        {
            List<string> subDirectories = Directory.GetDirectories(path).ToList();

            foreach (string dir in subDirectories)
            {
                FileAttributes att = new DirectoryInfo(dir).Attributes;

                if ((att & FileAttributes.ReparsePoint) == FileAttributes.ReparsePoint)
                {
                    continue;
                }

                directoryInfo directory = new directoryInfo() { directory = dir };
                directories.Add(directory);
                _tasks.Add(Task.Run(() => { AddFiles(directory); }));
                AddDirectories(dir, directories);
            }
        }
        catch { }
    }

    private static void AddFiles(directoryInfo directoryInfo)
    {
        long sum = 0;

        try
        {
            DirectoryInfo dInfo = new DirectoryInfo(directoryInfo.directory);
            List<FileInfo> filesList = dInfo.GetFileSystemInfos().Where(x => (x is FileInfo)).Cast<FileInfo>().ToList();
            directoryInfo.directoriesCount = dInfo.GetDirectories().Count();

            if (_uncompressed)
            {
                sum = filesList.Sum(x => x.Length);
            }
            else
            {
                sum = GetSizeOnDisk(filesList);
            }

            if (sum > 0)
            {
                directoryInfo.sizeGB = (float)sum / (1024 * 1024 * 1024);
                directoryInfo.filesCount = filesList.Count;


                if (_showFiles)
                {
                    foreach (FileInfo file in filesList)
                    {
                        directoryInfo.files.Add(file.Name, file.Length);
                    }

                    directoryInfo.files = directoryInfo.files.OrderByDescending(v => v.Value).ToDictionary(x => x.Key, x => x.Value);
                }
            }
        }
        catch { }
    }

    private static void FilterDirectories(List<directoryInfo> directories)
    {
        _directories = directories.Where(x => x.totalSizeGB >= _minSizeGB & (x.directory.Split(_pathSeparator.ToCharArray()).Count() <= _depth)).ToList();
    }

    private static uint GetClusterSize(string fullName)
    {
        uint dummy;
        uint sectorsPerCluster;
        uint bytesPerSector;
        int result = GetDiskFreeSpaceW(fullName, out sectorsPerCluster, out bytesPerSector, out dummy, out dummy);

        if (result == 0)
        {
            return 0;
        }
        else
        {
            return sectorsPerCluster * bytesPerSector;
        }
    }

    public static long GetFileSizeOnDisk(FileInfo file)
    {
        // https://stackoverflow.com/questions/3750590/get-size-of-file-on-disk
        uint hosize;
        string name = file.FullName.StartsWith("\\\\") ? file.FullName : "\\\\?\\" + file.FullName;
        uint losize = GetCompressedFileSizeW(name, out hosize);
        long size;

        if (losize == 4294967295 && hosize == 0)
        {
            // 0 byte file
            return 0;
        }

        size = (long)hosize << 32 | losize;
        return ((size + _clusterSize - 1) / _clusterSize) * _clusterSize;
    }

    private static long GetSizeOnDisk(List<FileInfo> filesList)
    {
        long result = 0;

        foreach (FileInfo fileInfo in filesList)
        {
            result += GetFileSizeOnDisk(fileInfo);
        }

        return result;
    }

    private static void TotalDirectories(List<directoryInfo> dInfo)
    {
        directoryInfo[] dirEnumerator = dInfo.ToArray();
        int index = 0;
        int firstMatchIndex = 0;

        foreach (directoryInfo directory in dInfo)
        {

            if (directory.totalSizeGB > 0)
            {
                continue;
            }

            bool match = true;
            bool firstmatch = false;

            if (index == dInfo.Count)
            {
                index = 0;
            }

            string pattern = string.Format(@"{0}(\\|/|$)", Regex.Escape(directory.directory));

            while (match && index < dInfo.Count)
            {
                string dirToMatch = dirEnumerator[index].directory;

                if (Regex.IsMatch(dirToMatch, pattern, RegexOptions.IgnoreCase))
                {
                    if (!firstmatch)
                    {
                        firstmatch = true;
                        firstMatchIndex = index;
                    }
                    else
                    {
                        directory.totalDirectoriesCount += dirEnumerator[index].directoriesCount;
                        directory.totalFilesCount += dirEnumerator[index].filesCount;
                    }

                    directory.totalSizeGB += dirEnumerator[index].sizeGB;
                }
                else if (firstmatch)
                {
                    match = false;
                    index = firstMatchIndex;
                }

                index++;
            }
        }
    }

    public class directoryInfo : IComparable<directoryInfo>
    {
        public string directory;
        public int directoriesCount;
        public Dictionary<string, long> files = new Dictionary<string, long>();
        public int filesCount;
        public float sizeGB;
        public int totalDirectoriesCount;
        public int totalFilesCount;
        public float totalSizeGB;

        int IComparable<directoryInfo>.CompareTo(directoryInfo other)
        {
            // fix string sort 'git' vs 'git lb' when there are subdirs comparing space to \ and set \ to 29
            string compareDir = new String(directory.ToCharArray().Select(ch => ch <= (char)47 ? (char)29 : ch).ToArray());
            string otherCompareDir = new String(other.directory.ToCharArray().Select(ch => ch <= (char)47 ? (char)29 : ch).ToArray());
            return String.Compare(compareDir, otherCompareDir, true);
        }
    }
}
'@

try
{
    Add-Type $code
    main
}
catch
{
    write-host "main exception: $($error | out-string)"   
    $error.Clear()
}
finally
{
    [dotnet]::_directories.clear()
    $script.directories = $Null

    if ($script:logStream)
    {
        $script:logStream.Close() 
        $script:logStream = $null
    }
}

Dans cet article, un exemple de modification customisé des champs d’une colonne en fonction de la valeur d’une autre colonne.

Dans cet exemple, celui d’une liste d’utilisateurs et de leur pays, on souhaite ajouter un préfixe au nom de l’utilisateur, en fonction de leur pays.

On importe les données (dans notre exemple, celui d’un fichier Texte/CSV)

Une fois les données importées, on sélectionne Transformer les données

On sélectionne Utiliser la première ligne pour les en-têtes pour changer le nom des colonnes 

On sélectionne Editeur avancé pour afficher la requête complète

AVANT :

let

    source = csv.document(file.contents("c:\users\cjourdan\documents\test_data.csv"),[delimiter=";", columns=2, encoding=1252, quotestyle=quotestyle.none]),

    #"type modifie" = table.transformcolumntypes(source,{{"column1", type text}, {"column2", type text}}),

    #"en-tetes promus" = table.promoteheaders(#"type modifie", [promoteallscalars=true]),

    #"type modifie1" = table.transformcolumntypes(#"en-tetes promus",{{"name", type text}, {"country", type text}})

 in

    #"type modifié1"

On ajoute à la requête les lignes ci-dessous

APRES :

Requête complète :

let

    Source = Csv.Document(File.Contents("C:\Users\cjourdan\Documents\Test_Data.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),

    #"En-têtes promus" = Table.PromoteHeaders(#"Type modifié", [PromoteAllScalars=true]),

    #"Type modifié1" = Table.TransformColumnTypes(#"En-têtes promus",{{"Name", type text}, {"Country", type text}}),

    #"Custo Replace value" = Table.ReplaceValue(#"Type modifié1" ,each [Name],each

        if ([Country]="United State") then "US_" & Text.From([Name])

        else if ([Country]="France") then "FR_" & Text.From([Name])

        else if ([Country]="Brazil") then "BR_" & Text.From([Name])

        else if ([Country]="England") then "UK_" & Text.From([Name])

        else if ([Country]="Germany") then "GER_" & Text.From([Name])

        else if ([Country]="Spain") then "SP_" & Text.From([Name])

        else if ([Country]="Italia") then "IT_" & Text.From([Name])

        else if ([Country]="Japan") then "JP_" & Text.From([Name])

        else if ([Country]="Norway") then "NW_" & Text.From([Name])

        else if ([Country]="Russia") then "RU_" & Text.From([Name])

        else [Name],Replacer.ReplaceValue,{"Name"})

in

    #"Custo Replace value"

Apres application, les noms sont modifiés

ValidateScript est un mot clé dans la declaration de paramètres d'un script ou d'une fonction, permettant de valider la valeur d'un paramètre en excutant un bloc de script qui va tester la valeur passée en paramètre. NB: il complète le mot clé ValidatePattern utilisé lui pour valider le paramètre a l'aide d'une expression regulière.

L'exemple ci-dessous est celui de la validation qu'une addresse IP donnée en paramètre est bien au format IPv4. Un message customisé est renvoyé.

[CmdletBinding()]
param(

[Parameter(Mandatory=$true,HelpMessage="IP cible")]
          [ValidateScript({
                           $startchar = "^"  # CARACTERE DE DEBUT DE REGEX
                           $endchar = "$"    # CARACTERE DE FIN DE REGEX
                           $ZeroOrOneTime = '?' # CHARACTERE 0 OU 1 FOIS
                           $byte = "(?:25[0-5]|2[0-4][0-9]|[01]$ZeroOrOneTime[0-9][0-9]$ZeroOrOneTime)" # REGEX CORRESPONDANT A UN NOMBRE D'UNE IP
                           $dot = '\.' # CARACTERE '.' 
                           $IPv4 = "$byte$dot$byte$dot$byte$dot$byte" # REGEX COMPLETE D'UNE IPv4
                           if($_ -match "$startchar$IPv4$endchar")
                                {
                                $true
                                } 
                                else 
                                {
                                write-host -B white -F red "$_ N'EST PAS UNE ADDRESSE IPV4 VALIDE. VEUILLEZ RENSEIGNER UNE ADRESSE AU FORMAT X.X.X.X (Ex: 192.168.0.1)"
                                EXIT 1
                                }
                          })]          
$TargetIP
)

Write-Host -F Green "$TargetIP EST UNE VALEUR CORRECTE"

Si la valeur renseignée est une adresse IPv4 correcte:

Si la valeur renseignée n'est pas une adresse IPv4 correcte:

Le scriptblock éxécuté par ValidateScript peut bien sur être reutilisé comme une fonction a part entière, en dehors du bloc de paramètres.

Le script ci-dessous utilise une requete SQL SCCM issue d'un precedent post, enrichie d'une autre vue, pour determiner, a partir du resultat de la requete, des roles applicatifs de serveurs, et en generer des listes de serveurs correspondants.

D'autres roles peuvent etre ajouté dans la section "Sous-Regroupements", pour enrichir les listes de roles.

NB: La variable $Result qui est le resultat direct de la requete SQL contiens tout les éléments (exe, add-remove-program, services), par serveur, permettant de determiner d'autres roles.

########################################################################################################
### REQUETE LA BASE SQL DE SCCM POUR OBTENIR LA LISTE DES APPLICATIONS ET EXECUTABLES DES ASSETS SERVEURS.
### EN FONCTION DES RESULTATS, GENERATION DE LISTES DE SERVEURS PAR ROLES APPLICATIFS (IIS,SQL ...).
### EXPORT DES RESULTAT EN FICHIER CSV  #####
######################################################################################################## 


<# 

    .SYNOPSIS 
        REQUETE LA BASE SQL DE SCCM POUR OBTENIR LA LISTE DES APPLICATIONS ET EXECUTABLES DES ASSETS SERVEURS.
        EN FONCTION DES RESULTATS, GENERATION DE LISTES DE SERVEURS PAR ROLES APPLICATIFS (IIS,SQL ...).
        EXPORT DU RESULTAT EN FICHIER CSV.

    .PARAMETER  
        SQLInstance : Instance SQL
        SQLDB : Instance SQL
        SQLQuery : Requete SQL
        ExportFolder : Dossier d'export du fichier CSV
        LogFolder : Chemin du dossier où creer le log du script

 
    .EXAMPLE 
     .\SCCM_Roles_And_Apps.ps1 -SQLInstance MyServer -SQLDB CM_BIM -ExportFolder D:\ExportCSV -LogFolder D:\ExportCSV
#>


[CmdletBinding()]
param(
[Parameter(Mandatory=$true,HelpMessage="Instance SQL")]
[string]$SQLInstance,

[Parameter(Mandatory=$true,HelpMessage="Base SQL")]
[string]$SQLDB,

[Parameter(Mandatory=$false,HelpMessage="Requete SQL")] 
[string]$SQLQuery= $("/* --- SERVER SOFTWARES, EXECUTABLES, AND SERVICES(NT) INVENTORY ---  
 NB: UNION ENTRE LES VUES:
	 [v_GS_INSTALLED_SOFTWARE_CATEGORIZED] 
	 [v_GS_INSTALLED_EXECUTABLE]
	 [v_ADD_REMOVE_PROGRAMS]
	 [v_GS_SERVICE]

	       
 NB: DES COLONNES SONT CREES ET POSITIONNEE A NULL POUR POUVOIR EFFECTUER LE UNION. (Meme nombre de colonnes) 

L'UNION ENTRE LES VUES EST REGROUPEE DANS UNE TABLE 'TAB' (CLAUSE 'WITH').
LA REQUETE FINALE EST EXECUTEE SUR LA TABLE 'TAB'


v_GS_INSTALLED_SOFTWARE_CATEGORIZED
Répertorie des informations sur les applications logicielles installées sur Configuration Manager clients trouvés via Asset Intelligence. 
Cette vue contient les informations contenues dans le v_GS_INSTALLED_SOFTWARE afficher et joint plusieurs autres tables pour fournir des détails supplémentaires sur les logiciels installés.

v_GS_INSTALLED_EXECUTABLE
Répertorie des informations sur les fichiers exécutables de l’application logicielle installée sur Configuration Manager clients trouvés via Asset Intelligence.

[v_ADD_REMOVE_PROGRAMS]
Combination of 32 and 64 bit programs data in 'Add Remove Programs'

[v_GS_SERVICE]
Services NT des Machines Windows

*/



DECLARE @CollectionID as Varchar(8)
SET @CollectionID = 'SMS00001' --Specify the collection ID
;


WITH TAB (
ResourceID
,ProductCode
,Machine_Name
,OS_Name
,Publisher
,NormalizedPublisher
,ExecutableName
,ServiceName
,ServicePath
,Service_Description
,Service_StartMode
,Service_StartName
,ProductName
,AddRemove_Name
,NormalizedName
,FamilyName
,CategoryName
,ProductVersion
,InstallDate
,Soft_Autostart
,EXE_FilePath
,EXE_Description
,EXE_FileVersion
)

AS (




SELECT 
SYST.ResourceID,
UPPER(SOFT.ProductCode0) as ProductCode,
SYST.Name0 as Machine_Name,

-- OS Info --
OS.Caption0 as OS_Name,

-- SOFT INFO --
SOFT.Publisher0 as Publisher
,SOFT.NormalizedPublisher
,NULL as ExecutableName
,NULL as ServiceName
,NULL as ServicePath
,NULL as Service_StartMode
,NULL as Service_StartName
,NULL as Service_Description
,SOFT.ProductName0 as ProductName
,NULL as AddRemove_Name,
SOFT.NormalizedName,
SOFT.FamilyName,
SOFT.CategoryName,
SOFT.ProductVersion0 as ProductVersion,


SOFT.InstallDate0 as InstallDate,


CASE 
	WHEN AUTOSTART_SOFT.Product0 IS NULL THEN 'NO'
	ELSE 'YES'
	END as Soft_Autostart,

NULL as EXE_FilePath,
NULL as EXE_Description,
NULL as EXE_FileVersion


FROM [dbo].[v_GS_INSTALLED_SOFTWARE_CATEGORIZED] SOFT
FULL JOIN v_R_System SYST on SYST.ResourceID = SOFT.ResourceID
FULL JOIN [dbo].[v_GS_OPERATING_SYSTEM] OS on OS.ResourceID = SYST.ResourceID
FULL JOIN [dbo].[v_GS_AUTOSTART_SOFTWARE] AUTOSTART_SOFT on AUTOSTART_SOFT.Product0 = SOFT.ProductName0   -- TO MAKE RELATION WITH AUTO-START SOFTWARE
FULL JOIN v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = SYST.ResourceID)

WHERE v_FullCollectionMembership.CollectionID = @CollectionID


  
UNION 
 

SELECT 
  

SYST.ResourceID,
UPPER(EXE.ProductCode0) as ProductCode,
SYST.Name0 as 'Machine Name'

-- OS Info --
,OS.Caption0 as OS_Name

,EXE.Publisher0 as Publisher
,NULL as NormalizedPublisher
,EXE.ExecutableName0 as ExecutableName
,NULL as ServiceName
,NULL as ServicePath
,NULL as Service_StartMode
,NULL as Service_StartName
,NULL as Service_Description
,EXE.Product0 as ProductName
,NULL as AddRemove_Name
,NULL as NormalizedName
,NULL as FamilyName
,NULL as CategoryName 
,EXE.ProductVersion0 AS ProductVersion
,NULL as InstallDate
,NULL as Soft_Autostart

,EXE.InstalledFilePath0 as EXE_FilePath
,EXE.Description0 as EXE_Description
,EXE.FileVersion0 AS EXE_FileVersion


FROM [dbo].[v_GS_INSTALLED_EXECUTABLE] EXE
  
  FULL JOIN v_R_System SYST on SYST.ResourceID = EXE.ResourceID
  FULL JOIN [dbo].[v_GS_OPERATING_SYSTEM] OS on OS.ResourceID = SYST.ResourceID
  FULL JOIN v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = SYST.ResourceID)
  --FULL JOIN [dbo].[v_GS_SERVICE] SERVICE_NT on SERVICE_NT.ResourceID = SYST.ResourceID
 
 WHERE v_FullCollectionMembership.CollectionID = @CollectionID
 
  
  
UNION


SELECT
ARP.ResourceID
,UPPER(ARP.ProdID0) as ProductCode
,SYST.Name0 as 'Machine Name'
-- OS Info --
,OS.Caption0 as OS_Name
,ARP.Publisher0 as Publisher
,NULL as NormalizedPublisher
,NULL as ExecutableName
,NULL as ServiceName
,NULL as ServicePath
,NULL as Service_StartMode
,NULL as Service_StartName
,NULL as Service_Description
,NULL as ProductName
,ARP.DisplayName0 as AddRemove_Name
,NULL as NormalizedName
,NULL as FamilyName
,NULL as CategoryName 
,ARP.Version0 AS ProductVersion 

,CASE
WHEN ISDATE(ARP.InstallDate0) <> 0 THEN CONVERT(date,ARP.InstallDate0)
WHEN ISDATE(ARP.InstallDate0) = 0 THEN NULL
END AS InstallDate

,NULL as Soft_Autostart
,NULL as EXE_FilePath
,NULL as EXE_Description
,NULL as EXE_FileVersion

FROM [dbo].[v_ADD_REMOVE_PROGRAMS] ARP
FULL JOIN v_R_System SYST on SYST.ResourceID = ARP.ResourceID  
FULL JOIN v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = SYST.ResourceID)
--FULL JOIN [dbo].[v_GS_SERVICE] SERVICE_NT on SERVICE_NT.ResourceID = SYST.ResourceID
INNER JOIN [dbo].[v_GS_OPERATING_SYSTEM] OS on OS.ResourceID = SYST.ResourceID  

WHERE v_FullCollectionMembership.CollectionID = @CollectionID 


UNION 
 

SELECT 

SERVICE_NT.ResourceID,
NULL as ProductCode,
SYST.Name0 as 'Machine Name'

-- OS Info --
,OS.Caption0 as OS_Name

,NULL as Publisher
,NULL as NormalizedPublisher
,NULL as ExecutableName
,SERVICE_NT.Name0 as ServiceName
,SERVICE_NT.PathName0 as ServicePath
,SERVICE_NT.Description0 as Service_Description
,SERVICE_NT.StartMode0 as Service_StartMode
,SERVICE_NT.StartName0 as Service_StartName
,NULL as ProductName
,NULL as AddRemove_Name
,NULL as NormalizedName
,NULL as FamilyName
,NULL as CategoryName 
,NULL as ProductVersion
,NULL as InstallDate
,NULL as Soft_Autostart
,NULL as EXE_FilePath
,NULL as EXE_Description
,NULL AS FileVersion


FROM [dbo].[v_GS_SERVICE] as SERVICE_NT
  
  FULL JOIN v_R_System SYST on SYST.ResourceID = SERVICE_NT.ResourceID
  FULL JOIN [dbo].[v_GS_OPERATING_SYSTEM] OS on OS.ResourceID = SYST.ResourceID
  FULL JOIN v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = SYST.ResourceID)
 
 WHERE v_FullCollectionMembership.CollectionID = @CollectionID

 
  
  ) 
  

 
 SELECT  DISTINCT 

TAB.ResourceID
,TAB.Machine_Name
,TAB.OS_Name

/*-- EXEMPLES DE COLONNE CUSTOM SELON DES VALEURS
,CASE

	WHEN (TAB.ServiceName = 'W3SVC' and TAB.Service_StartMode = 'Auto') THEN 'YES'
	ELSE NULL
	END AS 'IS_IIS'
	
,CASE

	WHEN (TAB.AddRemove_Name like 'SQL Server%Database Engine Services') THEN 'YES'
	ELSE NULL
	END AS 'IS_MSSQL_DBENGINE'  


,CASE

	WHEN (TAB.AddRemove_Name like 'SQL Server%Reporting Services') THEN 'YES'
	ELSE NULL
	END AS 'IS_MSSQL_RS'  
*/

,TAB.Publisher
,TAB.NormalizedPublisher
,TAB.ExecutableName
,TAB.ServiceName
,TAB.ServicePath
,TAB.Service_Description
,TAB.Service_StartMode
,TAB.Service_StartName
,TAB.ProductName
,TAB.ProductCode
,TAB.AddRemove_Name
,TAB.NormalizedName
,TAB.FamilyName
,TAB.CategoryName





,CASE
	
	WHEN TAB.ProductVersion like '[0-9][.][0-9][.][0-9][-]%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '[0-9][.][0-9][.][0-9][a-z]' THEN SUBSTRING(TAB.ProductVersion,0,5)
	WHEN TAB.ProductVersion like '[0-9][.][0-9][.][0-9][a-z]%' THEN SUBSTRING(TAB.ProductVersion,0,5)
	WHEN TAB.ProductVersion like '[0-9][.][0-9][.][0-9][0-9][0-9][0-9][a-z]%' THEN SUBSTRING(TAB.ProductVersion,0,5)
	WHEN TAB.ProductVersion like '[0-9][.][0-9][.][0-9][.]%[a-z]%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '[0-9][0-9][.][0-9][.]%[a-z]%' AND TAB.EXE_FileVersion IS NOT NULL THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '[0-9][0-9][.][0-9][0-9][.][a-z]%' AND TAB.EXE_FileVersion IS NULL THEN SUBSTRING(TAB.ProductVersion,0,5)
	WHEN TAB.ProductVersion like '[0-9][.][0-9][0-9][.][a-zA-Z]%' AND TAB.EXE_FileVersion IS NULL THEN SUBSTRING(TAB.ProductVersion,0,4)
	WHEN TAB.ProductVersion like '[0-9][.][0-9][0-9][.][0-9][0-9][0-9][.][0-9]%[0-9][0-9][.][0-9][0-9]%)' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '[0-9][.][0-9][0-9][.][0-9][0-9][0-9][0-9][.][0-9][0-9][0-9]%[0-9][0-9][.][0-9][0-9]%)' THEN TAB.EXE_FileVersion
	
	WHEN TAB.ProductVersion = 'Unidentified build' THEN NULL
	WHEN TAB.ProductVersion like '%.FR' THEN REPLACE(TAB.ProductVersion,'.FR','')
	WHEN TAB.ProductVersion like '%.RR' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%Release%' THEN REPLACE(TAB.ProductVersion,'Release','')
	WHEN TAB.ProductVersion like '%bet%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%build%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%Eagle%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%Impala%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%Summer%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%sum%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like 'S%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%c40%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%b5%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%59d%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%ffbc%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%2d6%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%7d7%' THEN TAB.EXE_FileVersion
	
	WHEN TAB.ProductVersion like '%Unversioned%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%rd_store_sdk%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion like '%[/]%' THEN SUBSTRING(TAB.ProductVersion,LEN(SUBSTRING(TAB.ProductVersion,0,LEN(TAB.ProductVersion) - CHARINDEX(' ',TAB.ProductVersion)+1))+1, LEN(TAB.ProductVersion) - LEN(SUBSTRING(TAB.ProductVersion,0,LEN(TAB.ProductVersion) - CHARINDEX(' ',TAB.ProductVersion))))
	WHEN TAB.ProductVersion like '%BLD%' THEN SUBSTRING(TAB.ProductVersion,0,5)
	WHEN TAB.ProductVersion like '%PQ%' THEN TAB.EXE_FileVersion
	WHEN TAB.ProductVersion = 'sonicmf.exe' THEN NULL
	WHEN TAB.ProductVersion = 'Unidentified build' THEN NULL
	WHEN TAB.ProductVersion like '%A%' THEN REPLACE(TAB.ProductVersion,'A','')
	WHEN TAB.ProductVersion like 'xxx%' THEN NULL
	WHEN TAB.ProductVersion like '%c' THEN REPLACE(TAB.ProductVersion,'c','')
	WHEN TAB.ProductVersion like '%k' THEN REPLACE(TAB.ProductVersion,'k','')
	WHEN TAB.ProductVersion like '%g' THEN REPLACE(TAB.ProductVersion,'g','')
	WHEN TAB.ProductVersion like '%.windows%' THEN SUBSTRING(TAB.ProductVersion,0,6)
	WHEN TAB.ProductVersion like '%beta%' THEN REPLACE(TAB.ProductVersion,'beta','')
	WHEN TAB.ProductVersion like '%Release%' THEN REPLACE(TAB.ProductVersion,'Release','')
	WHEN TAB.ProductVersion like '%NotilusWeb%' THEN REPLACE(TAB.ProductVersion,'NotilusWeb','')
	WHEN TAB.ProductVersion like '%WUG%' THEN REPLACE(TAB.ProductVersion,'WUG','')
	WHEN TAB.ProductVersion like '%[ ]%' THEN REPLACE(TAB.ProductVersion,' ','')
	
	WHEN TAB.EXE_Description = 'Windows Admin Center Windows Service' THEN TAB.EXE_FileVersion
	WHEN TAB.EXE_Description like '%ServiceHub.Host.CLR%' THEN TAB.EXE_FileVersion
	WHEN TAB.EXE_Description like '%Microsoft.ServiceHub.Controller%' THEN TAB.EXE_FileVersion
	WHEN TAB.EXE_Description like '%Microsoft Mashup%' THEN TAB.EXE_FileVersion
	WHEN TAB.EXE_Description like '%Element programu Soneta%' THEN TAB.EXE_FileVersion

		
	
	
	ELSE TAB.ProductVersion
	END AS ProductVersion


,TAB.InstallDate
,TAB.Soft_Autostart
,TAB.EXE_FilePath
,TAB.EXE_Description


,CASE
	
	WHEN TAB.EXE_FileVersion like '%build%' THEN NULL
	WHEN TAB.EXE_FileVersion like '%beta%' THEN REPLACE(TAB.EXE_FileVersion,'beta','')
	WHEN TAB.EXE_FileVersion like '%Release%' THEN REPLACE(TAB.EXE_FileVersion,'Release','')
	WHEN TAB.EXE_FileVersion like '%[ ]%' THEN REPLACE(TAB.EXE_FileVersion,' ','')
	--WHEN TAB.EXE_FileVersion like '%[,]%' THEN REPLACE(TAB.EXE_FileVersion,',','.')
	WHEN TAB.EXE_FileVersion like '%[ ]%' THEN SUBSTRING(TAB.EXE_FileVersion,0, LEN(TAB.EXE_FileVersion) - LEN(SUBSTRING(TAB.EXE_FileVersion,0,LEN(TAB.EXE_FileVersion) - CHARINDEX(' ',TAB.EXE_FileVersion))))
	WHEN TAB.EXE_FileVersion = 'sonicmf.exe' THEN NULL
	WHEN TAB.EXE_FileVersion like '%A%' THEN REPLACE(TAB.EXE_FileVersion,'A','')
	WHEN TAB.EXE_FileVersion like 'x%' THEN NULL
	WHEN TAB.EXE_FileVersion like '%c' THEN REPLACE(TAB.EXE_FileVersion,'c','')
	WHEN TAB.EXE_FileVersion like '%g' THEN REPLACE(TAB.EXE_FileVersion,'g','')
	WHEN TAB.EXE_FileVersion like '%WUG%' THEN REPLACE(TAB.EXE_FileVersion,'WUG','')
	
	
	ELSE TAB.EXE_FileVersion
	END AS EXE_FileVersion

 
 FROM  TAB
 
 WHERE TAB.OS_Name like '%Server%' -- UNIQUEMENT LES OS SERVEURS

"),


[Parameter(Mandatory=$true,HelpMessage="Dossier d'export du fichier CSV")]
[string]$ExportFolder,

[Parameter(Mandatory=$true,HelpMessage="Chemin du dossier où creer le log du script")] 
[string]$LogFolder

)


# SCRIPT NAME
$ScriptName = "SCCM_Roles_And_Apps.ps1"


# LogName = ScriptName without extension
$Log = $ScriptName.Split('.')[0]


### FUNCTIONS

# Function Write-Log



function Write-Log 
{ 
    <# 
    .SYNOPSIS 
        This function creates or appends a line to a log file. 
 
    .PARAMETER  Message 
        The message parameter is the log message you'd like to record to the log file. 
 
    .EXAMPLE 
        PS C:\> Write-Log -Message 'Value1' 
        This example shows how to call the Write-Log function with named parameters. 
    #> 
    [CmdletBinding()] 
    param ( 
        [Parameter(Mandatory)] 
        [string]$Message,
        [Parameter(Mandatory)] 
        [string]$LogPath, 
        [Parameter(Mandatory)] 
        [string]$LogName
        
    ) 
     
    try 
    { 
        $DateTime = Get-Date -Format ‘MM-dd-yy HH:mm:ss’ 
        Add-Content -Value "$DateTime # $Message" -Path "$LogPath\$LogName.log" 
    } 
    catch 
    { 
        Write-Error $_.Exception.Message 
    } 
} 


Function GetSQLData {

<# 
    .SYNOPSIS 
        This function query SQL Database and get Data 
 
    .PARAMETER  
        SQLInstance: Instance SQL.
        SQLDB: Base SQL.
        SQLQuery: Requete SQL.

 
    .EXAMPLE 
        GetSQLData -SQLInstance "MyInstance" -SQLDB "MyDB" -SQLQuery "Select * from MyView"
    #> 


[CmdletBinding()] 
    Param( 
        [Parameter(Mandatory=$false)] 
        [string[]] 
        $SQLInstance,
        [Parameter(Mandatory=$false)] 
        [string[]] 
        $SQLDB,
        [Parameter(Mandatory=$false)] 
        [string[]] 
        $SQLQuery
        
        )

$connectionString = "Data Source=$SQLInstance;"+"Integrated Security=SSPI;"+"Initial Catalog=$SQLDB"

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($SQLQuery,$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

}






# EXECUTE Query ($SQLQuery)
    Write-Log -Message "Execution of GetSQLData on $SQLDB" -LogPath $LogFolder -LogName $Log
    $Result = 
    Try {
        GetSQLData -SQLInstance $SQLInstance -SQLDB $SQLDB -SQLQuery $SQLQuery
        }
    Catch
        {
        $Message = "ERROR DURING EXECUTION OF QUERY"
        Write-Host -F Red $Message
        Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
        Exit 1
        }


########################################
# SOUS-REGROUPEMENTS
########################################


# All IIS
$AllIIS = $Result.rows.Where({$_.ServiceName -eq 'W3SVC' -and $_.Service_StartMode -eq 'Auto'}) | select Machine_Name | group Machine_Name | select name -ExpandProperty name

# All SQLDBEngine
$AllSQLDBEngine = $Result.rows.Where({$_.AddRemove_Name -like 'SQL Server*' -and $_.AddRemove_Name -like  '*Database Engine Services'}) | select Machine_Name | group Machine_Name | select name -ExpandProperty name

# All SQL Reporting Services
$AllSQLRS = $Result.rows.Where({$_.AddRemove_Name -like 'SQL Server*'`
    -and $_.AddRemove_Name -like  '*Reporting Services'`
    -and $_.AddRemove_Name -notlike '*Management Studio*'`
    -and $_.AddRemove_Name -notlike '*Data Tools*'`
        
    }) | select Machine_Name | group Machine_Name | select name -ExpandProperty name



# All Citrix Servers
$AllCitrixSrv = $Result.rows.Where({$_.ServiceName -eq 'BrokerAgent' -or $_.ServiceName -eq 'CitrixStorefront' }) | select Machine_Name | group Machine_Name | select name -ExpandProperty name


# All Apache Tomcat Servers
$AllTomcatSrv = $Result.rows.Where({$_.AddRemove_Name -like 'Apache Tomcat*'}) | select Machine_Name | group Machine_Name | select name -ExpandProperty name




# EXPORTS TO TXT FILES

$AllIIS | sort |  Out-File -FilePath "$ExportFolder\All_IIS.txt" -Force

$AllSQLDBEngine | sort |  Out-File -FilePath "$ExportFolder\AllSQLDBEngine.txt" -Force

$AllSQLRS | sort |  Out-File -FilePath "$ExportFolder\AllSQLRS.txt" -Force

$AllCitrixSrv | sort |  Out-File -FilePath "$ExportFolder\AllCitrixSrv.txt" -Force




# DISPLAY SUCCESS
$Message = "--- EXECUTION OK ---"
Write-Host -F Green $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log

Windows Admin center propose bien sur entre autres l’accès a l’analyseur de performance (Perfmon) de la machine gérée à distance.

Par défaut, aucun compteur n’est affiché

Cliquer sur «Espace de travail vide »

Enregistrer avant tout l’espace de travail qui contiendra les compteurs

Donner un nom explicite et enregistrer l’espace de travail

Ajouter un compteur

Dans cet exemple on ajoute : Processor / _Total / % Processor Time et l’on sélectionne le style de graphique «Ligne ».

Il est possible d’afficher aussi le style Min-Max :

Ou carte thermique :

Ajoutez d’autres compteurs et forme d’affichage en fonction des besoins.

Pensez a enregistrer en écrasant la version précédente

Il est également possible de télécharger l’espace de travail au format JSON:

Ci-dessous le fichier JSON obtenu.

On peut voir que sa modification peut être assez facilement automatisé afin d’ajouter d’autres compteurs (object,instances,instancename,type)

Enfin, en cas de suppression sur la machine concernée on plus pratiquement pour obtenir le même espace de travail sur une autre machine, il est possible de charger le fichier JSON :

Glissez le fichier dans la fenêtre

Cliquez Envoyer

Cliquez sur l’espace de travail pour l’afficher

*********************************************************

Derrière le terme un peu technique "Coloration conditionnel" se cache simplement le fait, dans un dashboard PowerBI d'afficher un changement de couleur dans le champ d'un tableau, en fonction de condition.

En plus des fonctions natives de mise en forme dans PowerBI Desktop, il est possible de creer une mesure (code DAX) , avec des conditions, que l'on va utiliser pour afficher une valeur dans un champ. Et cette valeur peut etre un code couleur.

Dans l'exemple du code ci-dessous, on a une application "MyApp" pour laquelle, dans PowerBI nous avons trois datasets (deux correspondrait par exemple a deux tables de la base de donnée associée a "MyApp", et un troisième qui est une connexion vers un outil qui reference la criticité de mes machines (ex: outil de gestion des asset ou cmdb)):

- MyAppAgents, qui contiens des infos sur les agents de mon application

- MyAppVersions, qui contiens un historique des numeros de version de mon application

-  MyServers qui est donc la table qui liste la criticité des mes serveurs.

Objectif: On veux dans un dashboard, colorer un champ en rouge ou jaune (exemple, le nom du serveur), pour lequel la version ((MyAppAgents[MyApp_agent_version]) serai differente de la dernière version, ET dont le serveur serait un serveur de Production ou de test.

Color_MyApp_agent = 

   VAR Result1 = SELECTEDVALUE(MyAppAgents[MyApp_agent_version])
   VAR Result2 = SELECTEDVALUE(MyAppVersions[MyApp_Last_version_available])
   VAR Result3 = SELECTEDVALUE(MyServers[Server_Criticity])

-- SI MyApp_agent EST DIFFERENT DE MyApp_Last_version_available ET SI Server_Criticity = "PROD" alors on renvoi "CRITICAL"
   VAR Critical = IF(AND(Result1<>Result2, Result3="PROD"),"CRITICAL")
   
-- SI MyApp_agent EST DIFFERENT DE MyApp_Last_version_available ET SI Server_Criticity <> "TEST" alors on renvoi "WARNING"  
   VAR Warning = IF(AND(Result1<>Result2, Result3<>"TEST"),"WARNING")
   
-- SI MyApp_agent EST EGALE A MyApp_Last_version_available   
   VAR OK = IF(Result1=Result2,"OK")

   
 -- SELON LE CAS ON RENVOI UN CODE COULEUR DIFFERENT
RETURN
SWITCH(
True(),
 Critical="CRITICAL" , "#FEA19E",
Warning="WARNING" , "#F9FF28",
 OK="OK" , "#96FF93" 
)

On crée donc une mesure (measure) dans laquelle on va coller le code ci-dessus

Apres validation la mesure apparait dans le dataset ou elle a été crée

On selectionne ensuite le champ que l'on veut colorer puis Conditional formatting/Background color

On selectionne Format by "Field Value", et on va venir selectionner notre mesure (Color_MyApp_agent)

Apres validation, Les valeurs de la colonne seront formatée selon la condition rencontrée (Les code couleurs '#FEA19E' peuvent bien sur etre modifié dans le code pour choisir une autre coloration):

SecretStore est un module Powershell permettant de facilement stocker et gérér des Credentials.

Dans l’exemple ci-dessous nous le mettons en place et l’utilisons pour demander, dans cet exemple, un token d’acces au cloud de l’EDR Crowdstrike.

NB : Le module SecretStore requiert l’installation du module SecretManagement

Comme tout module Powershell, si ils ne peut pas être directement téléchargé en ligne sur un repository avec la commande Install-Module, il est possible de le(s) récupérer sur Github ou encore sur Powershell Galery

(https://www.powershellgallery.com/packages/Microsoft.PowerShell.SecretStore)

(https://www.powershellgallery.com/packages/Microsoft.PowerShell.SecretManagement)

Une fois les module décompressé et stocké dans un des dossier contenant en standard des modules (ex : «C:\Program Files\WindowsPowerShell\Modules»)

NB : Si le module n’est pas stocké dans un chemin déclaré dans la variable $env:PSModulePath, son chemin complet devra être renseigné lors de l’import. Ceci peut être problématique aussi après l’import, dans le cadre de certaines commandes du module. Pour cela il est préférable qu’il soit stocké dans un chemin reconnu par la variable $env:PSModulePath

NB : Pour qu’un module soit correctement reconnu par Powershell, le nom du dossier le contenant doit être identique au nom du fichier psd1 ou psm1 :

 C:\Program Files\WindowsPowerShell\Modules \microsoft.powershell.secretstore

Dans une fenêtre powershell, exécuter Import-module en spécifiant le chemin d’accès aux fichiers indiqué ci-dessous

*****

Import-module microsoft.powershell.secretmanagement

Import-module microsoft.powershell.secretstore

*****

Par défaut le module SecretStore requiert un password pour accéder a chaque au coffre-fort de mot passe. La commande suivante permet de spécifier que l’authentification ne sera pas demandée , uniquement pour l’utilisation actuel (CurrentUser)

Set-SecretStoreConfiguration -Scope CurrentUser -Authentication None -Interaction None

La commande demande un password qui ne sera pas redemandé

La commande suivante permet de créer le coffre-fort (vault). Indiquer un nom explicite pour l’usage de ce coffre. (La commande requiert d’indiquer le module SecretStore)

Register-SecretVault -ModuleName microsoft.powershell.secretstore -Name MyVault

A présent que le coffre est créé, nous allons y stocker les informations requise, dans cet exemple, pour récupérer un token Crowdstrike.

Dans une variable de type hashtable ($ApiClient) On renseigne le Client Id, Le Client Secret, et le Hostname (url de l’api). On ‘range’  ($ApiClient) dans le coffre MYVault. (Set-secret)

$ApiClient = @{
    ClientId     = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
    ClientSecret = ' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
    Hostname     = 'https://api.eu-1.crowdstrike.com'
}

Set-Secret -Name MyApiClient -Secret $ApiClient -Vault MyVault

On peut tester a présent que le credential peut être récupéré :

Get-Secret -Name MyApiClient -Vault MyVault -AsPlainText

Et dans notre exemple, on peut maintenant récupérer notre token Crowdstrike

Get-Secret -Name MyApiClient -Vault MyVault -AsPlainText | ForEach-Object { Request-FalconToken @_ }

Notre token doit etre valable

$(Test-FalconToken).token

Les cmdlets du module PSFalcon peuvent maintenant être exécutées, par exemple :

Get-FalconHost -All -Detailed

NB : Les modules powershell SecretStore et SecretManagement peuvent même etre supprimé (Remove-Module). Le coffre crée sera toujours accessible par le compte l’ayant crée.

L’EDR Crowdstrike integre bien sur une fonction de scan de machines cibles, a la demande ou planifiés.

Aller sur le lien Endpoint security/On-demand scans

Cliquer Create a scan

Sélectionner Now ou In the future pour planifier un scan

Laisser coché l’option «Limit how long this scan runs» pour limiter la durée du scan. NB : La durée de 2 heures par défaut pourra être adapté au vu de la durée observée en moyenne dans le résultat final du/des scans.

Sélectionner une ou plusieurs et/ou des groupes de machines (la selection dans le champs affiche automatiquement les éléments)

Inscrire un ou plusieurs chemin a scanner (1 par ligne)

Inscrire éventuellement un ou plusieurs chemin a exclure

Le champ «Exclusions to test against above pattern» peut être utilisé pour tester les exclusions.

Dans l’exemple ci-dessus, on veut scanner le lecteur D:\, en excluant tout le contenu (*) de D:\App1

(la coche et la couleur du test effectué  valide le fait que la syntaxe de l’exclusion est correcte. Au contraire, par exemple  ne sera pas exclu)

Le bouton  peut être utilisé pour charger une liste d’exclusion en respectant une ligne par exclusion.

Renseigner une description explicite

Laisser par défaut la niveau de détection et de prévention a Moderate

Pour information la description des niveau de détection est la suivante :

Selectionner un niveau maximum de consommation CPU a utiliser (par defaut Low up to 25%)

Décocher Show notifications to end user si vous ne voulez pas que l’utilisateur connecté sur la machine reçoive une notification de Scan (le paramètre Pause duration permet d’autoriser l’utilisateur connecté à reporter le scan pour une durée donnée)

Cliquer Create Scan

Le scan apparait dans la liste et passez en état Completed lorsque le scan est fini.

Un clic sur le scan permet d’avoir le détail

Cliquer sur See full details pour afficher les détails complets

Dans notre exemple, la machine cible est bien dans les «Hosts without detection»

Le lien  permet d’accéder si besoin a des éléments de diagnostique/remédiation, dans le cas d’une machine ou des détections ont été remontées :