PI Services

La requête SQL ci-dessous propose un inventaire avancé des patchs de mise a jour installés ou requis sur les machines cibles.

Les variables @StartDate et @EndDate sont utilisées pour limiter la date de création du patch.

	/* PATCH REQUIRED OR INSTALLED STATUS FOR ALL SERVERS WITH SOFTWARE UPDATE GROUP INFO */
	DECLARE @Collection varchar(8)
	DECLARE @StartDate DATETIME, @EndDate DATETIME
	SET @Collection = 'SMS00001' -- Collection ID for All Systems
	-- Since 1 months
	SET @StartDate = DATEADD(mm, -1,GETDATE())
	SET @EndDate = GETDATE() 
	
	SELECT DISTINCT  VRS.Name0 as 'MachineName',
	Os.Caption0 as 'OperatingSystem',
	CASE
		WHEN summ.ClientActiveStatus = 0 THEN 'Inactive' 
		WHEN summ.ClientActiveStatus = 1 THEN 'Active' 
		end as 'ClientActiveStatus',
	CASE 
		WHEN LastHealthEvaluationResult = 1 THEN 'Not Yet Evaluated' 
		WHEN LastHealthEvaluationResult = 2 THEN 'Not Applicable'
		WHEN LastHealthEvaluationResult = 3 THEN 'Evaluation Failed' 
		WHEN LastHealthEvaluationResult = 4 THEN 'Evaluated Remediated Failed' 
		WHEN LastHealthEvaluationResult = 5 THEN 'Not Evaluated Dependency Failed' 
		WHEN LastHealthEvaluationResult = 6 THEN 'Evaluated Remediated Succeeded'
		WHEN LastHealthEvaluationResult = 7 THEN 'Evaluation Succeeded'
		end as 'Last Health Evaluation Result',
	CASE 
		WHEN LastEvaluationHealthy = 1 THEN 'Pass'
		WHEN LastEvaluationHealthy = 2 THEN 'Fail'
		WHEN LastEvaluationHealthy = 3 THEN 'Unknown'
		end as 'Last Evaluation Healthy',
	CASE 
		WHEN summ.ClientRemediationSuccess = 1 THEN 'Pass' 
		WHEN summ.ClientRemediationSuccess = 2 THEN 'Fail' ELSE '' 
		end as 'ClientRemediationSuccess',
	
	
	UI.Title as 'Title',
	UI.ArticleID as 'ArticleID',
	UI.BulletinID as 'BulletinID',
	UI.DatePosted,
	UI.DateCreated,
	UI.DateLastModified,
	
	CASE UI.Severity
		WHEN 0 THEN 'None'
		WHEN 2 THEN 'Low'
		WHEN 6 THEN 'Moderate'
		WHEN 8 THEN 'Important'
		WHEN 10 THEN 'Critical'
		end as Patch_Severity,

        CASE UI.IsExpired 
		WHEN 0 THEN 'NOT EXPIRED' 
		WHEN 1 THEN 'EXPIRED' 
		END AS [EXPIRY],
	
	CASE UI.IsSuperseded 
		WHEN 0 THEN 'NOT Superseded' 
		WHEN 1 THEN 'Superseded' 
		END AS [SUPERSEDENCE],


	CASE 
		WHEN UCS.Status = 2 THEN 'Required'
		WHEN UCS.Status = 3 THEN 'Installed'
		ELSE 'Unknown' END AS 'KBStatus',
	UI.InfoURL as 'InformationURL', 
	KB_AND_SUG.Title as SoftwareUpdateGroup,

	
	CASE 
		WHEN USS.LastScanState = 0 THEN 'unknown'
		WHEN USS.LastScanState = 1 THEN 'waiting for catalog location'
		WHEN USS.LastScanState = 2 THEN 'running'
		WHEN USS.LastScanState = 3 THEN 'completed'
		WHEN USS.LastScanState = 4 THEN 'pending retry'
		WHEN USS.LastScanState = 5 THEN 'failed'
		WHEN USS.LastScanState = 6 THEN 'completed with errors'
		END as LastScanState,

	USS.ScanTime,
	USS.LastScanTime,
	USS.LastErrorCode,
	USS.LastStatusMessageID
	
	FROM v_R_System VRS
	INNER JOIN v_UpdateComplianceStatus UCS ON UCS.ResourceID = VRS.ResourceID
	INNER JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
	INNER JOIN v_UpdateScanStatus USS on VRS.ResourceID = USS.ResourceID
	INNER JOIN v_CICategories_All CIC ON UI.CI_ID = CIC.CI_ID
	
	
	INNER JOIN v_CH_ClientSummary summ on Vrs.ResourceID = summ.ResourceID
	INNER JOIN v_GS_OPERATING_SYSTEM Os on UCS.ResourceID = Os.ResourceID
	INNER JOIN Computer_System_DATA St on UCS.ResourceID = st.MachineID
	INNER JOIN v_FullCollectionMembership Col on UCS.ResourceID = Col.ResourceID
	
