Windows LAPS with Intune

Windows Local Administrator Password Solution (LAPS) has been a crucial tool for securing local administrator accounts in managed Windows devices. With the recent Windows 24H2 update, Microsoft has introduced several enhancements to LAPS, empowering IT administrators with new options for automatic account management and increased flexibility for password security policies. One of the standout improvements is the ability to create managed accounts and configure automatic account management directly from Intune, making it easier than ever to enforce security standards across your devices.

 

Key Features of LAPS in Windows 24H2

With the release of Windows 24H2, Microsoft has made several improvements to LAPS, including the ability to define policies that streamline the management of local administrator accounts through Intune. The primary update is the inclusion of new policies for **Automatic Account Management**, which makes it easier to automate and enforce the creation, management, and maintenance of local administrator accounts. Additionally, administrators can now randomize the account name for an added layer of security.

 

New LAPS Policies in Intune

With these updates, administrators can now leverage the **Configuration Service Provider (CSP)** to define policies for LAPS in Microsoft Intune. Below are the essential CSPs that are now available to configure LAPS policies:

 

1. **`./Device/Vendor/MSFT/LAPS/Policies/AutomaticAccountManagementEnabled`** 

   This policy allows administrators to enable or disable automatic management of the local administrator account.

 

2. **`./Device/Vendor/MSFT/LAPS/Policies/AutomaticAccountManagementEnableAccount`** 

   This policy controls whether or not the local administrator account is automatically created and managed via LAPS.

 

3. **`./Device/Vendor/MSFT/LAPS/Policies/AutomaticAccountManagementNameOrPrefix`** 

   Here, administrators can define a name or prefix for the local administrator account. The flexibility to define custom account names is crucial for organizations with specific naming conventions.

 

4. **`./Device/Vendor/MSFT/LAPS/Policies/AutomaticAccountManagementRandomizeName`** 

   This policy allows the administrator to randomize the local administrator account name, which enhances security by reducing the predictability of the account name.

 

5. **`./Device/Vendor/MSFT/LAPS/Policies/AutomaticAccountManagementTarget`** 

   This policy defines the target device group or specific devices that will be subject to LAPS management.

 

6. **`./Device/Vendor/MSFT/LAPS/Policies/BackupDirectory`** 

   This policy specifies the backup directory for LAPS password storage, ensuring that backup copies of passwords are safely stored for recovery when needed.

 

Configuring LAPS Policies in Intune

To create a new LAPS policy via Intune, administrators can use the aforementioned CSPs in the **Device Configuration** section. These settings can be pushed to managed Windows devices, allowing for centralized control over the local administrator account security.

 

1. **Navigate to Intune > Devices > Configuration Profiles** in the Intune portal.

2. **Create a New Profile** and select **Windows 10 and later** as the platform.

3. **Choose the Profile Type** as **Custom**, and under **OMA-URI Settings**, you can input the appropriate CSPs based on your requirements.

 

Once configured, these settings will be applied to the managed devices, ensuring the local administrator account is automatically created and secured according to the defined policies.

 

Backup and Recovery Considerations

As part of the improved LAPS functionality, administrators are now encouraged to set up a backup directory for storing passwords securely. This ensures that in the event of an emergency or a recovery scenario, administrators can retrieve the local administrator password for troubleshooting and remediation.

 

