SCCM daily cleanup SQL DAC Change Tracking data

Hello Friends,

This PowerShell script I have written checks SQL Server's Dedicated Admin Connection (DAC) usage and diagnose Change Tracking (CT) health on a given SQL instance. It's designed to log the results in an HTML report format.:

PowerShell function SQLDACChangeTracking in more detail so you have a full understanding of what it does, section by section.

🔍 Section-by-Section Breakdown

🔹 1. Function and Header Setup

Function SQLDACChangeTracking {

    $Server = $strSQLDACChangeTracking

    $Server = $Server.toupper()

Initializes the function.

Takes the server name from a variable $strSQLDACChangeTracking and converts it to uppercase.

🔹 2. Report Header HTML Setup

$rptheader = @"

<table width='100%'><tbody>

<tr bgcolor=#01A982> <td align='center'> $Server SQL DAC ChangeTracking Report</td>

</table>

...

"@

Add-Content "$Report" $rptheader

Sets up the top portion of an HTML report.

Adds a title and column headers like Name and Status.

$Report is assumed to be a path to an output HTML file.

🔹 3. First SQL Query – Check DAC Session

$sqlquery1 = @"

USE databasename;

SELECT ...

FROM sys.endpoints ep

JOIN sys.dm_exec_sessions es ON ep.endpoint_id = es.endpoint_id

WHERE ep.name = 'Dedicated Admin Connection'

"@

This SQL checks whether any session is using the Dedicated Admin Connection (DAC).

DAC is used for emergency administrative access to SQL Server.

$sqlresults = Invoke-Sqlcmd ...

Executes the above query.

If any DAC session is active, it will return details like:

host_name (machine using DAC)

session_id

login_time

status

🔹 4. If DAC Query Returns Results

If $sqlresults is not null, it extracts fields and formats them into a green-colored HTML table indicating an active DAC session. However, there’s a mismatch here:

<td width='90%' align='left' >DAC session is not available</td>

This line implies DAC isn't in use, but it's inside the block that runs when DAC is in use. That could be misleading and might need correcting.

🔹 5. If DAC Query Fails (Fallback to DAC Mode)

If the first query fails (returns $null), it falls back to running a stored procedure:

USE databasename; EXEC spDiagChangeTracking

Runs under DAC connection using:

-DedicatedAdministratorConnection

Captures exceptions using:

-ErrorAction SilentlyContinue -ErrorVariable abc

If the execution succeeds, it reads values from $sqlresults.Tables[1], such as:

CT_Days_Old: how old the last tracked change is.

Auto_Cleanup_Time_(Local) and Cleanup_Time_(Local): cleanup timestamps.

Retention_Period and Retention_Unit: CT retention configuration.

🔹 6. Data Evaluation and HTML Reporting

Based on thresholds (like CT_Days_Old > 5), the script highlights rows using color coding:

Red: bad or needs attention

Green: healthy

Orange: warning or needs monitoring

Each piece of data is wrapped in an HTML table and written to the report using Add-Content.

🔹 7. Error Logging

If any exception occurs during DAC mode query:

$abc[0].Exception

Add-Content $logfile -Value "$abc : Checking SQL Server Services Details"

Logs the error details.

Displays an error in the HTML report using Error Exception.

✅ Use Cases for This Script

Health monitoring of SQL Server's Change Tracking (CT) feature.

Auditing unexpected use of the DAC connection.

Preventive maintenance by alerting on data retention age, cleanup times.

Daily automated reporting on CT status via HTML output.

Updating the country or region settings in windows 10

Hello Friends,

 

We observed sometimes, we are facing issues while updating the country or region settings in windows 10. Thus, it is important to keep the correct settings. If you can’t change country or region, it might be for one of these reasons:

You changed the country or region within the last three months.

Your account is suspended. For example, the credit card on a subscription has expired, or your subscription has a balance due.

You’re legally a minor in your current country or region and won’t be a minor in the new region, or vice-versa.

 

For more details about country or region settings in Windows 10, refer to the below link.

 

START/ STOP services and rename any folder

Hey Guys, What's up? 

 

Today we are going to automate and deploy the below tasks.

Stop Services

Perform Operations (rename folder)

Start Services

 

How?

Update Task Sequence, DP info in registry

Hello Friends,

Is there a way I can know which task sequence is used to deploy a particular machine?

Yes, we can update the registry key with the Task Sequence info like, which Task Sequence is used to deploy the OS on a particular machine?

from Which DP the contents are downloaded? Is the machine built with Boot media or it is PXE boot?

Is this a machine desktop or laptop? what is the machine type, machine model?

Also, we can get when the machine is imaged(date and time when the Task Sequence is deployed).

First, we have to gather all the information which we want to update in the registry.

let's take the example of DP's name...

How do we know from which DP the contents are getting downloaded? from where I can get this information? is it a simple variable which I can use to update the registry? No.

While running the Task Sequence all the variable data is stored in a .dat file and we can fetch that using "New-Object -ComObject 'Microsoft.SMS.TSEnvironment"

Update WoLWAN settings on Wireless Adaptor

Hello Guys,

As an SCCM administrator, we are receiving the requirement to deploy applications, patches, application updates post OSD.

Sometimes we have to modify the OS-related settings like if a particular file is present on the machine, then run remediation script OR if a particular setting is present on the machine, then change it with new settings.

In this scenario, we are going to change the Wireless Adaptor settings related to Power Management.

Yes, I am talking about Wake on LAN settings WoL. Microsoft has released Information about power management settings on a network adapter. Click HERE to read the Microsoft kb article. This article is important to learn more about the PnPCapabilities values and how to use its combinations. In this article, MS provided information on how to use the registry key if we are using this for broad deployment purposes.

Ok, here we are focusing on the Wireless adaptor and will create a PowerShell script using the above-mentioned settings in the script.

We are using the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E972-E325-11CE-BFC1-08002bE10318}\DeviceNumber registry settings to update the wireless settings.

