PI Services

Le blog des collaborateurs de PI Services

Script - Powershell et SCCM Query pour determiner des listes de roles applicatifs

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


 

Module Powershell SecretStore - Exemple avec API Crowdstrike

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.

Script - Exemple de recherche d'ancien fichier de log dans une arborescence de dossier

Le script ci-dessous propose de rechercher dans une liste de dossiers specifiques (expression regulière FolderPattern) des fichiers de logs plus ou moins anciens, selon un seuil prédéfini (TimeDelta), ajoute un statut au tableau selon le cas rencontré, et exporte le resultat en fichier csv.

 

##############################################
### CHECK OLD LOG FILES ###
##############################################


<# 

    .SYNOPSIS 
        VERIFICATION DE LA PRESENCE ET DE L'ANCIENNETE DU LOG LE PLUS RECENT DANS UNE LISTE DE DOSSIER; 
        UN TABLEAU DES DOSSIERS DONT LE LOG LE PLUS RECENT EST PLUS ANCIEN QUE J-$TimeDelta EST AFFICHE ET EXPORTE EN FICHIER CSV.  

    .PARAMETER  
        RootFolder : Chemin du dossier racine
        TimeDelta : Seuil d'ancienneté du fichier de log le plus recent
        FolderPattern: Expression reguliere pour selectionner des noms de sous dossier
        

 
    .EXAMPLE 
     .\Check_Old_Log_Folders.ps1 -RootFolder "C:\MyRootFolder" -TimeDelta 15
#>




[CmdletBinding()]
param(
[Parameter(Mandatory,HelpMessage="Chemin du dossier racine")]
$RootFolder,

[Parameter(Mandatory,HelpMessage="Seuil d'ancienneté du fichier de log le plus recent")]
$TimeDelta,

[Parameter(HelpMessage="Expression reguliere pour selectionner des noms de sous dossier")]
[regex]$FolderPattern = "^(SRV|DEV).*"

)



# Date du jour - TimeDelta
$DateTimeMinus = (get-date).AddDays(-$TimeDelta)

# Verification que RootFolder existe 
if (!(Test-Path -Path $RootFolder))
    {
    write-host -B White -F Red "UNABLE TO FIND $RootFolder - CHECK PATH - END OF SCRIPT"
    EXIT 1
    }


# Creation d'un PSobject pour stocker les futurs valeurs LogFolder,LastLog et Status
$FileTab = @()

# Recuperation des noms de dossier
$Folders = Get-ChildItem -Path $RootFolder -Directory | Where-Object {$_.Name -match $FolderPattern}


# Pour chacun des dossier on cherche le dernier fichier modifié
$Folders | foreach {


$LogFolder = $_.Name
$LastLog = Get-ChildItem -Path $_.FullName | select -Last 1 -Property LastWriteTime -ExpandProperty LastWriteTime

# Si le dossier est vide (pas de Lastlog) le status est KO
 if (!$LastLog) {$status = "KO - DOSSIER VIDE"}

# Si LastLog est plus ancien ou plus recent que $DateTimeMinus  
 switch($LastLog)
    {
        
        {$LastLog -lt $DateTimeMinus} {$status = "KO - OLDER THAN $DateTimeMinus"}
        {$LastLog -gt $DateTimeMinus} {$status = "OK - NEWER THAN $DateTimeMinus"}
        default {$status = "UNKNOWN"}
    }


# remplissage du tableau
$FileTab += New-Object -TypeName psobject -Property @{LogFolder=$LogFolder;LastLog=$LastLog;Status=$status}

}


# Affiche le tableau $FileTab des dossiers dont le fichier le plus recent est plus ancien que $TimeDelta ou pour lequel il n'y a pas de log
Write-Host -F red -B White "LOG FOLDERS DONT LE FICHIER DE LOG LE PLUS RECENT EST PLUS ANCIEN QUE J $TimeDelta OU POUR LESQUELS IL N'EXISTE PAS DE LOG (DOSSIER VIDE)"
$KOFiles = $FileTab | Where-Object {$_.status -like 'KO*'} | select LogFolder,LastLog,Status | Sort LastLog
$KOFiles | ft -AutoSize