For more detailed information on configuring backup directories and additional LAPS policy options, refer to the official [Microsoft documentation on LAPS

CSP](https://learn.microsoft.com/en-us/windows/client-management/mdm/laps-csp#policiesbackupdirectory).

 

Conclusion

The introduction of **Automatic Account Management** in Windows LAPS (available in the latest Windows 24H2 update) represents a significant step forward in securing local administrator accounts in a streamlined, automated manner. With the ability to manage account names, randomize credentials, and enforce automatic updates directly through Intune, organizations can enhance their security posture while reducing the administrative overhead of managing these critical accounts.

Automating Email Alerts for Non-Compliant Devices in Intune via PowerShell and Graph API

Managing devices through Microsoft Intune requires efficient tracking of device compliance, and one key task for administrators is to keep users informed about the status of their devices. In some cases, users may have devices that are not compliant with your organization’s policies. To automate the process of notifying these users, you can use the Microsoft Graph API in combination with PowerShell to send an email to users whose devices are non-compliant.

In this blog post, we'll walk through a PowerShell script that pulls non-compliant device data from Intune via the Microsoft Graph API and sends a notification email to the user informing them about the non-compliant status.

Prerequisites:

Before running the script, ensure the following prerequisites are met:

1. Microsoft Graph PowerShell SDK is installed. You can install it with the following command:

                                Install-Module Microsoft.Graph -Scope CurrentUser

2. Permissions: Make sure your account has the necessary Microsoft Graph API permissions to read device data and send emails. The required permissions are:
• DeviceManagementManagedDevices.ReadWrite.All to manage devices.
• User.Read to read user information.
3. SMTP Server Setup: You need access to an SMTP server (in this case, Office 365) to send emails.
4. PowerShell Version: Ensure you are using PowerShell 7.x or a version that supports the required modules.

PowerShell Script to Send Emails to Non-Compliant Users

Here is a simple PowerShell script to get the non-compliant devices and send an email to the respective users:

# Connect to Microsoft Graph

# Connect to Microsoft Graph

Connect-MgGraph -Scopes "DeviceManagementManagedDevices.ReadWrite.All", "User.Read"

 

# Get the list of non-compliant devices

$nonCompliantDevices = Get-MgDeviceManagementManagedDevice -Filter "complianceState eq 'noncompliant'"

 

# Check if we found any non-compliant devices

if ($nonCompliantDevices -eq $null -or $nonCompliantDevices.Count -eq 0) {

    Write-Host "No non-compliant devices found."

    exit

}

 

# Loop through each non-compliant device and send an email

foreach ($device in $nonCompliantDevices) {

    # Display device details in console (for debugging)

    Write-Host "User: $($device.userPrincipalName)"

    Write-Host "Device Name: $($device.deviceName)"

    Write-Host "Compliance State: $($device.complianceState)"

    Write-Host "----------------------------------------------------------"

 

    # Prepare email subject and body

    $subject = "Non-Compliant Device"

    $body = "Device $($device.deviceName) assigned to $($device.userPrincipalName) is not currently compliant."

   

    # Email settings

    $smtpServer = "smtp.office365.com"

    $from = "IntuneAdmin@domain.com"

    $to = $device.userPrincipalName

    $port = 587

 

    # Ensure email address is valid

    if ($to -match "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$") {

        try {

            # Send email using SMTP

            Send-MailMessage -To $to -Subject $subject -Body $body -SmtpServer $smtpServer -From $from -UseSsl -Port $port

            Write-Host "Email sent to: $to"

        } catch {

            Write-Error "Failed to send email to $to. Error: $_"

        }

    } else {

        Write-Host "Invalid email address for user: $($device.userPrincipalName)"

    }

}

 

# Optional: Disconnect from the Graph API after the operation

Disconnect-MgGraph

Useful References:

1. Microsoft Graph PowerShell SDK documentation: https://learn.microsoft.com/en-us/powershell/microsoftgraph/intune/introduction

Conclusion:

By using the Microsoft Graph API and PowerShell, you can easily automate the process of identifying non-compliant devices in Microsoft Intune and sending out notifications to users. This script helps improve compliance management and ensures that users are promptly informed of any issues with their devices.

 

SCCM Audit: 3 Methods to Identify Audit Status

 When managing an SCCM (System Center Configuration Manager) environment, it’s crucial to regularly audit your deployments, installations, and overall system health. Fortunately, SCCM provides various options to identify audit statuses and troubleshoot potential issues. In this blog post, we'll explore three methods you can use to identify the audit status in SCCM: Status Filter Rule, Default Report, and SQL Query. We'll also provide a sample SQL query to help you get the audit status based on Message ID and strings.

1. Using Status Filter

The first method to track audit status is by using the Status Filter in the SCCM console. This option allows you to filter messages based on criteria such as message type, severity, and date.

To use the Status Filter:

• Navigate to Monitoring in the SCCM Console.
• Under System Status, select Status Messages.
• Use the filtering options to narrow down to the specific status you're looking for.

This is a quick and easy way to check the status of various tasks within SCCM, including package deployments and client health.

2. Using Default Report

SCCM also provides a set of default built-in reports, which are useful for auditing purposes. These reports provide visibility into package deployments, software updates, client activity, and more.

To access default reports:

• Go to the Monitoring workspace in the SCCM Console.
• Under Reporting, select Reports.
• Browse the list of available reports and select the one relevant to your audit.

These reports are pre-configured to give you insights into various aspects of your environment, such as package distribution, client status, and software update compliance.

3. Using SQL Query

If you want more flexibility and advanced querying capabilities, SQL queries are a powerful tool. You can directly query the SCCM database to retrieve audit information based on specific message IDs and strings.

Below is an example SQL query to help you get audit status based on the Message ID and Message String:

SELECT *

FROM vStatusMessagesWithStrings

WHERE MessageID = '30000'

AND InsStrValue3 LIKE '%packagename%'

In this query:

• vStatusMessagesWithStrings is the SCCM view that contains status messages along with their associated strings.
• MessageID = '30000' filters the messages for a specific ID, in this case, 30000 (this ID represents a specific status event such as successful package deployment).
• InsStrValue3 LIKE '%packagename%' further narrows the results to only show messages containing the package name.

Microsoft Technet Blog on Status Message IDs

For further information on the different Message IDs in SCCM, you can refer to the Microsoft Technet Blog. The blog provides an extensive list of SCCM Status Message IDs and their titles. This will help you understand what each message ID represents and how you can use them for better auditing.

Here is the link to the Microsoft Technet Blog that contains detailed information on status message IDs.

Conclusion

By using Status Filter, Default Reports, or SQL Queries, you can effectively track and monitor the audit status of your SCCM environment. SQL queries, in particular, offer advanced flexibility for detailed audits. The sample query provided above is just one example, and you can modify it according to your specific needs. For further reference, the Microsoft Technet Blog provides a complete list of status message IDs and their explanations, which will be extremely helpful for identifying and troubleshooting issues within your SCCM environment.

Stay on top of your SCCM environment's health, and leverage these methods for effective auditing and troubleshooting

Windows Update Management from SCCM to Intune

Moving Windows Update Workload from SCCM to Intune and Cleanup GPO Registry Keys for Smooth Intune Update Deployment

As organizations increasingly adopt Intune for modern device management, it becomes necessary to transition certain workloads, such as Windows Update management, from SCCM (System Center Configuration Manager) to Intune. This shift enables you to leverage cloud-based management, offering benefits like simplified administration and greater flexibility.

 

In this blog post, we'll explore how to move the Windows Update workload from SCCM to Intune and use PowerShell scripts to clean up old Group Policy Objects (GPO) registry keys that may interfere with Intune-based update management. Additionally, we will discuss how to use detection and remediation scripts to ensure the devices are ready for Intune update deployment.

 

Steps to Move Windows Update Workload from SCCM to Intune

Before you can move your Windows Update management from SCCM to Intune, you need to follow a few steps to properly set up your environment for Intune-based Windows Update management:

 

1. Disable SCCM Software Update Point (SUP)

The Software Update Point (SUP) role in SCCM manages Windows updates. To transition this responsibility to Intune, the first step is to disable the update management in SCCM.

 

In SCCM, go to Administration > Site Configuration > Sites.

Right-click on the site where the SUP role is installed, and select Remove Roles.

Uncheck the Software Update Point and confirm the removal.

2. Enable Windows Update for Business (WUfB) in Intune

Next, configure Windows Update for Business (WUfB) policies in Intune to manage updates:

 

In the Microsoft Endpoint Manager Admin Center, navigate to Devices > Windows > Update Rings for Windows 10 and later.

Create a new update ring that defines when and how updates are deployed to your devices.

Configure update settings like deferral periods, active hours, and automatic update behavior.

3. Assign Update Rings to Devices

Once the update rings are configured, assign them to relevant groups or devices in your environment. This ensures that Intune will control how and when updates are deployed to these devices.

 

Using PowerShell Scripts for GPO Registry Cleanup

When transitioning to Intune-managed updates, you may need to clean up old Group Policy registry keys set by SCCM, WSUS, or previous GPO configurations. These old registry settings can interfere with Intune’s update management.

 

We will use a PowerShell script for detection and remediation of Windows Update-related registry keys.

 

Detection Script: Check for Existing GPO Registry Settings

This script checks if any Windows Update-related registry keys are still present, indicating that Windows Update is being managed by an old GPO. If found, it will return a non-compliant status.

 

# WU Registry Key Detection Script

$regPath = "HKLM:\Software\Policies\Microsoft\Windows\WindowsUpdate"

$keys = Get-ItemProperty -Path $regPath -ErrorAction SilentlyContinue

 

if ($keys) {

    Write-Output "Windows Update GPO settings found"

    exit 1  # Non-compliant status

} else {

    Write-Output "No Windows Update GPO settings found"

    exit 0  # Compliant status

}

Remediation Script: Clean Up Registry Keys

If the detection script finds Windows Update GPO settings, the remediation script will delete the associated registry keys, ensuring that Intune can take over Windows Update management.

 

# Remediation Script to clean up GPO registry keys for Intune update deployment

 

# Define the registry paths to be deleted

$registryPaths = @(

    "HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate",

    "HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU"

)

 

# Function to delete registry keys

function Remove-RegistryKey {

    param (

        [string]$path

    )

    if (Test-Path $path) {

        Remove-Item -Path $path -Recurse -Force

        Write-Host "Deleted registry key: $path"

    } else {

        Write-Host "Registry key not found: $path"

    }

}

 

# Delete the registry keys

foreach ($path in $registryPaths) {

    Remove-RegistryKey -path $path

}

 

# Restart Windows Update service to apply changes

Restart-Service -Name wuauserv

 

Write-Host "Registry cleanup complete. The device is now ready for Intune update deployment."

Additional Detection Script: Verify Windows Update Service (WUfB)

This script checks whether the Windows Update service is being managed by Intune (via Windows Update for Business). If it is, the device is ready for Intune updates.

# Detection Script to verify if Windows Update is managed by Intune (WUfB)

$WUServiceManager = New-Object -ComObject "Microsoft.Update.ServiceManager"

$WUService = $WUServiceManager.Services | Where-Object { $_.IsDefaultAUService -eq $true }

$WUServiceNameDetails = $WUService.Name

 

if ($WUService.Name -eq "Microsoft Update") {

    Write-Output "Intune (WUfB) - $WUServiceNameDetails."

    Exit 0  # Compliant

} else {

    Write-Output "Not Intune (WUfB) - $WUServiceNameDetails."

    Exit 1  # Non-compliant

}

This detection script will help confirm that the Microsoft Update service is controlled by Intune’s Windows Update for Business (WUfB) settings.

 

Conclusion

Transitioning the Windows Update workload from SCCM to Intune allows for a modern, cloud-based approach to update management. By cleaning up old GPO registry keys and verifying that devices are configured for Intune-managed updates, you ensure a smooth and seamless update process for your organization's devices.

By using PowerShell scripts for detection and remediation, you automate the process of preparing devices for Windows Update for Business (WUfB) in Intune, making the transition efficient and secure.

Troubleshooting of SCCM Windows Update Deployment with PowerShell Scripts

 

Introduction

Managing and deploying Windows Updates in an enterprise environment can sometimes become a challenging task. Often, updates fail to install or are stuck in a pending state. When troubleshooting such issues in an SCCM (System Center Configuration Manager) environment, PowerShell scripts can be a powerful tool to monitor, remediate, and resolve common issues. This blog post will guide you through using PowerShell scripts within SCCM Configuration Items (CI) for monitoring and remediating Windows Update deployment issues.

We'll walk through:

1. Monitoring Windows Update Folder Activity with a PowerShell script.
2. Remediation of Common Windows Update Issues using PowerShell, such as stopping update services, clearing cache, and resetting configurations.

---

1. Monitoring Script: Checking Windows Update Folder Activity

The first step in troubleshooting Windows Update issues is to ensure the update files are being handled correctly. The SoftwareDistribution folder stores update-related files, and if it's not being updated regularly, it could indicate a problem with the update process.

Monitor Script:

# Define the folder path you want to check

$folderPath = "C:\Windows\SoftwareDistribution"

# Get the current date

$currentDate = Get-Date

# Get the folder's LastWriteTime property

$folderLastModified = (Get-Item $folderPath).LastWriteTime

# Calculate the time span between the current date and the folder's last modification date

$timeSpan = $currentDate - $folderLastModified

# Check if the folder was last modified within the last 15 days

if ($timeSpan.TotalDays -le 15) {

    Write-Host "Compliant"

} else {

    Write-Host "Non-Compliant"

}

---

2. Remediation Script: Resolving Windows Update Issues

When updates aren't working as expected, it's time to run a remediation script. This script will:

• Stop Windows Update services.
• Clear out old update cache and logs.
• Rename the SoftwareDistribution and CatRoot2 folders (which may become corrupted).
• Reset client settings and force discovery of new updates.

Remediation Script:

$arch = Get-WMIObject -Class Win32_Processor -ComputerName LocalHost | Select-Object AddressWidth

$ErrorActionPreference = 'SilentlyContinue'

Write-Host "1. Stopping Windows Update Services..."

Stop-Service -Name BITS -Force

Stop-Service -Name wuauserv -Force

Stop-Service -Name appidsvc -Force

Stop-Service -Name cryptsvc -Force

Write-Host "2. Remove QMGR Data file..."

Remove-Item "$env:allusersprofile\Application Data\Microsoft\Network\Downloader\qmgr*.dat" -ErrorAction SilentlyContinue

Stop-Service -Name BITS -Force

Stop-Service -Name wuauserv -Force

Stop-Service -Name appidsvc -Force

Stop-Service -Name cryptsvc -Force

Write-Host "3. Renaming the Software Distribution and CatRoot Folder..."

Remove-Item $env:systemroot\SoftwareDistribution.bak -Force -Recurse

Rename-Item $env:systemroot\SoftwareDistribution SoftwareDistribution.bak -ErrorAction SilentlyContinue

Rename-Item $env:systemroot\System32\Catroot2 catroot2.bak -ErrorAction SilentlyContinue

Write-Host "4. Removing old Windows Update log..."

Remove-Item $env:systemroot\WindowsUpdate.log -ErrorAction SilentlyContinue

Stop-Service -Name BITS -Force

Stop-Service -Name wuauserv -Force

Stop-Service -Name appidsvc -Force

Stop-Service -Name cryptsvc -Force

Write-Host "5. Resetting the Windows Update Services to default settings..."

"sc.exe sdset bits D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;AU)(A;;CCLCSWRPWPDTLOCRRC;;;PU)"

"sc.exe sdset wuauserv D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;AU)(A;;CCLCSWRPWPDTLOCRRC;;;PU)"

Set-Location $env:systemroot\system32

Write-Host "6) Removing WSUS client settings..."

REG DELETE "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v AccountDomainSid /f

REG DELETE "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v PingID /f

REG DELETE "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v SusClientId /f

Write-Host "7) Delete all BITS jobs..."

Get-BitsTransfer | Remove-BitsTransfer

Write-Host "8) Starting Windows Update Services..."

Start-Service -Name BITS -Force

Start-Service -Name wuauserv -Force

Start-Service -Name appidsvc -Force

Start-Service -Name cryptsvc -Force

Start-Sleep -Seconds 10

Invoke-WmiMethod -Namespace "root\ccm" -Class "SMS_Client" -Name "ResetPolicy" -ArgumentList 1

Start-Sleep -Seconds 10

Invoke-WmiMethod -Namespace root\ccm -Class sms_client -Name TriggerSchedule "{00000000-0000-0000-0000-000000000021}"

Start-Sleep -Seconds 10

Invoke-WmiMethod -Namespace root\ccm -Class sms_client -Name TriggerSchedule "{00000000-0000-0000-0000-000000000113}"

Start-Sleep -Seconds 10

Invoke-WmiMethod -Namespace root\ccm -Class sms_client -Name TriggerSchedule "{00000000-0000-0000-0000-000000000114}"

Start-Sleep -Seconds 10

Invoke-WmiMethod -Namespace root\ccm -Class sms_client -Name TriggerSchedule "{00000000-0000-0000-0000-000000000026}"

Write-Host "9) Forcing discovery..."

wuauclt /resetauthorization /detectnow

 

Write-Host "Process complete. Please reboot your computer."

---

Conclusion

By using SCCM Configuration Items (CI) and PowerShell scripts, you can efficiently monitor and remediate Windows Update issues in your environment. The monitoring script helps identify whether updates are happening, while the remediation script tackles common update failures and clears the way for a successful update process.

SCCM WSUS / SUSDB Maintenance

 

Why is WSUS Maintenance Important?

As WSUS stores metadata, update content, and client communication information, the WSUS database can grow over time. If left unmanaged, this can lead to:

• Slower performance for both WSUS console operations and client update installations.
• Excessive disk usage, leading to storage issues.
• Database fragmentation, which reduces the speed of queries and operations.

To avoid these issues, regular maintenance is necessary.

---

1. Reindexing the WSUS Database

Reindexing helps optimize the performance of the WSUS database by reorganizing and rebuilding indexes. This improves query performance, making it faster to retrieve update information.

Steps to Reindex the WSUS Database:

1. Connect to SQL Server: Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance hosting the WSUS database (SUSDB).
2. Rebuild Indexes:
• Once connected to the database, you can run the following script to rebuild all indexes in the SUSDB:

USE SUSDB;

GO

SET NOCOUNT ON;

 

-- Rebuild or reorganize indexes based on their fragmentation levels

DECLARE @work_to_do TABLE (

    objectid int

    , indexid int

    , pagedensity float

    , fragmentation float

    , numrows int

)

 

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @numrows int

DECLARE @density float;

DECLARE @fragmentation float;

DECLARE @command nvarchar(4000);

DECLARE @fillfactorset bit

DECLARE @numpages int

 

-- Select indexes that need to be defragmented based on the following

-- * Page density is low

-- * External fragmentation is high in relation to index size

PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)

