A basic example of how to use DCM (Desired Configuration Management) instead of Hardware Inventory custom mof extensions
Issue: A task was assigned to find any computers, servers or workstations, which might have the Environment Variable of SQLCMDPASSWORD defined. If found, remove it automatically.
Traditional method: Before DCM, what I would have done would be to enable the win32_environment default class in sms_def.mof. Or I would have created a custom mof edit to either look for the registry key instance of that Environment Variable, or I to look in WMI for that specific environment variable in win32_environment.
With Configuration Manager, and especially because this request is a very clear yes/no type question I’m going to be asking, it’s a perfect candidate for a DCM baseline, configuration item, and auto-remediation if not compliant. If I had instead been asked to report on the values contained in the environment variable SQLCMDPASSWORD, then DCM might not have been the correct solution.
And actually, the very opposite is true regarding the value of this environment variable. This would be—in clear text—a password for access to SQL databases. Quite honestly I don’t want to know. The whole reason for this request is to reduce the # of places that a clear-text password is listed on computers in the company. If I gather it via Hinv and store it on the ConfigMgr database, I’ve actually just made the security risk worse.
Create the CI:
- In the console, create a new CI. In this case, an General Configuration Item type. Input a name and select a category if you wish.
- Next (no objects)
- For the actual test, New, WQL. Input a name, like “SQLCMDPASSWORD Env Variable”, and a description. We’re testing for root\cimv2, win32_environment, where name = ‘SQLCMDPASSWORD’.
- On the test tab, Compliant (a good machine) would be if this environment variable does NOT exist, so uncheck the box regarding instance count fails. No instances would be absolutely perfect
- Create a New String type validation rule. Equals AnyValueIsWrong
o Seems weird, right? We just asked for it to find the Name value where Name=SQLCMDPASSWORD, so naturally the name *has* to =SQLCMDPASSWORD. But since that’s exactly what we want to get rid of, for the test, if Name <> AnyValueIsWrong (which is doesn’t equal that), the box is not compliant.
o Yes, I know I could have used a script test instead of a WQL test. This feels a little cleaner to me.
- Next/Next/Finish.
Now that the CI is done, I need to either create a new Baseline, or if there happens to already be a Baseline that would be targeted to the *exact* same collection (like All Systems) already, I could add it to that existing baseline. In this case, we’re going to assume this is the first custom Configuration Item.
Create the Baseline:
- In the Console, right-click, new baseline. Give it a name, and select your CI.
- Now, assign a collection to that baseline. It is recommended you test your baseline against a few test systems first.
o Note: by design if you add or remove the element you are testing for (i.e., create or delete an environment variable) the Baseline will not re-test for 15 minutes—it will use cached results. So if you are testing, changing, testing, changing… remember to wait 15 min between tests.
Magical: Although possibly unrealistically, for the purposes of this blog entry I happened to create the baseline and CI perfectly the very first time. That’s never happened to me before, I always have to tweak my custom DCM Baselines, but this is a blog entry, so I can pretend I’m infallible, and the first attempt at this CI was perfect. It was Compliant if no SQLCMDPASSWORD environment variable existed, and it reported Non-Compliant if the SQLCMDPASSWORD environment variable existed. (in reality, it was 3 attempts before I got it right).
Remediation:
Great! Now I want to send out an advertisement to those computers reporting Non-Compliant, with the goal that this environment variable—which is a security risk according to the powers-that-be, be automatically deleted from any computers where it might be found—whether they are workstations or servers is irrelevant. It’s not supposed to exist, period.
The Package/Program is a vbscript.
- I could have simply used cmd.exe /c SET SQLCMDPASSWORD= but… I try to be nice, and leave a trail behind me when settings are changed. Even though it’s 100% against corporate policy to have this environment variable, one never knows. Perhaps there is a decades-old application out there which simply refuses to work without this environment variable set, so we’d need to leave a note… somewhere… for the hapless admin stuck trying to figure out “what went wrong” after we’ve not-so-helpfully removed the SQLCMDPASSWORD= variable.
- So, to give them a snowball’s chance to know who to contact to request an exception to this draconian rule, we’ll leave a note in EventVwr.
on error resume Next
Set sho = WScript.CreateObject("WScript.Shell")
Set objLocator = CreateObject("wbemscripting.swbemlocator")
Set objServices = objLocator.ConnectServer(,"root\cimv2")
Set colVariable = objServices.ExecQuery ("Select * from Win32_Environment where Name = 'sqlcmdpassword'")
For Each object In colVariable
sho.LogEvent 2, "The Environment Variable SQLCMDPASSWORD has been automatically removed from this system. " &_
"If this action has been done in error, submit an exception request to the Corporate " &_
"Policy Department, x1234, MailCode R4321"
object.VariableValue = ""
object.Put_
Next
The collection query to target the advertisement to is similar to this:
select SMS_R_SYSTEM.ResourceID
from
SMS_R_System
inner join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_CI_ComplianceState.ComplianceStateName = "Non-Compliant" and SMS_G_System_CI_ComplianceState.LocalizedDisplayName = "Environment Variable Exists SQLCMDPASSWORD"
End Result:
- Weekly, the DCM is run, and the clients report compliant or non-compliant.
- The Collection Update frequency is weekly.
- If a computer drops into the collection, it deserves the advertisement, which is set to run weekly.
Why weekly? For this, it’s a security risk, true. But it’s not a risk level along the lines of “company will lose money”. So more than weekly really isn’t necessary. If someone at the highest levels of the organization determines that the risk level is that high—i.e., similar to a zero-day patch or something—all of the frequencies can be increased as necessary. But in my opinion, unless someone does assign that kind of level to a DCM baseline, weekly or every 5 days or something mildly infrequent is good enough.