Friday, 31 January 2025

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.

  1. 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.

 

Wednesday, 22 January 2025

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.


Monday, 20 January 2025

PSAppDeployToolkit in Intune to Check Interactive Session and Install Application with Notifications

 

How to Use PSAppDeployToolkit in Intune to Check Interactive Session and Install Google Chrome with Notifications

Managing software installation across an organization’s devices can be a challenging task for IT administrators, especially when dealing with complex scenarios like ensuring that installations occur only when certain conditions are met. One such condition might be whether a user is logged in interactively and if the target application (like Google Chrome) is running.

In this blog post, we'll guide you through the process of using PSSTool (PowerShell Script Tool) in Microsoft Intune to check for an active interactive session, check whether Google Chrome is already running, and install the browser with appropriate notifications for the user. We’ll use a custom PowerShell script to make this happen.

Scenario Overview

For this scenario, the goal is to:

· Check for an interactive session on the device.

· Detect if Google Chrome is already running.

· Install Google Chrome using a silent mode if the application is not running.

· Display appropriate notifications during the installation process.

The Script Explained

The PowerShell script you're going to use will check if Google Chrome is running, determine the correct installation method based on that, and install Chrome in a way that respects the user’s session. Here's the script you’ll deploy:

powershell

Copy

# Check if Google Chrome is running

$ChromeProcesses = @(Get-WmiObject -Query "select * FROM Win32_process WHERE name='Chrome.exe'" -ErrorAction SilentlyContinue)

 

if ($ChromeProcesses.Count -eq 0) {

    # If Chrome is not running, initiate installation in Session 0 (Non-Interactive mode)

    Try {

        Write-Output "Google Chrome is not started, we can run the installation in session 0"

        Start-Process Deploy-Application.exe -Wait -ArgumentList '-DeployMode "NonInteractive"'

    }

    Catch {

        $ErrorMessage = $_.Exception.Message

        Write-Error "Error: $ErrorMessage"

    }

} else {

    # If Chrome is running, initiate installation in Session 1 (Interactive mode)

    Try {

        Write-Output "Google Chrome is started, we need to run the installation in session 1 using ServiceUI.exe"

        .\ServiceUI.exe -process:explorer.exe Deploy-Application.exe

    }

    Catch {

        $ErrorMessage = $_.Exception.Message

        Write-Error "Error: $ErrorMessage"

    }

}

 

# Output the installation exit code

Write-Output "Exit Code of installation is: $LASTEXITCODE"

exit $LASTEXITCODE

Conclusion

Using PSAppDeployToolkit in Microsoft Intune to check for an interactive session and install Google Chrome with notifications is a great way to automate software installation while ensuring minimal disruption to users. By checking if Chrome is already running and using ServiceUI.exe to interact with the logged-in user, you create a smoother and more efficient deployment process.

More details about PSAppDeployToolkit- https://psappdeploytoolkit.com/docs/3.10.2/examples/googlechrome-configmgr

Friday, 10 January 2025

How to Execute SQL Query in SCCM to Retrieve Deployment and Application Information

 

If you're working with SCCM (System Center Configuration Manager) and need to pull detailed information about applications, deployment types, and associated content IDs, you can use SQL queries to directly access the SCCM database. Below is a SQL query designed to extract the deployment types and application details, including their associated content information.

This blog will walk you through how to execute the following SQL query and explain its components.

SQL Query for SCCM:

SELECT

    app.DisplayName AS ApplicationName,

    dt.DisplayName AS DeploymentTypeName,

    dt.PriorityInLatestApp,

    dt.Technology,

    DT.ContentId

FROM

    dbo.fn_ListDeploymentTypeCIs(1033) AS dt

INNER JOIN

    dbo.fn_ListLatestApplicationCIs(1033) AS app

    ON dt.AppModelName = app.ModelName

WHERE

    (dt.IsLatest = 1)

    AND (DT.ContentId LIKE '%Content_d1714238-1d5f-4b16-aaa2-385c37218c41%'

         OR DT.ContentId LIKE '%Content_e134865c-8b44-4b02-8e1f-de53e05ccc18%')

ORDER BY

    ApplicationName

Conclusion:

This SQL query is useful for retrieving detailed information about applications and their deployment types, particularly when troubleshooting or analyzing deployment configurations in SCCM. Using SQL queries like this helps SCCM administrators gain deeper insights into the deployment structure and content associations.