INSERT @work_to_do

SELECT

    f.object_id

    , index_id

    , avg_page_space_used_in_percent

    , avg_fragmentation_in_percent

    , record_count

FROM

    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f

WHERE

    (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)

    or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)

    or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)

 

PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))

 

PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)

 

SELECT @numpages = sum(ps.used_page_count)

FROM

    @work_to_do AS fi

    INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id

    INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

 

-- Declare the cursor for the list of indexes to be processed.

DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do

 

-- Open the cursor.

OPEN curIndexes

 

-- Loop through the indexes

WHILE (1=1)

BEGIN

    FETCH NEXT FROM curIndexes

    INTO @objectid, @indexid, @density, @fragmentation, @numrows;

    IF @@FETCH_STATUS < 0 BREAK;

 

    SELECT

        @objectname = QUOTENAME(o.name)

        , @schemaname = QUOTENAME(s.name)

    FROM

        sys.objects AS o

        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id

    WHERE

        o.object_id = @objectid;

 

    SELECT

        @indexname = QUOTENAME(name)

        , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END

    FROM

        sys.indexes

    WHERE

        object_id = @objectid AND index_id = @indexid;

 

    IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)

        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    ELSE IF @numrows >= 5000 AND @fillfactorset = 0

        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';

    ELSE

        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;

    EXEC (@command);

    PRINT convert(nvarchar, getdate(), 121) + N' Done.';

