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