/* JOINTURE POUR OBTENIR L'INFO DU SOFTWARE UPDATE GROUP AUQUEL APPARTIENS LE PATCH */
INNER JOIN (SELECT DISTINCT
		upd.ArticleID,
		AL.Title
		FROM vSMS_CIRelation as cr
		INNER JOIN fn_ListUpdateCIs(1033) upd ON  upd.CI_ID = cr.ToCIID AND cr.RelationType = 1
		INNER JOIN v_CIToContent CC ON cc.CI_ID=upd.CI_ID
		INNER JOIN v_AuthListInfo AL ON al.CI_ID =cr.FromCIID
		) KB_AND_SUG on UI.ArticleID = KB_AND_SUG.ArticleID
		
	--WHERE VRS.Operating_System_Name_and0 like '%Server%'
	AND Col.CollectionID = @Collection
	AND DateCreated BETWEEN @StartDate AND @EndDate
	--AND CategoryInstanceName = 'Security Updates' -- Only Security Updates
	
	ORDER BY DateCreated DESC
	

Le script ci-dessous est un exemple simple de l'utilisation de l'API Rest de Vcenter pour recuperer toute sorte d'information, nottament sur les machines virtuelles.

A noter que la limitation du nombre d'objet retournés oblige simplement a recuperer dans un premier temps la liste des hosts, puis par host la liste des VMs.

### QUERY VCENTER REST API 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)/rest/com/vmware/cis/session"
Headers = $authHeader
}
$result = Invoke-RestMethod @sRest


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


# Get All Hosts
$gethosts = "https://$($vCenterName)/rest/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

 

Ci-dessous, une série de requêtes SQL sur les déploiements, les assignations et les mise a jours.

/**** QUERIES: DEPLOYMENTS,ASSIGNMENTS,UPDATES ****/


--- LIST OF DEPLOYMENTS NAME
SELECT 
--DeploymentID = cia.Assignment_UniqueID,
DeploymentName = isnull(grp.Title+' - ', '') + cia.AssignmentName
FROM [dbo].[vCI_CIAssignments]  CIA
LEFT JOIN [dbo].[vCI_AssignmentTargetedGroups]  ATG on CIA.AssignmentType=5 and ATG.AssignmentID=CIA.AssignmentID
LEFT JOIN [dbo].[v_AuthListInfo] GRP on CIA.AssignmentType=5 and GRP.CI_ID=ATG.AssignedUpdateGroup
WHERE CIA.AssignmentType in (1,5)




--- LIST OF ASSIGNMENTS NAME WITH RELATED DEPLOYMENTS NAME
SELECT 
--AssignmentID,
CIA.AssignmentName,
--,DEPLOY.DeploymentID,
DEPLOY.DeploymentName 

FROM v_CIAssignment CIA
INNER JOIN (
			SELECT DeploymentID = cia.Assignment_UniqueID
			, DeploymentName = isnull(grp.Title+' - ', '') + cia.AssignmentName
			FROM [dbo].[vCI_CIAssignments]  CIA
			LEFT JOIN[dbo].[vCI_AssignmentTargetedGroups]  ATG on CIA.AssignmentType=5 and ATG.AssignmentID=CIA.AssignmentID
			LEFT JOIN [dbo].[v_AuthListInfo] GRP on CIA.AssignmentType=5 and GRP.CI_ID=ATG.AssignedUpdateGroup
			WHERE CIA.AssignmentType in (1,5)

			) DEPLOY on DEPLOY.DeploymentID = CIA.Assignment_UniqueID



---- LIST OF UPDATES 

		SELECT DISTINCT 
		--CI_UniqueID,
		DisplayTitle = ui.Title+isnull(' ('+nullif(ui.ArticleID, '')+')', '') + isnull(' ('+nullif(ui.BulletinID, '') +')', '')
		,DateCreated
		, (CASE ui.Severity 
		WHEN 0 THEN 'NONE' 
		WHEN 2 THEN 'LOW' 
		WHEN 6 THEN 'MODERATE' 
		WHEN 8 THEN 'IMPORTANT' 
		WHEN 10 THEN 'CRITICAL' END) AS SEVERITY
		FROM v_UpdateInfo UI
		
		ORDER BY DateCreated DESC

        

---- LIST OF UPDATES AND RELATED ASSIGNMENTS NAME

		SELECT DISTINCT 
		--CI_UniqueID,
		DisplayTitle = ui.Title+isnull(' ('+nullif(ui.ArticleID, '')+')', '') + isnull(' ('+nullif(ui.BulletinID, '') +')', '')
		,DateCreated
		,(CASE ui.Severity 
			WHEN 0 THEN 'NONE' 
			WHEN 2 THEN 'LOW' 
			WHEN 6 THEN 'MODERATE' 
			WHEN 8 THEN 'IMPORTANT' 
			WHEN 10 THEN 'CRITICAL' END) AS SEVERITY
		,CIA.AssignmentName
		FROM v_CIAssignment CIA
		INNER JOIN v_CIAssignmentToCI  ATC on ATC.AssignmentID=CIA.AssignmentID
		INNER JOIN v_UpdateInfo UI on UI.CI_ID=ATC.CI_ID

		ORDER BY DateCreated DESC
		
		