END

 

-- Close and deallocate the cursor.

CLOSE curIndexes;

DEALLOCATE curIndexes;

 

 

IF EXISTS (SELECT * FROM @work_to_do)

BEGIN

    PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))

    SELECT @numpages = @numpages - sum(ps.used_page_count)

    FROM

        @work_to_do AS fi

        INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id

        INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

 

    PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))

END

GO

 This will update statistics for the WSUS database and rebuild the indexes, improving performance.

2. Updating Database Statistics

Updating statistics in the WSUS database helps SQL Server determine the most efficient way to execute queries. Over time, as data in the database changes, SQL queries can become less efficient if the statistics are outdated.

Steps to Update Database Statistics:

1. Run the Update Statistics Command:
• You can update statistics for the entire WSUS database by running:

--Update all statistics

PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)

EXEC sp_updatestats

PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)

GO

This command updates the statistics for all tables in the database, improving the query execution plan used by SQL Server.

2. Schedule Regular Updates:
• To keep the database running efficiently, it’s a good practice to schedule regular updates to statistics. You can automate this by creating a SQL job to run the sp_updatestats procedure at regular intervals.

---

3. Viewing and Managing Updates in WSUS

One of the key tasks in WSUS maintenance is managing the updates available for clients. Regularly reviewing, approving, and declining updates can help ensure that only relevant and required updates are distributed to clients, reducing unnecessary bandwidth and storage usage.

 