OSD Driver installation via PowerShell

Today we are going to install the model-specific driver packages using PowerShell script. The advantage of using this method of driver installation during the Operating System deployment is to reduce the overall Task Sequence deployment time, This method is more suitable if you have multiple hardware devices with multiple vendors (Dell, HP, Lenovo, Microsoft, etc...) 

In order to achieve this method follow the below steps. 

1. Create the folder structure as below.

        a. Vendor\Hardware model\

2. Download the specific hardware model driver package from the vendor website and extract the cab files.

3. Archive/Zip all the extracted drivers into one single folder called "Drivers.zip".

4. Copy DISM folder from ADK installed machine and place into the "Vendor\Hardware model\"

path of DISM-(C:\Program Files (x86)\Windows Kits\<version>\Assessment and Deployment Kit\Deployment Tools\10\DISM)

SCCM SQL query to get the full OS version with build number

 Today, again we are going to create one SQL query..... 

Today we are going to pull the Operating System version with the build number. from SCCM SQL database.

For a single machine, you can run the command "Winver" from the run and you will get the below information

SCCM SQL query to get machines with user id, name, email, last logon

Hello Friends, Good evening and how are you?

Today someone asked me to create one SQL query to get the machine details like hostname, IP Address, RAM, total HDD size, Free Space, Manufacturer, Model, Last Heartbeat, Users name, email ID, Last logged on user name.

Also, We will fetch the domain details and CCM client active status.

Distribute multiple packages on multiple distribution points

Hello Guys, Hope you are doing well. Today we are going to automate the package distribution.

This script will help you to distribute multiple packages on multiple distribution points.

let's download the PowerShell code mentioned below and run it from any SMS Provider server.

Error during connection to SMS_DP$(67)

Today we were facing some issues with our Distribution Point server. Wintel has confirmed the issue with the Operating System and has wiped and loaded the C Drive and D Drive was remain the same.

Now, the actual problem has started while updating contents on the DP. We are getting below errors in random.

"There is no existing connection. win32 error = 67"
"Error during connection to SMS_DP$(67)"
"Attempting to connect failed"

Resolution:

SCCM image migration to new server

 Hello Guys, hope you doing well and are using the automation scripts, and are happy with the way of automation.

Today we are going to discuss some troubleshooting parts.

If you are going to migrate your existing SCCM infra to the new hardware, from the Build site you need to migrate your Driver packages, Boot Images, Task Sequences, all TS dependant packages, and off-course Operating Systems to the new SCCM server.  

obviously, you have to create a migration job to do so. The migration job wizard will help you to migrate the objects from one SCCM server to another. While migrating the WIM files from existing SCCM infra you might be getting the below error.

OSD log file locations

While deploying the TS on the targeted machine we are experiencing Task Sequence failure on various steps. like before format the Hard Disk, after format the Hard Disk or post-Win-PE stage.

Below are the scenarios from where you can gather the required log file for further troubleshooting.

OSD log file locations:

Phase -Windows PE (Before the hard disk are formatted)

Location - "x:\windows\temp\smstslog\smsts.log"

Phase -Windows PE (After the hard disk are formatted)

Location - "x:\smstslog\smsts.log" and copied to "c:\_SMSTaskSequence\Logs\Smstslog\smsts.log"

Phase -Windows Operating System (Before the SCCM client is installed)

Location - "c:\_SMSTaskSequence\Logs\Smstslog\smsts.log"

Ping multiple hostnames in single PowerShell script.

Guys, In a day-to-day activity, we have to perform repetitive tasks like check the availability of the server by using ping utility and send the report to management. while doing this activity manually, this is an irritative task, while using automation, it's pretty easy.

Let's create a new PowerShell script and insert the below code. once you save the ps1 file.