# Export CSV du tableau en supprimant les caractere '"'
$KOFilesCSV = $KOFiles | ConvertTo-Csv -Delimiter "," -NoTypeInformation | ForEach {$_.replace('"','')}


# Export du fichier CSV
$KOFilesCSV | Out-File "$($pwd.ProviderPath)\OldLogFolders.csv" -Force
write-host -F Blue -B White "Old Log Folders CSV File Exported as $($pwd.ProviderPath)\OldLogFolders.csv"


# Ouverture du fichier CSV
notepad.exe "$($pwd.ProviderPath)\OldLogFolders.csv"

 

Scripting - Exemple de la recuperation de la date de modification du password d'un compte AD

Problématique:  Récuperer et rendre lisible un timestamp représentant la date de modification du password d'un compte AD, renvoyé par l'outil Dsquery (https://ss64.com/nt/dsquery.html)

 

$user = "johndoe"
$domain = "mydomain.com"

# Executer la commande dsquery pour recuperer l'attribut pwdLastSet
$obj = dsquery * -filter "samaccountname=$user" -attr displayName pwdLastSet -d $domain

# Decouper $obj pour ne recuperer que la chaine correspondant au timestamp
$TimeStamp = $($obj[1] -split " ") | Where-Object {$_ -match '^\d+$'}

# Convertir le timestamp en date avec l'outil w32tm.exe
w32tm.exe /ntte $TimeStamp



Script - Crowdstrike - Exemple d'utilisation du module PSFalcon

Falcon Crowdsrike est l'un des antivirus next generation (EDR) les plus en vue et les plus performants du marché.  Le script ci-dessous propose un exemple de requetage de l'API de Crowdstrike via l'utilisation du module Powershell PSFalcon (https://www.powershellgallery.com/packages/PSFalcon)

L'acces et l'utilisation de l'Api Crowdstrike requiert bien sur un abonnement Crowdstrike (https://www.crowdstrike.com/)

Le script utilisant pour l'acces a l'API, une clé d'encryptage (AES.key), un fichier contenant le SecID (SecID.txt), et un fichier contenant la passphrase (pass.txt), il est necessaire de recreer ces éléments avec le contexte de l'abonnement auquel on se connecte (SecID).

Le script interroge l'API pour recuperer des éléments tel que les hotes, les comportements (Behaviors), les detections, les incidents.

 

QueryCrowdstrike.ps1 (11,62 kb)

 

##############################################
### QUERY CROWDSTRIKE WITH PSFALCON MODULE ###
##############################################


<# 

    .SYNOPSIS 
        INTERROGATION D'UN TENANT FALCON CROWDSTRIKE VIA L'UTILISATION DU MODULE POWERSHELL PSFALCON
        NB: LES DONNEES SONT EXTRAITES SOUS FORME D'UN FICHIERS CSV 

    .PARAMETER  
        ClientID : Client ID crowdstrike
        Pass : Password du compte d'acces a l'API
        
        ExportFolder : Dossier d'export du fichier CSV
        LogFolder : Chemin du dossier où creer le log du script

 
    .EXAMPLE 
     .\QueryCrowdstrike.ps1 -CloudUrl "https://api.eu-1.crowdstrike.com"  -ClientID <clientid> -Pass <pass> -ExportFolder ./ -LogFolder ./
#>


[CmdletBinding()]
param(
[Parameter(Mandatory,HelpMessage="Cloud URL")]
$CloudUrl,

[Parameter(HelpMessage="Client ID crowdstrike")]
$ClientID,

[Parameter(HelpMessage="Password du compte d'acces a l'API")]
$Pass,

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

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

[Parameter(Mandatory,HelpMessage="Chemin de la clé utilisée pour l'encryption du CID et de la passphrase")] 
$KeyFile = "D:\MyFolder\CROWDSTRIKE\AES.key",

[Parameter(Mandatory,HelpMessage="Chemin du fichier contenant le SecID du client")] 
$SecIDFile = "D:\MyFolder\CROWDSTRIKE\SecID.txt",

[Parameter(Mandatory,HelpMessage="Chemin du fichier contenant la passphrase")] 
$PassFile = "D:\MyFolder\CROWDSTRIKE\Pass.txt"


)


# TO RENEW KEY FILE USED FOR CLIENT ID AND PASSPHRASE ENCRYPTION
<#
    # Generate Key File
    $Key = New-Object Byte[] 16   # You can use 16, 24, or 32 for AES
    [Security.Cryptography.RNGCryptoServiceProvider]::Create().GetBytes($Key)
    $Key | out-file $KeyFile

#>


# TO RENEW SECID ENCRYPTION, EXECUTE THE CODE BETWEEN <# #>

<#
    # Create secure string object
    $SecIDFile = "D:\MyFolder\CROWDSTRIKE\SecID.txt"
    $Key = Get-Content $KeyFile
    $SecID = read-host  -Prompt "Enter client SecID" -AsSecureString
    $SecID | ConvertFrom-SecureString -key $Key | Out-File $SecIDFile

#>


# TO RENEW PASSPHRASE ENCRYPTION, EXECUTE THE CODE BETWEEN <# #>

<#
    # Create secure string object
    $PassFile = "D:\MyFolder\CROWDSTRIKE\Pass.txt"
    $Key = Get-Content $KeyFile
    $Pass = read-host  -Prompt "Enter client PassPhrase" -AsSecureString
    $Pass | ConvertFrom-SecureString -key $Key | Out-File $PassFile

#>






# OTHER VARIABLES
# Script Name
$ScriptName = "QueryCrowdstrike.ps1"
# LogName = ScriptName without extension
$Log = $ScriptName.Split('.')[0]


# FUNCTIONS


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 
    } 
} 



            