Monday, 30 December 2024

Troubleshooting MDT Integration with Multiple Primaries and Editing the Microsoft.BDD.CM12Actions.mof File

 

Introduction

Microsoft Deployment Toolkit (MDT) and System Center Configuration Manager (SCCM) are powerful tools commonly used together for operating system deployments. However, when integrating MDT with SCCM, especially in environments with multiple primary site servers, issues can arise in task sequences. One such issue occurs when editing the Microsoft.BDD.CM12Actions.mof file to change the site service, leading to errors in MDT integration and task sequence execution.

In this blog, we will explore the challenges faced when MDT integration doesn’t install correctly, particularly in environments with multiple primary sites. We will walk through the process of editing the Microsoft.BDD.CM12Actions.mof file and recompiling it, while also troubleshooting common errors related to the configuration.

Understanding the Issue

MDT task sequences rely on SCCM to deploy operating systems. When there are multiple primary site servers in an SCCM environment, MDT needs to be properly configured to communicate with the correct management point. The Microsoft.BDD.CM12Actions.mof file plays a critical role in defining the SCCM site and management point for MDT to use during deployment.

The problem arises when this file is edited to point to a specific primary site server, but MDT is still unable to perform tasks correctly. This may be due to several reasons such as improper changes to the MOF file, conflicts between multiple primary sites, or issues with the management point that MDT is trying to communicate with.

Step 1: Edit the Microsoft.BDD.CM12Actions.mof File

In environments with multiple primary sites, the Microsoft.BDD.CM12Actions.mof file must be edited carefully to ensure MDT uses the correct site server for its communication. Here's how to do it:

  1. Locate the MOF File
    The Microsoft.BDD.CM12Actions.mof file is located in the AdminConsole\bin folder of your SCCM installation. The path typically looks like this:

<ConfigMgr_Install_Directory>\AdminConsole\bin

Replace <ConfigMgr_Install_Directory> with the actual directory where your SCCM is installed, which by default is:

C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin

  1. Open and Edit the File
    Open the Microsoft.BDD.CM12Actions.mof file using a text editor (e.g., Notepad++). In the file, find the line where the SMS provider or site service is listed. It should look like this:

Provider="sms:<SMSProvider_FQDN>"

  1. Replace the SMS Provider with the Correct Primary Site
    Since there are multiple primary sites in your environment, replace the <SMSProvider_FQDN> with the Fully Qualified Domain Name (FQDN) of the primary site server that you want MDT to use. For example:

Provider="sms:PrimarySiteServer.FQDN"

  1. Save the MOF File
    After making the necessary changes, save the file.

Step 2: Recompile the MOF File

After editing the Microsoft.BDD.CM12Actions.mof file, it must be recompiled to apply the changes. Follow these steps:

  1. Open an Elevated Command Prompt
    Run Command Prompt as an administrator to have the necessary privileges.
  2. Navigate to the Directory
    Use the cd command to navigate to the folder where the Microsoft.BDD.CM12Actions.mof file is located:

cd <ConfigMgr_Install_Directory>\AdminConsole\bin

  1. Compile the MOF File
    Run the following command to recompile the MOF file:

mofcomp Microsoft.BDD.CM12Actions.mof

This command will compile the MOF file and apply the changes to the WMI repository. If successful, you will see a confirmation message.

Conclusion

MDT integration with SCCM is a powerful tool for deploying operating systems, but it can encounter issues when there are multiple primary sites. Editing the Microsoft.BDD.CM12Actions.mof file to update the site service is an essential step in ensuring proper communication. However, if not done carefully, it can lead to issues such as task sequences not being available or MDT failing to communicate with the correct site server.

By following the steps outlined in this blog and troubleshooting common errors, you can ensure a smooth integration of MDT with SCCM, even in environments with multiple primary sites. Always test your changes thoroughly and check the relevant logs to diagnose and resolve any issues.

Troubleshooting and Installing SCCM Clients with PKI Certificates

 

Introduction:

In this blog post, we will go through several important steps to help with SCCM (System Center Configuration Manager) client management, including clearing old configurations, removing certificates, and installing the SCCM client using PKI certificates. We will also cover starting and stopping services, handling the Windows firewall, and ensuring a proper configuration when working with SCCM clients.

Step 1: Stop the SCCM Client Service (ccmexec)