create another file with extension .txt and put destination servers which do you want to ping and save that text file with the name "inputcomputers.txt" at the location where you save the ps1 file.

You can run the PowerShell with Task Scheduler or Simply run PS1 to get the ping report in "outputcomputers.log" 😀👍

Add domain user in Local Administrator Group

Today we are going to create a simple script that will help us to add domain users, into the local administrator's group via batch script.

Create a new notepad file and insert the below code into it. Once done save the notepad file with .bat or .cmd extension and place this file on the shared location and execute with psexec or PowerShell PSSession

Finally, your script will be looks like as shown in the above image✌

While running the script remotely we have to remove pause from the script and then execute the script.

TaskBand customization during TS execution PowerShell Solution

Friends, Hope you are doing well. Today we are going to automate the taskband during the imaging process. We will create the taskband package using Powershell and deploy the package via a task sequence. alternatively, you can use the batch file instead of PowerShell.

What first?

First, we have to log in on the reference machine from where we are capturing the taskband files and settings.

Pin the taskbar items whatever you want😊 as per your org requirement, not blindly😏

go to location "%userprofile%\AppData\Roaming\Microsoft\Internet Explorer\Quick Launch\User Pinned\TaskBar\"

TaskBar Customization during Task Sequence execution Batch Script

There are two ways to customize your TaskBand during the Task Sequence execution.

1. Using PowerShell script

2. Batch Script as described here.

Pin the taskbar items whatever you want😊 as per your org requirement, not blindly😏

go to the location "%userprofile%\AppData\Roaming\Microsoft\Internet Explorer\Quick Launch\User Pinned\TaskBar\" 

copy all the shortcut files from this location and save them to your reference package folder.

for me, I have created one folder on the reference machine desktop named "TaskBandAutomation\Taskbar".

now what next? open registry settings for users and navigate to "Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Taskband" and say export and save as TaskBand.reg. where? on your desktop or anywhere you want. anyway, we have to delete this file after getting some info from it.

Make CMTrace as default log viewer during the Imaging process(OSD)

While analyzing the SCCM logs every engineer wants to open log files by default in CMTrace. But When you log in to a new build machine and open the CCM logs or any *.log files from the machine, the log files will open in the notepad by default. Reading the SCCM log files into notepad is very difficult, Microsoft is providing the live log reading tool call CMTrace with the SCCM suite. In this post, we are going to make it the default app for log files. We will create the SCCM package and deploy it via Task Sequence execution while Operating System Deployment step.

To achieve this, create a new package using the below PS1 code and deploy it on required DPs

Cleanup Image before Capture - Event Viewer cleanup batch file

Guys, Why WIM Image cleanup is more important ? This is because it delivers error-free images to the customer and reduces the manufacturer's bugs and issues. While troubleshooting the end-user machine, there should not be previous logs that can create confusion.

If you are going to clean up the event viewer logs one by one manually it will take your whole day and also an irritating job for you. don't worry we have created a simple and easy script that helps you to clean the event viewer logs within a minute.

Auto Diskpart and stale entry delete from SCCM during Imaging process

I know many of the build administrators are deploying the Task Sequence on unknown device collection. I purpose and the idea behind it is to do not deploy the task sequence on a production device and prevent accidental data loss.

If the machine is in SCCM collection and we want to run the task sequence on it, we must SCCM Console and do a manual deletion job and wait for a couple of minutes to restart the imaging on the target machine

This is a time-consuming and repetitive task for us. Can we create a way to do this in an automotive way? Like, create the PowerShell Script, which will automatically read the machine's MAC Address and connect to the SCCM database from the WinPE environment for getting all the hostnames to hold by that mac address and delete each.

Also, can we do a diskpart at the same time to avoid further imaging failure? Yes we can create the PowerShell Script and integrate it with Boot Image, which will automatically check whether the machine having an internal storage device attached or not, and based on the results script will perform Diskpart operation, if the internal storage device is faulty or not connected in BIOS, the script will prompt the error.

Provide Hostname and delete Machines from SCCM console - PowerShell automation

Hello Friends,

Suppose, if I have 200 machines hostname and each hostname having 2-3 machines in the SCCM console and you want to delete each machine from the console. you will be irritated doing this job manually. the reason behind it, its time-consuming activity and repetitive task.

why you are not doing smart work? how?

Let's say we have a scenario like above and you have one PowerShell script in which you added all the machines hostname into it and just run the script and it deleted all the desired machines from the console while you finish a cup of tea 😉

Yes, We can create the PowerShell script like this using ConfigurationManager.psd1 SCCM PS module.

Import the PowerShell module first and then connect to the Primary Site Server. To get the hostname of the machine from a file, we have to create one input file named "hostnameinput.txt" in the same directory where we have created and kept the PowerShell script. Once you create the input text file paste all the machines hostname into the file and runs the below script with SCCM admin privileges.