---- LIST OF UPDATES AND RELATED DEPLOYMENTS NAME

		SELECT 
		--CI_UniqueID,
		DisplayTitle = ui.Title+isnull(' ('+nullif(ui.ArticleID, '')+')', '') + isnull(' ('+nullif(ui.BulletinID, '') +')', '')
		,UI.DateCreated
		,(CASE ui.Severity 
			WHEN 0 THEN 'NONE' 
			WHEN 2 THEN 'LOW' 
			WHEN 6 THEN 'MODERATE' 
			WHEN 8 THEN 'IMPORTANT' 
			WHEN 10 THEN 'CRITICAL' END) AS SEVERITY
		,DEPLOY.DeploymentName
		FROM v_CIAssignment CIA
		INNER JOIN v_CIAssignmentToCI  ATC on ATC.AssignmentID=CIA.AssignmentID
		INNER JOIN v_UpdateInfo UI on UI.CI_ID=ATC.CI_ID
		INNER JOIN (
					SELECT DeploymentID = cia.Assignment_UniqueID
					, DeploymentName = isnull(grp.Title+' - ', '') + cia.AssignmentName
					FROM [dbo].[vCI_CIAssignments]  CIA
					LEFT JOIN [dbo].[vCI_AssignmentTargetedGroups]  ATG on CIA.AssignmentType=5 and ATG.AssignmentID=CIA.AssignmentID
					LEFT JOIN [dbo].[v_AuthListInfo] GRP on CIA.AssignmentType=5 and GRP.CI_ID=ATG.AssignedUpdateGroup
					WHERE CIA.AssignmentType in (1,5)

					) DEPLOY on DEPLOY.DeploymentID = CIA.Assignment_UniqueID
			
		ORDER BY DateCreated DESC

	

 

Le script ci-dessous propose d'utiliser a distance la commande "PccNTMon.exe -u" sur un agent Trend OSC pour invoquer une mise a jour, en direction de son serveur OSC de rattachement.

#### RemoteInvokeTrendOSCAgentUpdate.ps1: REMOTELY INITIATE AN UPDATE OF A TREND OSC AGENT ####

## COMMENT: The script execute command through PSSession and open a RDP window to allow interactive use of "OSCAgentExe -u" ###

param(
# Target Computer
$Computer = "MyTarget",
# Cred
$Cred = $(Get-Credential -Credential "MyAccount"),
# OSC Agent Folder
$OSCAgentFolder = "C:\Program Files (x86)\Trend Micro\OfficeScan Client\",
# OSC Client Process
$OSCClientProc = "PccNTMon"
)


# OSC Agent Exe
$OSCAgentExe = "$OSCAgentFolder$OSCClientProc`.exe"


# FUNCTIONS
function Set-WindowState {
	
	[CmdletBinding(DefaultParameterSetName = 'InputObject')]
	param(
		[Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true)]
		[Object[]] $InputObject,

		[Parameter(Position = 1)]
		[ValidateSet('FORCEMINIMIZE', 'HIDE', 'MAXIMIZE', 'MINIMIZE', 'RESTORE',
					 'SHOW', 'SHOWDEFAULT', 'SHOWMAXIMIZED', 'SHOWMINIMIZED',
					 'SHOWMINNOACTIVE', 'SHOWNA', 'SHOWNOACTIVATE', 'SHOWNORMAL')]
		[string] $State = 'SHOW'
	)

	Begin {
		$WindowStates = @{
			'FORCEMINIMIZE'		= 11
			'HIDE'				= 0
			'MAXIMIZE'			= 3
			'MINIMIZE'			= 6
			'RESTORE'			= 9
			'SHOW'				= 5
			'SHOWDEFAULT'		= 10
			'SHOWMAXIMIZED'		= 3
			'SHOWMINIMIZED'		= 2
			'SHOWMINNOACTIVE'	= 7
			'SHOWNA'			= 8
			'SHOWNOACTIVATE'	= 4
			'SHOWNORMAL'		= 1
		}

		$Win32ShowWindowAsync = Add-Type -MemberDefinition @'
[DllImport("user32.dll")]
public static extern bool ShowWindowAsync(IntPtr hWnd, int nCmdShow);
'@ -Name "Win32ShowWindowAsync" -Namespace Win32Functions -PassThru

		if (!$global:MainWindowHandles) {
			$global:MainWindowHandles = @{ }
		}
	}

	Process {
		foreach ($process in $InputObject) {
			if ($process.MainWindowHandle -eq 0) {
				if ($global:MainWindowHandles.ContainsKey($process.Id)) {
					$handle = $global:MainWindowHandles[$process.Id]
				} else {
					Write-Error "Main Window handle is '0'"
					continue
				}
			} else {
				$global:handle = $process.MainWindowHandle
			}

			$Win32ShowWindowAsync::ShowWindowAsync($handle, $WindowStates[$State]) | Out-Null
			Write-Verbose ("Set Window State '{1} on '{0}'" -f $handle, $State)
		}
	}
}