The first step in the cleanup process is to stop the ccmexec service, which is the core service for the SCCM client. This will prevent the client from attempting to run while we perform our cleanup operations.

To stop the ccmexec service, run the following command in an elevated Command Prompt:

net stop ccmexec

This will stop the service temporarily and ensure no background operations are running while you perform the necessary cleanup steps.

Step 2: Delete the SMSCFG.INI File

The SMSCFG.INI file holds important configuration information for the SCCM client. Deleting this file can help resolve issues where the client is incorrectly configured or when you want to reset the client’s configuration to its default state.

Run the following command to delete the file:

del c:\Windows\SMSCFG.INI

This will remove the configuration file. It will be re-generated the next time the SCCM client is initialized.

Step 3: Remove the SCCM Certificate from the SMS Store

In certain cases, you may need to delete the certificate from the SMS certificate store (this could happen when you are troubleshooting issues with certificates or when you want to reset the certificates). To remove the certificate, you can use the certutil command.

Run the following command to delete the certificate from the SMS store:

certutil -delstore SMS SMS

This command deletes the certificate from the SMS store. Be cautious when using this, as it removes the certificate needed for secure communication between the client and the server.

Step 4: Restart the SCCM Client Service (ccmexec)

After performing the cleanup steps, you need to restart the ccmexec service to reinitialize the client. You can do so using the following command:

net start ccmexec

This will start the SCCM client service again, and the client will begin communicating with the SCCM server once more.

Step 5: Start the Windows Firewall Service (if it's stopped)

If the Windows Firewall service is stopped, it can cause communication issues between the SCCM client and the server. To ensure the firewall is running, you can start the service (if it's stopped) by running:

net start mpssvc

This command starts the Windows Firewall service (mpssvc), ensuring that the necessary firewall rules are applied, and the client can communicate over the required ports.

Step 6: Install SCCM Client Using PKI Certificates

If you're setting up the SCCM client and need to configure it to use PKI (Public Key Infrastructure) certificates, you can use the CCMSetup.exe command. This command installs the SCCM client while ensuring that it uses PKI certificates for secure communication with the management point.

Here is the command you will use:

CCMSetup.exe /mp:YOURMP /UsePKICert

  • /mp:YOURMP: Replace YOURMP with the fully qualified domain name (FQDN) of your Management Point (MP). The MP is a key component in the SCCM infrastructure that communicates with the client.
  • /UsePKICert: This flag tells the client to use PKI certificates for secure communication.

When this command is executed, it will install the SCCM client on the machine and ensure that the client communicates securely with the management point using the certificates issued by your PKI infrastructure.

Conclusion

By following these steps, you can troubleshoot SCCM client issues, remove old certificates, reset configurations, and install a new SCCM client using PKI certificates. These operations are crucial for maintaining a healthy SCCM infrastructure and ensuring secure communication between clients and servers.

Wednesday, 27 November 2024

SCCM SQL Query Online device by Management Point

 SCCM SQL Query Online device by Management Point


select srl.SiteCode, srl.ServerName, srl.InternetEnabled, srl.Shared, srl.SslState,

SUM(brs.OnlineStatus) AS OnlineClients, bs.ReportTime from SysResList srl

inner join BGB_Server bs ON srl.ServerName = bs.ServerName

inner join BGB_ResStatus brs ON bs.ServerID = brs.ServerID

where RoleName='SMS Management Point'

group by srl.SiteCode, srl.ServerName, srl.InternetEnabled, srl.Shared, srl.SslState, bs.ReportTime


 SCCM SQL Query Client device count by Management Point


Select Substring(br.AccessMP,1,(PATINDEX('%.%',br.AccessMP)-1)) as Management_Point, 

Count(Substring(br.AccessMP,1,(PATINDEX('%.%',br.AccessMP)-1))) as Total_Clients

from

v_R_System sd join BGB_ResStatus br on sd.ResourceID=br.ResourceID 

Where PATINDEX('%.%',br.AccessMP) > 0

Group By Substring(br.AccessMP,1,(PATINDEX('%.%',br.AccessMP)-1))

Order By Count(Substring(br.AccessMP,1,(PATINDEX('%.%',br.AccessMP)-1))) Desc



SCCM WSUS / SUSDB Maintenance

  Why is WSUS Maintenance Important? As WSUS stores metadata, update content, and client communication information, the WSUS database can ...