Check Decline, superseed & ununsed update count

 

select

--(Select count (*) 'Total Updates' from vwMinimalUpdate ) 'Total Updates',

 

--(Select count (*) 'Live updates'  from vwMinimalUpdate where declined=0) as 'Live Updates',

 

--(Select count (*) 'Superseded'  from vwMinimalUpdate where IsSuperseded =1) as 'Superseded',

 

--(Select count (*) 'Superseded But NoDeclined'  from vwMinimalUpdate where IsSuperseded =1 and declined=0) as 'Superseded but not declined',

 

(Select count (*) 'Declined'  from vwMinimalUpdate where declined=1) as 'Declined',

 

(Select count (*) 'Superseded & Declined' from vwMinimalUpdate where IsSuperseded =1 and declined=1) 'Superseded & Declined'

Query to Decline update superseded 90 Days

DECLARE @thresholdDays INT = 90   -- Specify the number of days between today and the release date for which the superseded updates must not be declined.

DECLARE @testRun BIT = 0          -- Set this to 1 to test without declining anything.

 

-- There shouldn't be any need to modify anything after this.

 

DECLARE @uid UNIQUEIDENTIFIER

DECLARE @title NVARCHAR(500)

DECLARE @date DATETIME

DECLARE @userName NVARCHAR(100) = SYSTEM_USER

 