# FOR EACH COMPUTER
foreach ($Comp in $Computer) {


# Create PSSession
try
{
New-PSSession -ComputerName $Comp -Credential $Cred -Name "PSSession_$Comp" -ErrorAction Stop
}
catch
{
write-host -F Red "Error during New-PSSession `n $($Error[0].Exception)"
exit 1 
}


# Get PSsession
$RemoteSession = Get-PSSession -Name "PSSession_$Comp"


# Open RDP Session (needed because the invoke update is interactive)
cmdkey /generic:TERMSRV/$Comp /user:($Cred.UserName) /pass:($Cred.GetNetworkCredential().Password)

mstsc /v:$Comp /w:50 /h:50

Write-Host "Wait for 10 sec..."
Start-Sleep -Seconds 10


# Get the RDP process Id to kill it at the end
$ProcId = (Get-Process mstsc | Where-Object {$_.MainWindowTitle -like "*$Comp*"}).Id


# Minimize RDP Window
(Get-Process mstsc | Where-Object {$_.MainWindowTitle -like "*$Comp*"}) | foreach {Set-WindowState $_ HIDE}


Write-Host "Wait for 10 sec..."
Start-Sleep -Seconds 10



# Invoke Update
write-host "Invoking update..."
Invoke-Command -Session $RemoteSession -ScriptBlock {Start-Process -FilePath $Using:OSCAgentExe -ArgumentList "-u"} 


        # Wait while update (see for update of ofcscan.ini file)
        do {
            Write-Host "Wait while update..."
           }

        until

           (
            (Invoke-Command -Session $RemoteSession -ScriptBlock {Get-ChildItem -Path "$Using:OSCAgentFolder`ofcscan.ini" | Where-Object {$_.LastWriteTime -gt $(get-date).AddMinutes(-15)}})
           )


Write-Host "Wait for 10 sec..."
Start-Sleep -Seconds 10



# Close RDP Session
write-host "Logoff RDP Session on $Comp..."
Invoke-Command -Session $RemoteSession -ScriptBlock {`
    $CredUser = $($Using:Cred.UserName)
    $sessions = quser | Where-Object {$_ -match $CredUser} ; 
    $sessionIds = ($sessions -split ' +')[2] ; 
    Write-Host "Found $(@($sessionIds).Count) user login(s) on computer.";
    $sessionIds | ForEach-Object {Write-Host "Logging off session id [$($_)]..." ; logoff $_}
     
     }


# Close RDP Session Disconnect Window by killing process
Get-Process -Id $ProcId | stop-process


}

# Remove Pssession
Get-PSSession | Remove-PSSession

Le script SQL ci-dessous propose de lister les détails de l'état et la configuration des antivirus Defender pour des machines gérés par SCCM.

/****** ALL WORKSTATIONS ENDPOINT PROTECTION DETAILS  ******/

	SELECT DISTINCT (S.ResourceID)
	,S.Name0 AS 'Machine Name'
	,AD_Site_Name0 AS 'AD Site'
	,S.Operating_System_Name_and0 AS 'Operating System'
	,CASE [EpProtected]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Protected'
	,CASE [EpAtRisk]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Computer at Risk'
	,CASE [EpNotYetInstalled]
		WHEN 1 THEN 'NOT INSTALLED'
		WHEN 0 THEN 'INSTALLED'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Installed'
	,CASE [EpUnsupported]
		WHEN 1 THEN 'UNSUPPORTED'
		WHEN 0 THEN 'SUPPORTED'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Support'
	,CASE[Inactive]
		WHEN 1 THEN 'INACTIVE'
		WHEN 0 THEN 'ACTIVE'
		ELSE 'UNKNOWN' 
		END AS 'SCCM Client Activity'
	,CASE[NotClient]
		WHEN 1 THEN 'NO'
		WHEN 0 THEN 'YES'
		ELSE 'UNKNOWN' 
		END AS 'SCCM Client'
	,CASE [AmRemediationFailed]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'AntiMalware Failed Remediation'
	,CASE [AmFullscanRequired]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'AntiMalware Full Scan Required'
	,CASE [AmRestartRequired]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'AntiMalware Restart Required'
	,CASE [AmOfflineScanRequired]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'AntiMalware Offline Scan Required'
	,CASE [AmManualStepsRequired]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'AntiMalware Manual Scan Required'
	,CASE [AmRecentlyCleaned]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'AntiMalware Recently Cleaned'
	,CASE [AmThreatActivity]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'AntiMalware Threat Activity'
	,CASE [EpInstallFailed]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Failed Install'
	,CASE [EpEnforcementSucceeded]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Enforce Succeed'
	,CASE [EpEnforcementFailed]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Enforce Failed'
	,CASE [EpPendingReboot]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Pending Reboot'
	,CASE [Unhealthy]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Unhealthy'
	,CASE [SignatureUpTo1DayOld]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Signature Age 1 day old'
	,CASE [SignatureUpTo3DaysOld]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Signature Age 3 day old'
	,CASE [SignatureUpTo7DaysOld]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Signature Age 7 day old'
	,CASE [SignatureOlderThan7Days]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Signature Age over 7 day old'
	,CASE [NoSignature]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'No Signature'
	,CASE [AmPending]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'AntiMalware Pending'
	,CASE [LastScanUpTo2DaysOld]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Last Scan 2 days old'
	,CASE [LastScanUpTo8DaysOld]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Last Scan 8 days old'
	,CASE [LastScanUpTo31DaysOld]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Last Scan 31 days old'
	,CASE [LastScanOver31DaysOld]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Last Scan Over 31 days old'
	,CASE [Healthy]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Healthy'
	,CASE [Active]
		WHEN 1 THEN 'ACTIVE'
		WHEN 0 THEN 'INACTIVE'
		ELSE 'UNKNOWN' 
		END AS 'Client Activity'
	,CASE [EpUnmanaged]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Not Managed'
	,CASE [EpToBeInstalled]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint To be installed'
	,CASE [EpManaged]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Managed'
	,CASE [EpInstalled]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Managed'
	,CASE [EpEnforced]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Enforced'
	,CASE [EpEnabled]
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Endpoint Enabled'
	
	,CASE AMSH.Enabled 
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'AntiMalware_Enabled'
	,AMSH.Version as AntiMalware_Version
	--,AMSH.ProductStatus
	,CASE AMSH.RtpEnabled
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'RealTime_Ptct_Enabled'
	,CASE AMSH.OnAccessProtectionEnabled -- Specifies whether the computer is monitoring file and program activity on your computer
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'OnAccess_Ptct_Enabled'
	,CASE AMSH.IoavProtectionEnabled -- Scan all downloaded files and attachments
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Downloaded_Ptct_Enabled'
	,CASE AMSH.BehaviorMonitorEnabled
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Behavior_Monitor_Enabled'
	,CASE AMSH.AntivirusEnabled
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Antivirus_Enabled'
	,CASE AMSH.AntispywareEnabled
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
		ELSE 'UNKNOWN' 
		END AS 'Antispyware_Enabled'
	,AMSH.EngineVersion
	,AMSH.LastQuickScanDateTimeStart as 'Last QuickScan DateTime Start'
	,AMSH.LastQuickScanDateTimeEnd as 'Last QuickScan DateTime End'
	,AMSH.LastFullScanDateTimeStart as 'Last FullScan DateTime Start'
	,AMSH.LastFullScanDateTimeEnd as 'Last FullScan DateTime End'
	,AMSH.LastFullScanAge as 'Last FullScan Age'
	,AMSH.LastQuickScanAge as 'Last Quick Scan Age'
	,AMSH.AntivirusSignatureUpdateDateTime as 'Antivirus Signature Update DateTime'
	,AMSH.AntiSpywareSignatureUpdateDateTime as 'AntiSpyware Signature Update DateTime'
	,AMSH.AntivirusSignatureAge as 'Antivirus Signature Age'
	,AMSH.AntispywareSignatureAge as 'Antispyware Signature Age'
	,AMSH.AntivirusSignatureVersion as 'Antivirus Signature Version'
	,AMSH.AntispywareSignatureVersion as 'Anti spyware Signature Version'
	
	FROM [CM_BIM].[dbo].[v_EndpointProtectionStatus] EPPS /*(v_EndpointProtectionStatus: Fournit un résumé de l'état des clients Endpoint Protection global pour chaque ordinateur)*/
	INNER JOIN v_R_System S on S.ResourceID = EPPS.ResourceID
	INNER JOIN v_GS_AntimalwareHealthStatus AMSH on AMSH.ResourceID = EPPS.ResourceID /*(v_GS_AntimalwareHealthStatus:  Most recent snapshot of the AntimalwareHealthStatus WMI class for each client where EndPoint Protection is installed)*/
	

Pour rappel, Defender Security Center représente la brique et le portail dédié associé a Defender for Endpoint, et représente l’offre SAS de Microsoft dans le domaine des EDR (Endpoint Detection and Response), encore appelé antivirus de nouvelle génération.

Dans le cadre de l’exploitation des données remontées par la solution, il peut être utile de collecter des données de diagnostique sans avoir a se connecter sur la machine cible.

Dans le portail Security Center, dans la zone Device Inventory, cliquer sur le device cible pour ouvrir la page du device.

Cliquer sur et sélectionner Collect investigation package. La demande est initiée.

Apres quelques minutes, dans le même menu, sélectionner Action Center pour afficher la disponibilité du package

Cliquer sur le lien Package collection package available pour télécharger le package.

Le package contiens différents éléments:

- Programmes installés

- Diagnostiques réseaux ()

- Une liste un dump du contenu du dossier Windows\Prefetch (cf: Prefetcher — Wikipédia (wikipedia.org))

- Le détail des processes en cours

- les taches planifiées

- un export de l’eventlog Security

- Le détails des services

- les session SMB

- les infos du systeme (systeminfo.exe)

- les listes de contenu de differents dossier Temporaires

- Les users et groupes locaux

- La collecte de diagnostiques générée avec l’outil mpcmdrun.exe  (cf: Utiliser la ligne de commande pour gérer l’Antivirus Microsoft Defender - Windows security | Microsoft Docs ;  Collecter des données de diagnostic de l’Antivirus Microsoft Defender - Windows security | Microsoft Docs)

Dans Power BI, l’info du dernier rafraichissement d’un dashboard ne fait pas l’objet d’une fonctionnalité directe identifiée comme telle.

Il s’agit d’une info bien sur importante. Pour l’implémenter, la procédure n’est pas très compliquée.

Dans l’editeur de requete, creer une nouvelle requete vide.

Renommer la requête avec un nom explicite.

Cliquer sur Advanced Editor pour éditer la nouvelle requête.

Ecrire la requete comme ci-dessus (Source = #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()}})

La valeur de la date et heure actuelle s’affiche.

Valider en cliquant Close & Apply

Sur la page du dashboard dans la liste des champs (Fields) Faite un clic droit et selectionner New measure

Dans la barre éditeur de la mesure, tapez le texte ci-dessus (Last Refreshed = VALUES(‘Date Last Refreshed’[Date Last Refreshed])

la mesure est crée et va pouvoir etre utilisé dans une visualization.

Selectionner une Visualization de type Card

Associer a cette visualization la mesure crée.

Voila. Vous pouvez bien sur changer l’apparence de la visualization (par ex la couleur de fond) dans ses propriétés.

L’objectif est identique à l’intégration via un script local, traité dans un récent article mais dans cet exemple l’ intégration s’effectue en:

- Ajoutant sur la machine cible le compte de l’utilisateur de la machine tel qu’il apparait dans Endpoint Manager

- S’assurant de la configuration du connecteur entre Endpoint Manager et Defender ATP (action à effectuer une seule fois)

Créant un profil de configuration dans Endpoint Manager pour permettre à la machine de s’enregistrer sur Defender ATP.

1. Ajout du compte du user pour inscription dans Windows Intune et découverte du device:

sur le client Windows 10,ouvrir « Access Work or School »

Connect

Renseigner le compte du user O365 et cliquer Next

Renseigner le password du user.

2. Option Intune activée dans Defender Security Center

Dans la console Defender Security Center aller dans Setting /Advanced Features

S’assurer que l’option Microsoft Intune connection est bien On

3. Types d’OS autorisés

Dans la console Endpoint Manager aller sur Endpoint Security / Microsoft Defender ATP

Dans la zone MDM Compliance Policy Settings’, s’assurer que les types d’OS devant etre connecté sont bien autorisés.

4. Creation d’une Configuration Policy dans Endpoint Manager

- Dans la console Endpoint Manager aller sur Devices / By Platform : Windows / Configuration profiles

NB : L’objectif est de créer une/des ‘Configuration Policy’ qui vont permettre aux devices de s’enregistrer.

Des configuration Policies similaires peuvent etre crées pour les autres type de device (Devices / By Platform)

Cliquer Create Profile

Selectionner Windows 10 and later.

Selectionner ‘Microsoft Defender ATP (Windows 10 Desktop)’ puis Create.

Nommer la Policy avec un nom explicite

Cliquer Next

Laisser ces options Not Configured

Dans cette zone il s’agit de sélectionner le scope des devices/users a qui va s’appliquer la policy.

Si un/des groupes crées dans Endpoint Manager auparavant, doivent être sélectionnés, cliquer Select groups groups to include.

Sélectionner d’éventuels groupes à exclure.

Cliquer Next

Selectionner la règle d’application (NB : les règles d’application sont differentes selon les types de devices et profile)

Dans cet exemple on applique le profile SI l’édition de Windows 10 est ‘Windows 10 Professional’

Cliquer Next.

Résumé de la création.

Cliquer Create.

A l’issu de la création un dashboard affiche le statut de l’assignation du profile.

Pour le moment le dashboard affiche 0. Le deploiement est en cours.

NB : Penser à vérifier que le groupe crée au préalable contiens bien des objets auxquels le profil doit être appliqué.

Il est également possible de cliquer sur Properties ( ) pour afficher les propriétés de la policy crée et notamment le/les scopes. (groupes/users)

Contrairement au dashboard, la zone Monitor affiche immédiatement le contenu (ici la machine de test qui est membre du groupe que l’on a associé à la policy (groupe de test ‘MDATP Test’) :

Et l’on peut voir que le champ Deployment Status affiche ‘Pending’ :

Des que la policy est deployée, on retrouvera la machine cible dans les devices “with ATP Sensor”

Et surtout, dans la console Defender Security Center.

Defender for Endpoint, nouveau nom de Defender ATP, représente les fonctionnalités de sécurité avancé permettant de gérer les Endpoint (PC Client, Smartphone..).

Defender for Endpoint est utilisable soit conjointement avec Endpoint Manager (Intune), soit de manière autonome en inscrivant les devices via l’utilisation d’une GPO, d’un déploiement de package SCCM ou encore l’utilisation d’un script local généré depuis la console dédiée a Defender for Endpoint: Defender Security Center.

L’exemple qui suit est celui de l’enrollment d’un client Windows 10 via l’utilisation du script local.

Depuis la console Endpoint Manager, aller sur «Sécurité du point de terminaison » puis « Installation / Microsoft Defender ATP »

Cliquer sur le lien Ouvrir la console d'administration du connecteur Microsoft Defender ATP pour ouvrir la console Defender Security Center.

Aller sur Settings / Device management / Onboarding

Selectionner Windows 10

Selectionner Local Script

Cliquer Download package

Copier le package sur la machine cible

Decompresser l’archive et executer le fichier WindowsDefenderATPLocalOnboardingScript.cmd avec des droits administrateur.

Y

Press any Key

Le service ‘Windows Defender Advanced Threat Protection Service’ (Sense) passe en mode de démarrage automatique.

L’information ci-dessous est logué dans l’eventlog Application

Dans le registre local, la valeur OnboardingState doit être à 1

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Advanced Threat Protection\Status

Afin de tester l’enrollment du client executer la commande ci-dessous dans une fenetre de commande éxécutée en tant qu’administrateur.

powershell.exe -NoExit -ExecutionPolicy Bypass -WindowStyle Hidden $ErrorActionPreference= 'silentlycontinue';(New-Object System.Net.WebClient).DownloadFile('http://127.0.0.1/1.exe', 'C:\\test-MDATP-test\\invoice.exe');Start-Process 'C:\\test-MDATP-test\\invoice.exe'

La fenêtre d’invite de commandes se ferme automatiquement. En cas de succès, le test de détection sera marqué comme terminé et une nouvelle alerte apparaitra quelques minutes plus tard dans la console Defender Security Center.

En paralelle le nouveau client apparait dans la zone Device Inventory de la console Defender Security Center.

En cas d’erreur, consultez le Lien : https://docs.microsoft.com/fr-fr/windows/security/threat-protection/microsoft-defender-atp/troubleshoot-onboarding (rubrique Résoudre les problèmes d’intégration lors du déploiement à l’aide d’un script)

Le script ci-dessous prend en paramètre un compte et un nom de Privilège pour l’attribution des ‘User Rights Assignment’ au niveau de la 'Local Policy' d’une machine.

#### SCRIPT: ADD NEW ACCOUNT IN USER RIGHT ASSIGNMENT OF LOCAL SECURITY POLICY

# SCRIPTNAME : LocPolicyAddUserRight.ps1

# PARAMETERS:
# User: Account to grant
# PrivilegeName : See $AccesList to select right name
# ConfFilesPath : Path to export/import Conf File

# EXAMPLE: LocPolicyAddUserRight.ps1 -User MyDomain\MyAccount -PrivilegeName SeTimeZonePrivilege -ConfFilesPath C:\



Param
(
$User,
$PrivilegeName,
$ConfFilesPath
)



#USER RIGHT ASSIGNMENT LIST

[array]$AccessList = $(
"Access Credential Manager as a trusted caller = SeTrustedCredManAccessPrivilege"
"Access this computer from the network = SeNetworkLogonRight"
"Act as part of the operating system;SeTcbPrivilege"
"Add workstations to domain = SeMachineAccountPrivilege"
"Adjust memory quotas for a process = SeIncreaseQuotaPrivilege"
"Allow log on locally = SeInteractiveLogonRight"
"Allow log on through Remote Desktop Services = SeRemoteInteractiveLogonRight"
"Back up files and directories = SeBackupPrivilege"
"Bypass traverse checking = SeChangeNotifyPrivilege"
"Change the system time = SeSystemtimePrivilege"
"Change the time zone = SeTimeZonePrivilege"
"Create a pagefile = SeCreatePagefilePrivilege"
"Create a token object = SeCreateTokenPrivilege"
"Create global objects = SeCreateGlobalPrivilege"
"Create permanent shared objects = SeCreatePermanentPrivilege"
"Create symbolic links = SeCreateSymbolicLinkPrivilege"
"Debug programs = SeDebugPrivilege"
"Deny access to this computer from the network = SeDenyNetworkLogonRight"
"Deny log on as a batch job = SeDenyBatchLogonRight"
"Deny log on as a service = SeDenyServiceLogonRight"
"Deny log on locally = SeDenyInteractiveLogonRight"
"Deny log on through Remote Desktop Services = SeDenyRemoteInteractiveLogonRight"
"Enable computer and user accounts to be trusted for delegation = SeEnableDelegationPrivilege"
"Force shutdown from a remote system = SeRemoteShutdownPrivilege"
"Generate security audits = SeAuditPrivilege"
"Impersonate a client after authentication = SeImpersonatePrivilege"
"Increase a process working set = SeIncreaseWorkingSetPrivilege"
"Increase scheduling priority = SeIncreaseBasePriorityPrivilege"
"Load and unload device drivers = SeLoadDriverPrivilege"
"Lock pages in memory = SeLockMemoryPrivilege"
"Log on as a batch job = SeBatchLogonRight"
"Log on as a service = SeServiceLogonRight"
"Manage auditing and security log = SeSecurityPrivilege"
"Modify an object label = SeRelabelPrivilege"
"Modify firmware environment values = SeSystemEnvironmentPrivilege"
"Perform volume maintenance tasks = SeManageVolumePrivilege"
"Profile single process = SeProfileSingleProcessPrivilege"
"Profile system performance = SeSystemProfilePrivilege"
"Remove computer from docking station = SeUndockPrivilege"
"Replace a process level token = SeAssignPrimaryTokenPrivilege"
"Restore files and directories = SeRestorePrivilege"
"Shut down the system = SeShutdownPrivilege"
"Synchronize directory service data = SeSyncAgentPrivilege"
"Take ownership of files or other objects = SeTakeOwnershipPrivilege"

)



Function GetUserSID($UserName)
{
(New-Object System.Security.Principal.NTAccount($UserName)).Translate([System.Security.Principal.SecurityIdentifier]).value
}




Function Parse-SecPol($CfgFile){ 
    secedit /export /cfg "$CfgFile" | out-null
    $obj = New-Object psobject
    $index = 0
    $contents = Get-Content $CfgFile -raw
    [regex]::Matches($contents,"(?<=\[)(.*)(?=\])") | %{
        $title = $_
        [regex]::Matches($contents,"(?<=\]).*?((?=\[)|(\Z))", [System.Text.RegularExpressions.RegexOptions]::Singleline)[$index] | %{
            $section = new-object psobject
            $_.value -split "\r\n" | ?{$_.length -gt 0} | %{
                $value = [regex]::Match($_,"(?<=\=).*").value
                $name = [regex]::Match($_,".*(?=\=)").value
                $section | add-member -MemberType NoteProperty -Name $name.tostring().trim() -Value $value.tostring().trim() -ErrorAction SilentlyContinue | out-null
            }
            $obj | Add-Member -MemberType NoteProperty -Name $title -Value $section
        }
        $index += 1
    }
    return $obj
}




Function Set-SecPol($Object, $CfgFile){
   $OldSecPool.psobject.Properties.GetEnumerator() | %{
        "[$($_.Name)]"
        $_.Value | %{
            $_.psobject.Properties.GetEnumerator() | %{
                "$($_.Name)=$($_.Value)"
            }
        }
    } | out-file $CfgFile -ErrorAction Stop
    secedit /configure /db c:\windows\security\local.sdb /cfg "$CfgFile" #| out-null
}



# Export Original Conf
$OldSecPool = Parse-SecPol -CfgFile "$ConfFilesPath`Old.inf"

# Get user SID
try
    {
    $newsid = GetUserSID -UserName $User
    }
catch
    {
    $Message = "'$User' user SID has not been found"
    Write-Host -F Red $Message.ToUpper()
    exit 1 
    }


# 
if ($OldSecPool.'Privilege Rights'.$PrivilegeName -eq $null)
    {
    $Message = "'$PrivilegeName' privilege has not been found. Check Name. `n "
    Write-Host -F Red $Message.ToUpper()
    Write-Host "--- ACCESS RIGHTS NAMES ---:`n"
    $AccessList
    exit 1
    }
Else
    {
    # get original value
    $OldValue = $OldSecPool.'Privilege Rights'.$PrivilegeName
    # Add new SID to the string with required additional characters
    $NewValue = $OldValue+",*$newsid"
    # Set New Value
    $OldSecPool.'Privilege Rights'.$PrivilegeName = $NewValue
    }


$result = Set-SecPol -Object $OldValue -CfgFile "$ConfFilesPath`New.inf" 

if ($result[-2] -like "*successfully*")
    {
    $Message = "OK - '$User' has been granted '$PrivilegeName' `n $($result[-2]) `n $($result[-1])"
    write-host -F Green $Message.ToUpper()
    exit 0
    }

Else
    {
    $Message = "Error during grant of rights"
    write-host -F Red $Message.ToUpper()
    $result
    exit 1
    }