MECM check for all updates that are required but not deploy SQL Query

Recently we had an issues where some software updates where missed during regular patching cycle, I wanted to have an automated report to list all updates that where required on more than one system but no deployed.

First I checked the Microsoft SQL views for software updates

https://docs.microsoft.com/en-us/configmgr/develop/core/understand/sqlviews/software-updates-views-configuration-manager

The three tables I used where,

V_UpdateComplianceStatus: This table is used to the get status code for updates. Status of 2 means the update is required.

v_UpdateInfo: This table is used to get information on each update

https://docs.microsoft.com/en-us/configmgr/develop/core/understand/sqlviews/software-updates-views-configuration-manager#v_updateinfo

v_Update_ComplianceSummary: This table is used to get compliance of each update to see how many devices are missing the updates.

https://docs.microsoft.com/en-us/configmgr/develop/core/understand/sqlviews/software-updates-views-configuration-manager#v_update_compliancesummary

select distinct
UI.DatePosted as ‘Release Date’,
UI.articleid as ‘ArticalID’,
UI.Title,
Update_Required=(case when UCS.Status=2 then ‘Yes’ end),
CS.NumMissing,
Updates_Deployed=(case when UI.IsDeployed=0 then ‘NotDeployed’ end),
UI.InfoURL as InformationURL

from V_UpdateComplianceStatus UCS
join v_UpdateInfo UI on UI.CI_ID=UCS.CI_ID
join v_Update_ComplianceSummary CS on CS.CI_ID=UCS.CI_ID
WHERE UCS.Status=2 and UI.IsDeployed=0
order by NumMissing desc

Below is what the query return looks like in SQL Management Studio.missing1To confirm the query is working we can create a  search filter for all software updates to make sure they match.missing2Last step was to create a weekly email report using SQL Server Reporting Services (SSRS).

To create a new report (MECM reporting point role will need to be installed for this) go to Monitoring > Reporting > Reports.missing3I created a custom Reports folder to keep any report I created.

Right click on reports and click create reportmissing4

Give the report a name and a folder path the report will be stored. missing5Follow the report wizard to finish the report creation. missing6missing7Once the report wizard completed the report builder should load. missing8First create a new dataset. Right click on Datasets > Add Datasetsmissing9

Give the Dataset a name, Use a dataset embedded in my report, Data source and select the MECM data source and query type as text. Last part is to copy the query to the query field.missing10Once that click ok and the dataset should show under datasets. missing11

Next we create a table for the report view. missing12Select the dataset we create.missing13I just wanted to use all values so drag all row to the value box.missing14Next just go through the rest of the wizard. Once completed you should see something similar to the below. I  added a text box at the top for a tile and added in some grey filling on the table. missing16Now save the report and we can run from the MECM console to see what the report will look like. missing17Last step is to create subscription to send a the weekly report missing18Select report delivered by Email and put in the required detail and report to be included.(Email settings need to be configured in SSRS before you can select email as a deliver option)missing19Set the schedule that is required and complete the wizard. missing20The report should now be sent out weekly as a csv file.

 

 

Windows Admin Centre 1910

I have previously looked at Window Admin centre (WAC) tech preview and wanted to configure the latest full release, to see what changes have been made and do a proof of concept to see if this will be useful if it is deployed to our production environment.

PowerShell version 5.1 is required on servers that will be added to WAC and will need Window Management Framework (WMF) 5.1 installed, some will require additional steps also. See list below for required pre-req before they can be managed in WAC.

https://www.microsoft.com/en-us/download/details.aspx?id=54616

Windows Admin Centre uses SMB for some file copying tasks, such as when importing a certificate on a remote servers.

Once all pre-req are confirmed, we can go ahead and download Windows Admin centre.

https://www.microsoft.com/en-us/evalcenter/evaluate-windows-admin-centerhttps://docs.microsoft.com/en-us/windows-server/manage/windows-admin-center/overview#download-now

Once the MSI is downloaded run and following the installation Admin1Accept the terms.Admin2Admin3

I used the default settings but you can enable WinRM over https if you have certificates that have server authentication set. If you want to look at configuring WinRM over https see following link: https://support.microsoft.com/en-us/help/2019527/how-to-configure-winrm-for-httpsAdmin4

I will use default port 443 and generate a self signed cert, if you have a certificate authority you can generate a cert that can be used for gateway. Admin5Admin6Admin7

Once the install is completed you can access the Windows Admin Centre URL using https://servername.

Admin8To add a server click Add. Admin9Admin10Click search active directory and put in the server name. Admin11