DECLARE @count INT = 0

 

DECLARE DU CURSOR FOR

       SELECT MU.UpdateID, U.DefaultTitle, U.CreationDate FROM vwMinimalUpdate MU

       JOIN PUBLIC_VIEWS.vUpdate U ON MU.UpdateID = U.UpdateId

       WHERE MU.IsSuperseded = 1 AND MU.Declined = 0 AND MU.IsLatestRevision = 1

       AND MU.CreationDate < DATEADD(dd,-@thresholdDays,GETDATE())

       ORDER BY MU.CreationDate

 

PRINT 'Declining superseded updates older than ' + CONVERT(NVARCHAR(5), @thresholdDays) + ' days.' + CHAR(10)

 

OPEN DU

FETCH NEXT FROM DU INTO @uid, @title, @date

WHILE (@@FETCH_STATUS > - 1)

BEGIN 

       SET @count = @count + 1

       PRINT 'Declining update ' + CONVERT(NVARCHAR(50), @uid) + ' (Creation Date ' + CONVERT(NVARCHAR(50), @date) + ') - ' + @title + ' ...'

       IF @testRun = 0

              EXEC spDeclineUpdate @updateID = @uid, @adminName = @userName, @failIfReplica = 1

 

       FETCH NEXT FROM DU INTO @uid, @title, @date