# Requis si l'import du module echoue en en renvoyant plusieurs messages 'Unable to find [System.Net.Http.(...)*]' 
Add-Type -AssemblyName System.Net.Http 

# Import Module
Import-Module -Name D:\MyFolder\CROWDSTRIKE\Powershell_Module\psfalcon-master\PSFalcon.psm1



$Key = Get-Content $KeyFile

# Get file content and Decrypt ClientID
$ClientID = Get-Content $SecIDFile | ConvertTo-SecureString -Key $key
$ClientID = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($ClientID))

# Get file content and Decrypt Pass
$Pass = Get-Content $PassFile | ConvertTo-SecureString -Key $key
$Pass = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Pass))







# Query for Falcon Token
try
    {
    Request-FalconToken -ClientId $ClientID -ClientSecret $Pass -Hostname $CloudUrl
    }
catch
    {
    $Message = "Error during Request-FalconToken - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }


# Test if Test-FalconToken return True
if ($(Test-FalconToken).token -eq $true)  
      {
      $Message = "Token is valid - OK"
      write-host -F Green $Message
      Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
      }
Else
    {
    $Message = "Problem with Token - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }




# GET DATA

#region Falcon Hosts 

# Get Falcon Hosts (exemple option -filter:  –Filter "hostname:'SRV'")
try
    {
    $FalconHosts = Get-FalconHost -All -Detailed
    #$FalconHosts | select hostname,os_version,agent_version,status | ft -AutoSize
    }
catch
    {
    $Message = "Problem during Get-FalconHost - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }


# Convert To CSV
 try
    {
    $FalconHostsCSV = $($FalconHosts | select `
        hostname,
        os_version,
        platform_name,
        status,
        agent_version,
        config_id_base,
        config_id_build,
        @{Name='First_Seen';Expression={[datetime]::ParseExact($_.first_seen.Replace('T','').Replace('Z',''),'yyyy-MM-ddHH:mm:ss',$null)}},
        @{Name='Last_Seen';Expression={[datetime]::ParseExact($_.last_seen.Replace('T','').Replace('Z',''),'yyyy-MM-ddHH:mm:ss',$null)}},
        reduced_functionality_mode | ConvertTo-Csv -Delimiter ',' -NoTypeInformation).Replace('"','')
    
    # Display CSV File
    $FalconHostsCSV
    }
catch
    {
    $Message = "Error during convert to CSV file for FalconHosts - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }

# Export CSV
 try
    {   
    $FalconHostsCSV | Out-File -FilePath "$ExportFolder`/FalconHosts.csv" -Force
    }
catch
    {
    $Message = "Error during export of CSV file for FalconHosts - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }


#endregion Falcon Hosts 







#region Falcon Detection




#Get-FalconDetection
write-host "FALCON DETECTION EVENTS" -B White -f Blue
try
    {
    $FalconDetection = Get-FalconDetection -Detailed -All  
    $FalconDetection | sort created_timestamp -Descending
    }
catch
    {
    $Message = "Error during Get-FalconDetection - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }


# Convert To CSV
 try
    {
    $FalconDetectionCSV = $($FalconDetection | select `
    detection_id,
    @{Name='Detection_Creation_Date';Expression={[datetime]::ParseExact($_[0].created_timestamp.split('.')[0].Replace('T',''),'yyyy-MM-ddHH:mm:ss',$null)}},
    @{Name='HostName';Expression={$_.device.hostname}},
    @{Name='HostIP';Expression={$_.device.local_ip}},
    @{Name='DeviceID';Expression={$_.device.device_id}},
    @{Name='BehaviorID';Expression={$_.Behaviors.Behavior_id}},
    @{Name='Behavior_FileName';Expression={$_.Behaviors.filename}},
    @{Name='Behavior_FilePath';Expression={$_.Behaviors.filepath}},
    @{Name='Behavior_cmdline';Expression={$_.Behaviors.cmdline}},
    status,
    max_severity_displayname,
    email_sent | ConvertTo-Csv -Delimiter ',' -NoTypeInformation).Replace('"','')
    }
catch
    {
    $Message = "Error during convert to CSV file for FalconDetection - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }

# Export CSV
 try
    {   
    $FalconDetectionCSV | Out-File -FilePath "$ExportFolder`/FalconDetection.csv"
    }
catch
    {
    $Message = "Error during export of CSV file for FalconDetection - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }




#endregion Falcon Detection




#region Falcon Behavior

# Get-FalconBehavior (NB: Transform TimeStamp to more readable format with [datetime]::ParseExact)
write-host "FALCON BEHAVIOR EVENTS" -B White -f Blue

try
    {
    $FalconBehavior = Get-FalconBehavior | foreach {Get-FalconBehavior -Ids $_} | Select -property  behavior_id,display_name,objective,@{Name='BehaviorDate';Expression={[datetime]::ParseExact($_.timestamp.Replace('T','').replace('Z',''),'yyyy-MM-ddHH:mm:ss',$null)}},cmdline,filepath,user_name,incident_id,tactic_id,tactic,technique_id,technique | sort BehaviorDate -Descending
    $FalconBehavior
    }
catch
    {
    $Message = "Error during Get-FalconBehavior - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }


# Convert To CSV
 try
    {
    $FalconBehaviorCSV = $($FalconBehavior | select BehaviorDate,cmdline,filepath,user_name | ConvertTo-Csv -Delimiter ';' -NoTypeInformation).Replace('"','')
    }
catch
    {
    $Message = "Error during convert to CSV file for FalconBehavior - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }

# Export CSV
 try
    {   
    $FalconBehaviorCSV | Out-File -FilePath "$ExportFolder`/FalconBehavior.csv"
    }
catch
    {
    $Message = "Error during export of CSV file for FalconBehavior - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }



#endregion Falcon Behavior





#region Falcon Incident

write-host "FALCON INCIDENTS EVENTS" -B White -f Blue
try
    {
    $FalconIncidents= Get-FalconIncident -Detailed -All  
    $FalconIncidents | sort created -Descending
    }
catch
    {
    $Message = "Error during Get-FalconIncident - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }


# Convert To CSV
 try
    {
    $FalconIncidentsCSV = $($FalconIncidents | select `
    incident_id,
    @{Name='Creation_Date';Expression={[datetime]::ParseExact($_.created.Replace('T','').Replace('Z',''),'yyyy-MM-ddHH:mm:ss',$null)}},
    state,
    email_state  | ConvertTo-Csv -Delimiter ',' -NoTypeInformation).Replace('"','')



        

    }
catch
    {
    $Message = "Error during convert to CSV file for FalconIncident - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }

# Export CSV
 try
    {   
    $FalconIncidentsCSV | Out-File -FilePath "$ExportFolder`/FalconIncidents.csv"
    }
catch
    {
    $Message = "Error during export of CSV file for FalconIncidents - END OF SCRIPT"
    write-host -F Red $Message
    Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
    EXIT 1
    }




#endregion Falcon Incident

 

Power BI Report Server - Script - Export de dashboards en PBIX

Le script ci-dessous prend en paramètre un fichier contenant une liste de dashboard a exporter au format pbix.

 

#####################################################################
###### PwBI_Export_Dashboards.ps1 - EXPORT DE RAPPORTS EN PBIX ######
#####################################################################


<# 

    .SYNOPSIS 
        CONNEXION A L'API SQL POWERBI REPORTING SERVICES ET EXPORT DE UN OU PLUSIEURS DASHBOARD
 

    .PARAMETER  
        webServiceUrl : Url Racine du serveur de rapport
        CatalogUrl : Url du catalogue des items
        DashboardListFile : Fichier des noms de dashboard a exporter
        BackupFolder : Dossier d'export des dashboards
        LogFolder : Chemin du dossier où creer le log du script

 
    .EXAMPLE 
        .\PwBI_Export_Dashboards.ps1 -webServiceUrl "http://MyServer/Reports"  -DashboardListFile ".\PwBI_Export_Dashboards_List.txt" -BackupFolder ".\BACKUP" -LogFolder ".\BACKUP" 
     
#>


[CmdletBinding()]
param(
[Parameter(Mandatory,HelpMessage="Url Racine du serveur de rapport")]
[string]$webServiceUrl,

[Parameter(Mandatory=$false,HelpMessage="Url du catalogue des items")]
[string]$CatalogUrl = "$webServiceUrl/api/v2.0/CatalogItems",


[Parameter(Mandatory,
HelpMessage="Fichier des noms de dashboard a exporter")]
[ValidateScript({
if( -Not ($_ | Test-Path) ){
                throw "File does not exist"
            }
            return $true 
})]
[string]$DashboardListFile,

[Parameter(Mandatory,HelpMessage="Dossier d'export des dashboards")]
[string]$BackupFolder,

[Parameter(Mandatory,HelpMessage="Chemin du dossier où creer le log du script")]
[string]$LogFolder = "D:\Indicateurs_Securité"

)




# GET DASHBOARD LIST CONTENT
[array]$DashboardList = Get-Content -Path $DashboardListFile



# SCRIPT NAME
$ScriptName = "PwBI_Export_Dashboards.ps1"

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

# GET CREDENTIALS TO CONNECT TO REPORT SERVER
$cred = $(Get-Credential -Credential "$env:USERDOMAIN\$env:USERNAME")



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 
    } 
} 




# TABLEAU DES CATALOG ITEMS
$Message = "Recuperation du catalogue des items..."
write-host $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log


            try
            {
            $CatalogItems = Invoke-RestMethod -Uri $CatalogUrl -ContentType 'application/json' -UseDefaultCredentials -Method get
            }
            catch
            {
            $Message = "Error during query of Catalog Items"
            Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
            Write-Host -F Yellow $Message
            exit 1
            }



# COMPARAISON DE $CatalogItems ET $ReportList POUR DETERMINER LA LISTE DES DASHBOARDS A EXPORTER

$FoundReport = $CatalogItems.value | Where-Object {$_.name -in $DashboardList}

if (!($FoundReport))
{
$Message = "The required Dashboards have not been found in the catalog of the report server - END OF SCRIPT"
write-host -f Yellow $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
Exit 1
}



# EXPORT DES DASHBOARDS
try
{

    foreach ($Report in $FoundReport)
    {
    $url = "$CatalogUrl($($Report.Id))/Content/`$value"
    Invoke-WebRequest -UseDefaultCredentials -Uri $url -OutFile "$BackupFolder\$($Report.name).pbix"
    }

}
catch
{
$Message = "KO - The required Dashboards have not been found in the catalog of the report server - END OF SCRIPT"
write-host -f Yellow $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
Exit 1
}


$Message = "OK - The required Dashboards have been Exported to $BackupFolder - END OF SCRIPT"
write-host -f Green $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
Exit 0



 

Power BI Report Server - Script - Execution d'un plan de refresh d'un rapport

Le script ci-dessous se connecte sur l'api de Reporting Services (Power BI) pour executer le rafraichissement des données d'un rapport spécifique.

 

###### PWBI_REFRESH_PLAN.PS1 ######

<# 

    .SYNOPSIS 
        CONNEXION A L'API SQL POWERBI REPORTING SERVICES ET EXECUTION DU PLAN DE RAFRAICHISSEMENT D'UN RAPPORT SPECIFIQUE
 

    .PARAMETER  
        webServiceUrl : Url Racine du serveur de rapport
        CatalogUrl : Url du catalogue des items
        ReportFolderPath : Chemin du dossier contenant le rapport a rafraichir (NB: apres la racine du serveur de rapport)
        ReportName : Nom du dashboard a rafraichir
        RefreshPlan : nom du plan de Refresh
        LogFolder : Chemin du dossier où creer le log du script

 
    .EXAMPLE 
        .\PwBI_Refresh_Plan.ps1 -webServiceUrl "http://MyServer/Reports" -ReportFolderPath "/MySpecFolder/" -ReportName "MySpecDashboard" -RefreshPlan "Refresh_Every_2H" -LogFolder "C:\Temp"   
     
#>


[CmdletBinding()]
param(
[Parameter(Mandatory,HelpMessage="Url Racine du serveur de rapport")]
[string]$webServiceUrl,

[Parameter(Mandatory=$false,HelpMessage="Url du catalogue des items")]
[string]$CatalogUrl = "$webServiceUrl/api/v2.0/CatalogItems",

[Parameter(Mandatory,HelpMessage="Chemin du dossier contenant le rapport a rafraichir (NB: apres la racine du serveur de rapport)")]
[string]$ReportFolderPath,

[Parameter(Mandatory,HelpMessage="Nom du dashboard a rafraichir")]
[string]$ReportName,

[Parameter(Mandatory,HelpMessage="nom du plan de Refresh")]
[string]$RefreshPlan,

[Parameter(Mandatory,HelpMessage="Chemin du dossier où creer le log du script")]
[string]$LogFolder = "D:\Indicateurs_Securité"

)

# FULL REPORT PATH
$FullPath = "`'$ReportFolderPath$ReportName`'"  # Backtick to escape "'" character.

# REFRESH PLAN URL
$RefreshPlanUrl = "$webServiceUrl/api/v2.0/PowerBIReports(Path=$FullPath)/CacheRefreshPlans"


# SCRIPT NAME
$ScriptName = "PwBI_Refresh_Plan.ps1"

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

# GET CREDENTIALS TO CONNECT TO REPORT SERVER
$cred = $(Get-Credential -Credential "$env:USERDOMAIN\$env:USERNAME")



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 
    } 
} 



            




# TABLEAU DES CATALOG ITEMS
$Message = "Recuperation du catalogue des items..."
write-host $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log


            try
            {
            $CatalogItems = Invoke-RestMethod -Uri $CatalogUrl -ContentType 'application/json' -UseDefaultCredentials -Method get
            }
            catch
            {
            $Message = "Error during query of Catalog Items"
            Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
            Write-Host -F Yellow $Message
            exit 1
            }




# RECUPERATION DU RAPPORT SPECIFIQUE
$Message = "Recuperation du rapport $ReportName"
write-host $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log


            $CatalogItem = $CatalogItems.value | Where-Object Name -eq $ReportName
            if (!($CatalogItem))
            {
            $Message = "Unable to find $ReportName"
            Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
            Write-Host -F Yellow $Message
            exit 1
            }
 


# RECUPERATION DU PLAN DE RAFRAICHISSEMENT
$Message = "Recuperation du plan de rafraichissement"
write-host $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log


            try
            {
            $refreshplans = 
            
            Invoke-RestMethod -Uri $RefreshPlanUrl -Method get -UseDefaultCredentials
            
            }
            catch
            {
            $Message = "Error during query of Refresh Plan"
            Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
            Write-Host -F Yellow $Message
            exit 1
            }



# RECUPERATION DE L'ID DU PLAN DE RAFRAICHISSEMENT
$refreshplanId = $($refreshplans | Where-Object {$_.value.Description -eq $refreshplan}).value.Id




# EXECUTION DU PLAN DE RAFRAICHISSEMENT
$Message = "Execution du plan de rafraichissement"
write-host $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log


            try
            {
            
            Invoke-RestMethod -Uri "$webServiceUrl/api/v2.0/CacheRefreshPlans($refreshplanId)/Model.Execute" -Method post -UseDefaultCredentials
            
            }
            catch
            {
            $Message = "Error during execution of Refresh Plan"
            Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
            Write-Host -F Yellow $Message
            exit 1
            }

 

Script - API Vmware VCenter v7 - Get VM infos

Une mise a jour du script proposé récemment, utilisant la nouvelle version de l'API de Vcenter.

 

### QUERY VCENTER REST API (v7) TO GET VM LIST ###


$user = ‘myaccount’
$pswd = Read-Host -Prompt "Enter Password"
$vCenterName = ‘MyVcenter’
$encoded = [System.Text.Encoding]::UTF8.GetBytes(($user, $pswd -Join ‘:’))
$encodedPassword = [System.Convert]::ToBase64String($Encoded)
$authHeader = @{
Authorization = "Basic $($EncodedPassword)"
}
$sRest = @{
Method = ‘Post’
Uri = "https://$($vCenterName)/api/session"
Headers = $authHeader
}
$result = Invoke-RestMethod @sRest


# Get TokenID
$authHeader = @{
‘vmware-api-session-id’ = $result.value
}


# Get All Hosts
$gethosts = "https://$($vCenterName)/api/vcenter/host"
$resultgethost = Invoke-RestMethod -Uri $gethosts -Headers $authHeader
$hostidlist = $resultgethost.value.host


# For each host, get all VMs

foreach ($hostid in $hostidlist)
{
$get_vm = "https://$($vCenterName)/rest/vcenter/vm?filter.hosts=$hostid"
$resultvm = Invoke-RestMethod -Uri $get_vm -Headers $authHeader
[array]$FinalTableau += $resultvm
}

# Display Result
$FinalTableau.value | select name,power_state


# Output to CSV
$CsvTab = $FinalTableau.value | select name,power_state | ConvertTo-Csv -Delimiter ';' -NoTypeInformation
$CsvTab | Out-File .\VMList.csv

 

 

SCCM - SQL Query - 'Last Enforcement State' pour tout les déploiements/computers.

La requete SQL ci-dessous liste l'état d'application (Enforcement) pour toutes les machines et les deploiements correspondant.

 

/*** SCCM - QUERY TO DISPLAY ALL COMPUTERS 'LAST ENFORCEMENT STATE' FOR ALL DEPLOYMENTS  ***/


DECLARE @StartDate DATETIME, @EndDate DATETIME
	
-- Since 12 months
SET @StartDate = DATEADD(mm, -12,GETDATE())
SET @EndDate = GETDATE() 


SELECT

vrs.Name0,

CASE vrs.Active0
	WHEN 0 THEN 'NO'
	WHEN 1 THEN 'YES'
	END AS Client_Active,

a.AssignmentName as DeploymentName,
a.StartTime as Deploy_Available,
a.EnforcementDeadline as Deploy_Deadline,
sn.StateName as LastEnforcementState,
wsus.LastErrorCode as 'LasErrorCode'


FROM v_CIAssignment a
JOIN v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
JOIN v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
JOIN v_R_System vrs on vrs.ResourceID = assc.ResourceID
JOIN v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID
JOIN v_UpdateScanStatus wsus on wsus.ResourceID = assc.ResourceID


AND CreationTime BETWEEN @StartDate AND @EndDate -- IN A SPECIFIC TIME WINDOW FOR THE DEPLOYMENT CREATION TIME

ORDER BY Deploy_Available DESC

 

 

SCCM - SQL Query - Inventaire Software, Executables et AddRemove Programs

La requête ci-dessous fait un 'UNION' entre trois requêtes sur les vues [v_GS_INSTALLED_SOFTWARE_CATEGORIZED], [v_GS_INSTALLED_EXECUTABLE] et [v_ADD_REMOVE_PROGRAMS] pour obtenir un inventaire logiciel un peu plus "avancé".

N.B: Modifiez la variable @CollectionID si besoin pour réduire le scope des machines concernées, et éviter un nombre conséquent de ligne renvoyées.

 

/* --- SOFTWARES, EXECUTABLES, AND ADD-REMOVE PROGRAMS ENTRIES INVENTORY ---  
 NB: UNION ENTRE LES VUES:
	 [v_GS_INSTALLED_SOFTWARE_CATEGORIZED] 
	 [v_GS_INSTALLED_EXECUTABLE]
	 [v_ADD_REMOVE_PROGRAMS]
	 
	       
 NB: DES COLONNES SONT CREES ET POSITIONNEE A NULL POUR POUVOIR EFFECTUER LE UNION. (Meme nombre de colonnes) 

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'
*/



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


SELECT  DISTINCT 

TAB.ResourceID
,TAB.ProductCode
,TAB.[Machine Name]
,TAB.OS_Name
,TAB.Publisher0
,TAB.NormalizedPublisher
,TAB.ExecutableName
,TAB.ProductName
,TAB.AddRemove_Name
,TAB.NormalizedName
,TAB.FamilyName
,TAB.CategoryName
,TAB.InstallDate
,TAB.Soft_Autostart
,TAB.EXE_FilePath
,TAB.EXE_Description
,TAB.ProductVersion
,TAB.EXE_FileVersion



 FROM (


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

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

-- SOFT INFO --
SOFT.Publisher0,
SOFT.NormalizedPublisher,
NULL as ExecutableName,
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
,NULL as NormalizedPublisher
,EXE.ExecutableName0 as ExecutableName
,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)
 
 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
,NULL as NormalizedPublisher
,NULL as ProductName
,NULL as ExecutableName
,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)
INNER JOIN [dbo].[v_GS_OPERATING_SYSTEM] OS on OS.ResourceID = SYST.ResourceID  

WHERE v_FullCollectionMembership.CollectionID = @CollectionID 
  
  ) AS TAB