Once the server is added it will show in the admin console. To connect click on the server and connect. Admin12When connecting to servers, to allow single sign on there is an additional command that need’s to be run. If this is not done you will see the below warning when connecting.

Admin13

I updated the command to run on all servers in AD, as I didn’t want to have to run on each individually.

$Servers = Get-ADComputer -Filter “OperatingSystem -like ‘Windows Server*'”
$WAC = “LAB-WAC”
Foreach ($Server in $Servers){
Set-ADComputer -Identity $Server -PrincipalsAllowedToDelegateToAccount (Get-ADComputer $WAC)
}

Admin16The above command adds the admin server to the below attribute. Admin14Once this has been done when you click connect it should now use SSO. Admin15

We can now manage the servers from a central console without having to logon individually to each server. In the next post we will go through the different options and settings in Windows Admin Centre.

Configure Azure ATP

In this post we are going to go through configuring Azure Advanced Threat Protection (Azure ATP), ATP  is a cloud tool that can be used to detect security issue with on-premises active directory.

https://azure.microsoft.com/en-us/features/azure-advanced-threat-protection/#security

Azure ATP is available as part of Enterprise Mobility + Security 5 suite (EMS E5), and as a standalone license. You can acquire a license directly from the Microsoft 365 portal or through the Cloud Solution Partner (CSP) licensing model.

Azure ATP uses agent sensor installed on domain controllers or as standalone deployment that used port mirroring to monitor and analyzes user activities and information across your network.

We will be using the agents in this post since I can installing software on my DC if you have a policy that restrict agents installed on DC then you can use standalone deployment.

To configure Azure ATP first step it to setup an ATP workspace.

Go to https://portal.atp.azure.com/ and use your Azure AD logonATP0Once the workspace has been created we need to configure the on-premises connection account. ATP1Put in details for the domain account, I used a service account that has read access to AD so that I could restricted the logon to the account to only be used for ATP ATP2Once AD details are added next step is to download and install the sensor agent. Go to sensors and download the setup exe and copy the access key as this will be required during the install. ATP3Copy the installer to the DC and run. ATP4Select required language. ATP5Deployment type will be sensor I am running on VMware and this will generate the below warning which will redirect to a guide to disable IPv4 TSO Offload. ATP6This is where we need to put in the access key so that the sensor is associated to the correct workspace. ATP7The agent should now start to install. ATP8ATP9Once the agent has install it can take some time for it to respond in Azure ATP.ATP10Now the agent should start to notify of any suspicious activity that run’s like reconnaissance on SMB,  DNS and other malicious reconnaissance.

We can also create HoneyToken account that will be dummy account that can be used to attracted attackers and will automatically generated an alert as they should never be used.

Go to Entity tags >Honeytoken and select the account that will be used as the HoneytokenATP13We can also add sensitive account and group, there are default groups/ accounts that are considered sensitive by default like Administrators, Domain admins, Enterprise admins…, a full list of these can be found in the below link.

https://docs.microsoft.com/en-us/azure-advanced-threat-protection/sensitive-accountsATP14If we now logon with the HoneyToken account it will then alert the activity by this account. ATP15We can also run reports or schedule reports for schedule reports. To run a report go to the reporting blade and change the date to the range you want and click download. ATP16Below is an example of the modification to sensitive groups. ATP17To scheduled reports go to configuration and scheduled reports. ATP18ATP19

 

 

Check Scheduled task for run as account PowerShell

Recently I needed to check for all scheduled task running on servers using a certain account as the password needed to be updated.  To search for these scheduled task I used schtask.exe

https://docs.microsoft.com/en-us/windows/win32/taskschd/schtasks

I wanted to integrate this with PowerShell so that I could do some connection tests, loop through a list of devices and output the results to CSV.  Below is the script I created it has three mandatory parameters for the computer list, run as account and export path.

## Set script parameter
param(
[parameter(Mandatory = $true)]
[String]$CompList,
[parameter(Mandatory = $true)]
[String]$RunAsAccount,
[parameter(Mandatory = $true)]
[String]$ExportLocation
)
## Get list of device to check
$comps = Get-Content $CompList