END

 

CLOSE DU

DEALLOCATE DU

 

PRINT CHAR(10) + 'Attempted to decline ' + CONVERT(NVARCHAR(10), @count) + ' updates.'

WSUS delete old update

DECLARE @UpdateID VARCHAR(50)

DECLARE @msg nvarchar(100)

DECLARE Curs CURSOR FOR

      SELECT UpdateID FROM [SUSDB].[PUBLIC_VIEWS].[vUpdate] where IsDeclined=1

 

OPEN Curs FETCH NEXT FROM Curs into @UpdateID

WHILE @@FETCH_STATUS = 0

BEGIN

SET @msg = 'Deleting' + CONVERT(varchar(100), @UpdateID)

RAISERROR(@msg,0,1) WITH NOWAIT

exec spDeleteUpdateByUpdateID @UpdateID FETCH NEXT FROM Curs into @UpdateID

 

END

CLOSE Curs

DEALLOCATE Curs

 

Conclusion

Regular maintenance of your WSUS environment is essential for optimal performance and reliability. By reindexing the database, updating statistics, managing update approvals, and cleaning up old records, you can ensure that WSUS remains efficient and capable of distributing updates in a timely manner.

Automating these tasks using SQL jobs or PowerShell scripts will help keep WSUS running smoothly without requiring constant manual intervention.

 

SCCM Run Script feature stopped working

Solution/Workaround:

1. Recreate the SMS SSL Certificate:
• The issue might be related to SSL certificate problems. Recreating the SMS SSL certificate can help resolve any issues with secure communication between SCCM and the clients.

Steps to recreate the SMS SSL certificate:

• Go to Configuration Manager Console.
• Navigate to Administration > Security > Certificates.
• Right-click on SMS Server Authentication Certificate, and select Delete.
• After deleting the certificate, select Create Certificate and follow the prompts to create a new SSL certificate for SMS communication.
1. Verify SQL Service Broker:
• The SQL Service Broker might not be enabled, which can interfere with the proper operation of scripts and certain SCCM tasks. To ensure it is enabled:

Steps to verify and enable SQL Service Broker:

• Open SQL Server Management Studio (SSMS).
• Connect to the SCCM database instance.
• Run the following SQL query to check the status of the Service Broker:

SELECT is_broker_enabled

FROM sys.databases

WHERE name = 'CM_' + <Your SCCM Database Name>;

• If the value of is_broker_enabled is 0 (which means it is disabled), you can enable it by running:

ALTER DATABASE <Your SCCM Database Name> SET ENABLE_BROKER;

• Once the SQL Service Broker is enabled, restart the SQL Server service to apply the changes.

These steps address the potential root causes related to SSL certificate issues and SQL Service Broker, which can affect SCCM script execution.