## Loop through each device
foreach ($comp in $Comps){
Write-Host “Testing connection to $($comp)” -ForegroundColor DarkGreen
$TC = Test-Connection $comp -Count 1 -ErrorAction SilentlyContinue

if ($TC){
Write-Host “Checkig $($comp)” -ForegroundColor Green

## Check scheduled task for specified run as account
$schtask = schtasks.exe /query /V /S $comp /FO CSV | ConvertFrom-Csv | Select-Object HostName,TaskName,Status,”Next Run Time”,”Run As User” |
Where-Object {$_.”Run As User” -contains $RunAsAccount}
if ($schtask){

## Export results
Write-Host “Task found exporting to results to $($ExportLocation)”
$schtask | Export-Csv “$ExportLocation\ScheduledTaskExport.csv” -NoTypeInformation -Append
}
else {
Write-Host “No task found with run as account”
}

}
else {
Write-Host “$($comp) not responding Exporting failures to log file located in $($ExportLocation)” -ForegroundColor Yellow
$comp | Out-File “$ExportLocation\FailureReport.log” -NoTypeInformation -Append
}
}

To run the script supple the three parameter like below. I used Check_RunAsAccount as the scrip name.

.\Check_RunAsAccount.ps1 -CompList D:\Scripts\Task_Scheduler\Complist.txt -RunAsAccount test1 -ExportLocation D:\Scripts\Task_Scheduler\

SCk1SCk4Once the script has completed there will be a result csv and if there are any non responding devices there will be a second csv.SCk5The export result will look like the below. SCk2

 

Configure Azure Arc for servers

Azure Arc for servers is a tool that can be used to add on-prem physical / virtual servers or servers running in other cloud providers to Azure, this allow these servers to be centrally organised and to be connected to Azure services like Azure policy or log analytics.

Azure Arc for servers is in public preview, so this should not be used on production system until there is a full release version. Currently the only support OS version’s are below.

  • Windows Server 2012 R2 and newer
  • Ubuntu 16.04 and 18.04

If there is a content filter or proxy, the URL’s in the below link will  need to be white-listed all traffic will go over HTTPS (Port 443)

https://docs.microsoft.com/en-us/azure/azure-arc/servers/overview#networking-configuration

To add a server to Azure Arc, logon to the Azure portal.

Go to All services > Machines – Azure ArcArc13

Click on create machine – Azure ArcArc1There are two options add machines using interactive script or add machines at scale. In this post we will be using the scripted method. Arc2

Select the subscription, resource group and region. When selecting  the operating system we can chose between Windows or Linux. Windows will create a PowerShell script and Linux will create a Linux .sh script.Arc3

As this feature is currently preview it will ask you to register before completing the download. Arc4Arc5Once the registration is completed, we can either copy the command or click download to get a copy of the PowerShell script. Below is how the script should look the only difference would be the Azure specific setting like resource group, tenantid…Arc6Once the script has run and the connect command has completed it will request that we connect to the device login site https://Microsoft.com/devicelogin and put in the authentication code. Arc7Arc8

Once completed go back to the PowerShell windows that the script was run from and we should see successfully onboarded to Azure resource.Arc9After a few minutes the device should show in Azure Arc. Arc10We can now apply Azure policies .Arc11

There are some additional steps required to configure the servers to integrate with Azure log analytics.

First step is to install the MMA (Microsoft Monitoring Agent) agent, to download the agent go to Log Analytics workspace and select the require workspace > Advanced settings

Arc14

Select the required connection source, then OS version. We will also need to take note of the workspace ID and Primary Key which will be used during the agent install to connect to the log workspace.Arc15

Go through the agent install.Arc16

Select Azure connect the agent to Azure Log Analytics (OMS).Arc22

To connect to Azure log analytics put in the workspace id and key.Arc18

Once the install has finished we can check the agent in control panel > All Control Panel Items > Microsoft monitoring agent.Arc20

We should now be able to query log from the server in Azure Arc for servers. Arc21

 

Remove WINS & Disable Netbios over Tcpip PowerShell

As part of  a recent decommission / security audit, we needed to remove an old WINS server. For desktop client this is fairly easy as they are all assigned through DHCP so it was just a case of removing WINS from the DHCP scope options.

For the hundreds of servers it is set manually, which to remove one by one would take a long time and be pretty boring for the person tasked with it. So I decided the simplest option would be to use PowerShell.

First I wanted to check if servers had WINS enabled so I could reduce the amount of server I would need to run the disable script against.

I am going to use Get-WmiObject and the Win32_NetworkAdapterConfiguration class as this is the simplest way I found to do this in PowerShell.

We will use a text file with a list of server names and a variable called $WINSServer that will be used to filter only network interfaces that have WINS set.

Below is the script to check for WINS and output to PowerShell windows I am just getting all adapters that have WINSPriamryServer value set to the IP in the $WINSServer variable and then selecting the objects to be outputted.

If you wanted to export to a csv or text file just add a | after the WINSPrimaryServer at the end of the script and do either Out-file or Export-csv and the path to export too. 

Below is the script to check WINS:

$comps = Get-Content “C:\Temp\complist.txt”
$WINSServer = “192.168.0.2”
foreach ($comp in $comps){
Write-Warning “Checking $comp”
$NETBios = Get-WmiObject -ComputerName $comp -Class Win32_NetworkAdapterConfiguration -Filter “WINSPrimaryServer=’$WINSServer'”
foreach($net in $NETBios){
$net | Select-Object @{N=”ComputerName”;E={$comp}},IPAddress,DefaultIPGateway,IPSubnet,WINSPrimaryServer
}
}

WINSTo remove the WINS IP and set NetBios option, we will use the set method in the WMI classs.

Below is the script:

$comps = Get-Content “C:\Temp\complist.txt”
$WINSServer = “192.168.0.2”
foreach ($comp in $comps){
Write-Warning “Checking $comp”
$NETBios = Get-WmiObject -ComputerName $comp -Class Win32_NetworkAdapterConfiguration -Filter “WINSPrimaryServer=’$WINSServer'”
foreach($net in $NETBios){
Write-Warning “WINS currently set to $($net.WINSPrimaryServer) on $comp ”
Write-Warning “Removing WINS and Disabling NetBios on Interface $($net.InterfaceIndex) with IP:$($net.IPAddress)”
$NETBios.SetWINSServer(“$Null”,”$Null”) | Out-Null
$NETBios.SetTcpipNetbios(“2”) | Out-Null
}
}

WINS01Once the script has run WINS should be removed and NetBios over Tcpip should be disabled this can be checked under the advanced properties on the NIC.

WINS02

Remove RDS Profile from AD PowerShell

I was having a issue with slow logon times and temporary profile when users where logging on to an Windows RDS 2012 farm. I had a look at the issue and it was down to the RDS profile path in AD being set to use an old decommissioned server.RDS1

Once I found the issue, I need to figure out how many users where affected so the easiest way I could do this was to use PowerShell.

Below is the report script that I used.  The distinguished name used in Get-ADUser -searchbase will need to be update and the export path.  (Test before running any script and also check the quotes and doubles quotes when copying) 

## Get list of Users
$RDUsers = Get-aduser -SearchBase “OU=TestUsers,OU=Users,DC=Domain,DC=Local” -Filter *

## Set Results Array
$Results = @()

foreach ($user in $RDUsers){
$RD = [ADSI]”LDAP://$($user.DistinguishedName)”
if ($RD.Properties.Contains(“userParameters”)){

$profilepath = $RD.psbase.Invokeget(“terminalservicesprofilepath”)
$profileHome = $RD.psbase.Invokeget(“terminalServicesHomeDirectory”)
$props = @{
UserName = $user.SamAccountName
RDSProfile = $profilepath
RDSHome = $profileHome
DistinguishedName = $user.DistinguishedName
}

$Results += New-Object psobject -Property $props
}

else {
Write-Host “No UserParameters set on” $user.SamAccountName -ForegroundColor Green
}
}
$Results | Export-Csv C:\Temp\Logs\RDSProfile.csv -NoTypeInformation

RDS2

Once the script has completed the results will be export to a CSV with all user and there profile pathsRDS3Once we have the list we can either remove manually or the better option use the CSV and remove the profiles using the invokeset method.

Below is the script I used. The script could be run against all AD users but I prefer to limit the amount of object I have to run against. (This will replace values on users so should be fully tested before apply to large amount of users.)

$RDProfile = Import-Csv -Path C:\Temp\Logs\RDSProfile.csv
foreach ($RDU in $RDProfile){
Write-Warning “Removing Profile from $($RDU.UserName)”
$RD = [ADSI]”LDAP://$($RDU.DistinguishedName)”
$RD.psbase.Invokeset(“terminalservicesprofilepath”,”$null”)
$RD.psbase.Invokeset(“TerminalServicesHomeDirectory”,”$null”)
$RD.setinfo()
}

RDS4

After the script has run the profiles should now be cleared.RDS5

Bulk add and remove Office 365 Licences

I recently had a to move around a few thousand EMS licences to enable MFA for Office 365 and Azure, I decided to do two quick scripts to remove and add back the licences to the required users. I thought I would do a quick post on how I moved the licences.

As always any scripts should be tested on a subset of users before running on larger groups to test that they work as expected.

For this script we need the Office365 PowerShell module installed.

To check if the module is installed run

Get-Module -ListAvailable MSOnlineBulkAdd

First step is to get the AccountSKU to do this run

Import-Module MSonline and then Connect-MsolServiceBulkAdd2

Get-MsolAccountSku | Select-Object AccountSkuIdBulkAdd3

To make things easier and more repeatable in case I need to remove or add other licence I am using Out-GridView -PassThru to select the CSV file and also the licence SKU.

First Out-GridView is for the Csv file with UserPrincipalName (UPN)BulkAdd4

The second is to select the SKU to be removedBulkAdd5

Once the two items are selected the script will then runBulkAdd6

The full remove licence script is below. The only part that needs to be updated is the $csv variable to point to the correct folder where the csv files will be kept.
## Bulk Remove EMS licences ##
## Select Csv file
$csv = Get-ChildItem -Path “C:\temp\Office365Licence\Remove\Csv\” -File | Out-GridView -PassThru
## Import Csv
$users = Import-Csv $csv.FullName
## Select Account SKU to be removed
$accountSKU  = Get-MsolAccountSku | Select-Object AccountSkuId | Out-GridView -PassThru
## Loop through each user in the Csv
foreach($user in $users){
Write-Host “Removing $($accountSKU.AccountSkuId) licence from $($user.UserPrincipalName)” -ForegroundColor Yellow
## Remove licence
Set-MsolUserLicense -UserPrincipalName $user.UserPrincipalName -RemoveLicenses $licenceSKU
}

The add script is the same only I added a check to confirm if the user requires the licence. The only part that needs to be updated is the $csv variable to point to the correct folder where the csv files will be kept.

Just a note on this I was applying the licence to existing users who where already setup with a usage location so if this is not set the script will error out. 

## Bulk Add EMS licences ##
## Select Csv file
$csv = Get-ChildItem -Path “C:\temp\Office365Licence\Remove\Csv\” -File | Out-GridView -PassThru
## Import Csv
$users = Import-Csv $csv.FullName
## Select Account SKU to be added
$accountSKU  = Get-MsolAccountSku | Select-Object AccountSkuId | Out-GridView -PassThru
## Loop through each user in the Csv
foreach ($user in $users) {
## Check if Licence is already applied
$EMS = Get-MsolUser -UserPrincipalName $user.UserPrincipalName | Select-Object UserPrincipalName, Licenses
if ($EMS.Licenses.AccountSkuId -notcontains “$accountSKU”){
## Add licence
Write-Warning “Adding EMS licnece from $($user.UserPrincipalName)”
Set-MsolUserLicense -userprincipalname $user.UserPrincipalName -AddLicenses $accountSKU
}
else
{
## Licence already applied
Write-Host “$($user.UserPrincipalName) has $($accountSKU) licence assigned” -ForegroundColor Green
}
}

HPE Gen 10 SUM Integrated Smart Update Tools VMware

I was installing some new ESXi host using HPE Gen 10 blade servers and was getting a warning when trying to update the firmware using HPE SPP (Service pack for proliant). The issue is due to HPE changing from installing updates directly to the ESXi OS level and instead using HPE ILO.

HPE03

If iSUT is not installed the update will be staged on the host but wont install. To install, download iSUT from the HPE Support site. Below is the link to version 2.3.6 which was the version I used. ISUT_Tool

HPE15Once download and extracted we need to copy the file to the ESXi host, the easiest way to copy is by enabling SSH on the host and using WinSCP.     HPE04I created a folder called hpe_isut on the ESXi host to copy the file to. HPE05Once the files are copied over, use either SSH or ESXi Shell to install the tools. I used SSH with putty as it was easier.

To install use esxcli command, this is the command I used.

esxcli software vib install -d /hpe_sut/sut-esxi6.0-bundle-2.3.6.0-16.zipHPE06Once the install has completed, a restart is required to finish the install. After the reboot the next step is to set the iSUT mode there are 4 <OnDemand/AutoStage/AutoDeploy/AutodeployReboot>

I chose AutoDeploy, to set the mode use the below command.

sut –set mode=AutoDeployHPE09

Once this has completed, run the inventory again from the SPP and the warning should now be gone and the firmware and driver updates should now apply. HPE10

HPE14

MBAM Policy Error code: -2147217402 on Windows 10 1903

I was installing MBAM 2.5 SP1 client on Windows 10 1903 during SCCM task sequence. Once the device was built and the user tried to put in a pin and start the encryption it would fail.

The below error was showing in the MBAM event logs. fail1We currently install MBAM successfully on Windows 1809 so it didn’t look like a MBAM server issue or connection issue. I checked and there was a newer servicing release from May 2019

https://support.microsoft.com/en-us/help/4505175/may-2019-servicing-release-for-microsoft-desktop-optimization-pack

This update adds support for Windows 10 1903. I applied this update and can now encrypt successfully.