Using Configuration Items instead of Software Inventory

Issue to be solved: Software Inventory (which is really FILE inventory) you've noticed takes FOREVER when you define a rule like "dropbox.exe on c:\users\", the clients take several minutes to run that query.  and the more rules you make, the longer it might take your clients to run software inventory.

Resolution:  whenever possible, forget creating software (file) inventory rules.  This blog post will show how to setup a rule looking for dropbox.exe on c:\users\, and you can get back File Version so you can run reports.

Take the attached --> here <-- and import it into your console, Configuration Items.  Create a Baseline and deploy that Baseline to a target collection.  What should happen is if the file dropbox.exe is in c:\users\ somewhere, those boxes will report non-compliant, and will report the Fileversion, and path location, where dropbox.exe is located.

Why I used dropbox.exe... dropbox.exe may not be listed in Installed Software, nor in Add/Remove Programs.  It might be listed in ccm_recentlyusedapps.  Using this as a sample, after you've deployed this, run this .sql query to see which computers, the version, and path.

select
  s1.netbios_name0,
  ci.displayname,
  rooles.RuleName,
  perclientdetails.DiscoveredValue,
  perclientDetails.InstancePath as 'Found In'
from
v_localizedciproperties ci
join vDCMDeploymentNonCompliantRuleDetailsPerClientMachine perclientDetails
 on perclientdetails.ci_id=ci.ci_id
join v_CIRules rooles on rooles.rule_id=perclientdetails.rule_id
join v_r_system s1 on s1.ResourceID=perclientDetails.ItemKey
where
  ci.displayname = 'File Inventory Dropbox.exe'
 and
 ci.localeid = 1033
order by s1.Netbios_Name0

One interesting caveat: every time you change a ConfigItem (add something) the vDCMDeploymentNonCompliantRuleDetailsPerClientMachine will "reset", so if you don't want to "lose" history, you'll want to likely simply make more CIs.  Not edit existing ones.

This sample was to show you can get version of any dropbox.exe file, for reporting purposes.  If, for example, what you really need to be able to do is create a collection of "machines where widgets.exe located in c:\program files\widgets is less than version 5.4.3.2", then make a ConfigItem for widgets.exe, in that folder, and "compliant" means version is greater than or equal to 5.4.3.2.  You can then easily right-click the CI and make a collection of Non-Compliant machines.

I encourage you to test it out for yourself; and see how quickly on a client a CI runs; vs. software file inventory for the same file. 

CMCB, SCCM

  • Created on .

WSUS (SUP) Servers in ConfigMgr 2012 custom Configuration Settings

Issue(s) to be resolved:

  1. WSUS Server (the SUP servers in Configmgr 2012), RapidFail currently Enabled (TRUE).  For our WSUS pool, during a particularly heavy patch release cycle, the clients were timing out, and going into retry mode.  This caused the Application Pool for WSUS (called wsusPool) to fail and not restart on it's own.  The clients continued to try; and due to the large number of failures and timeouts were "failing over" to other WSUS servers, which prompted them to need a full sync; and then those failover servers were getting hit hard and the wsuspool on them were failing and not restarting on their own.
  2. Ensure the PrivateMemoryLimit on the WsusPool is 20gb, presuming the server has 21gb or more of memory.

Manual Fix: On the WSUS Servers, RapidFail on the wsusPool was set to FALSE, PrivateMemorylimit was set to 20971520

Automated Fix: We wanted to be sure that if we created more WSUS servers, or for some unknown reason some iis reset or patch would reset RapidFail to TRUE... it would automatically be set back to FALSE. 

What we've implemented is a Compliance Setting using Powershell scripts.  Import --> This <-- into your console, Compliance Settings, Baselines.

What you'll likely want to tweak for your own environment is perhaps your SUP servers shouldn't have 20gb ram dedicated to the WSUSpool--you have different settings.  In the CI currently called "WSUSPool Private Memory Limit should be 20971520", edit multiple things:  

  1. Detection methods, check that the "Greater Than" for how much totalmemory your SUP servers have matches your reality.  If your SUP servers have 16gb, and you want to give 8gb (for example) to the Wsuspool, then change that -gt to something like 8000000000 
  2. Settings, for the Discovery, no change  For the Remediation, change the -Value to match exactly what you intend to have your wsuspool memory be; i.e., if you want to ensure it's 8gb, 8 * 1024 * 1024 = 8388608  
  3. Compliance, change the value to match (8388608)  
  4. you'll likely want to change the names of the rules from "should be 20971520" to instead match your reality.

When you deploy the baseline, make sure you check the box about remediation as well.  Target your SUP servers (by collection), and of course, test that remediation works like you expect it to work.

 

 

 

 

 

CMCB

  • Created on .

Configmgr 2012 Truncate History Tables

Updated 2019-04-29, with DRS replication in ConfigMgr Current Branch, global data tables might be hit with this _hist truncate, the sql below has been modified to exclude global data types, so that your replication (for example, in rcmctrl.log) doesn't go crazy about having to re-initialize any data.  Thanks very much to Umair Khan, Twitter @TheFrankUK, for the assist!

Have you ever noticed, being the extreme ConfigMgr geek that you are, that you have v_gs and v_hs views?  Which point back to current, and History tables in your database.

Have you ever, and I mean EVER, needed to reference anything in the v_hs views?  Ever?  If you have, then perhaps this isn't for you.  If you've never used the data in the history views... why are you keeping it?  Sure, there are Maintenance Tasks you can tweak to help keep that data down, but... there is a quick (not supported) way to clean that up.

Keeping in mind this is NOT SUPPORTED (but it works anyway), so do this at your own risk, etc. etc.  If you mess up, I don't support you.  Microsoft won't support you.  You have a backup of your database, right?

On your Primary Site (even if you have a CAS, you still do this at your primary sites), all of this is done in SQL, the console is not involved at all.

Take the below, and in SQL management Studio, just take a look at how much History data you have.  Only you can determine if that's cause for concern, and you want to automate cleaning that up using a SQL Truncate process.  At my company, in the 12+ years that people on this team have been supporting SMS, then ConfigMgr...no one ever needed data in the History tables.  So...for us this was a lot of space gained, that didn't need to be backed up, and made nightly processing of some of the maintenance tasks that look at history tables finish MUCH faster than they have in months.

John Nelson (aka, Number2) would run the Truncate manually occasionally; but after a while that gets tedious.  :)  So he showed me how to see what is going to be truncated (query #1) and then how to make a Scheduled Job that runs daily, to actually do the Truncate of History tables.

Query #1: This particular query is only to look at what you have.  It does nothing but show you results.  Run this against all of your ConfigMgr sites with a CM_ database; and see if there is history you want to truncate.  If so, you may want to then move on to running SQL #2 (below).

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN
          sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN
    ArticleData AD on AD.ArticleName = T.Name
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.NAME LIKE '%[_]HIST'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    AND AD.ReplicationID not in (Select ID from ReplicationData where ReplicationPattern = 'global')
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    rowcounts desc

SQL #2:  This will CREATE a job, with a daily schedule.  Before you run it, change CM_FUN to be your CM_<your Site Code> ; and you may also want to change
                @active_start_date=20140909
prior to running it, to whatever date you want the daily schedule to really start.  Once created, presuming SQL Server Agent is running, on that SQL server, for the Databse of CM_<whatever you put in>, it'll truncate your history tables on the schedule defined.

Optional:  After you've run the below, in your SQL Management Studio, Sql Server Agent, Jobs, if your right-click on the new job "ConfigMgr Truncate History Tables", you can select "Start Job at Step..." to have the job run RIGHT now; to confirm it works.  Once it's done, you can re-run query #1 above and see that it's clean(er).  Note that as machines report inventory, data will go into the history tables frequently.  You may already have new rows after you just ran the Truncate job, but it should be much less than it was.

Optional:  The next day, or weekly, or monthly...whatever schedule you have internally for checking up on your ConfigMgr infrastructure, every once in a while, run Query #1 above; and/or every once in a while, in SQL go to SQL Server Agent, Jobs, right-click on the Configmgr Truncate History Tables job, and select "View History", to see that the job was successful.

USE [msdb]
GO /****** Object:  Job [ConfigMgr Truncate History Tables]    Script Date: 9/8/2014 2:05:50 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 9/8/2014 2:05:51 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ConfigMgr Truncate History Tables',
                @enabled=1,
                @notify_level_eventlog=0,
                @notify_level_email=0,
                @notify_level_netsend=0,
                @notify_level_page=0,
                @delete_level=0,
                @description=N'Truncate ConfigMgr database History tables',
                @category_name=N'[Uncategorized (Local)]',
                @owner_login_name=N'NT AUTHORITY\SYSTEM', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Truncate]    Script Date: 9/8/2014 2:05:51 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N'Truncate',
                 @step_id=1,
                @cmdexec_success_code=0,
                @on_success_action=1,
                @on_success_step_id=0,
                @on_fail_action=2,
                @on_fail_step_id=0,
                @retry_attempts=0,
                @retry_interval=0,
                @os_run_priority=0, @subsystem=N'TSQL',
                @command=N'USE [CM_FUN]
GO
DECLARE @SQL NVARCHAR(MAX) = N''
''
SELECT
  @SQL = @SQL+N''TRUNCATE TABLE dbo.''+TABLE_NAME+'';
''  
FROM
  INFORMATION_SCHEMA.TABLES x
  INNER JOIN ArticleData A on x.TABLE_Name = A.ArticleName
WHERE
  x.TABLE_SCHEMA = ''dbo''
  AND x.TABLE_NAME LIKE ''%[_]HIST''
  AND A.ReplicationID not in (Select ID from ReplicationData where ReplicationPattern = ''global'')
ORDER BY
  x.TABLE_NAME
exec sp_executesql @SQL
',
                @database_name=N'CM_FUN',
                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email protected], @name=N'ConfigMgr Truncate Hsitory',
                @enabled=1,
                @freq_type=4,
                @freq_interval=1,
                @freq_subday_type=1,
                @freq_subday_interval=0,
                @freq_relative_interval=0,
                @freq_recurrence_factor=0,
                @active_start_date=20140908,
                @active_end_date=99991231,
                @active_start_time=231100,
                @active_end_time=235959,
                @schedule_uid=N'9936718a-af85-497b-ac0d-d47d91ce99d8'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTIONGOTO End
Save QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

 

 

CMCB, SQL

  • Created on .

Selectively Disable Software Distributions and Application Deployments on Clients

Credit to Niall Brady of http://windows-noob.com fame (Thanks Niall!)

You might want to separate certain computers with the Configuration Manager client agent installed by disabling the ability to install or run available (optional) or required (mandatory) Application Deployments, or Packages/Advertisements. You could achieve this by moving the computer into a collection which is excluded from All deployments but what if someone accidentally added should-be-excluded computers to a collection containing a required deployment?  It may lead to a reboot or something entirely worse. The ability to disable the Software Distribution Agent and the Application Deployment Agent would indeed be useful in this scenario.

Normally you can enable or disable System Center 2012 R2 Configuration Manager Client agent functionality via client settings in the console; however the Software Distribution Agent and the Application Deployment Agents are an exception. For those agents, to disable the software distribution agent and Application Deployment Agent, a local policy can be used. To re-enable it when needed that local policy is deleted, allowing the Site-wide settings to be reapplied.  Although it is certainly possible to do this using "mof" files and importing them, the method outlined here will use Compliance Settings to disable or enable those two agents on one or more computers depending on the collection they are in.

How To Step 1:  Attached --> Here <-- are two files.  In your Console, Compliance Settings, Import both of those .cab files by right-clicking on Configuration Baselines, Import, and pointing to each of those .cab files.  Once imported, to a previously created-by-you collection of computers you wish to disable Software Deployments, deploy the "Disable Software Distribution and Application Deployments" baseline to that collection.  Very carefully make sure you check the box about "Remediation".  In general, I recommend a schedule of daily; but really, once this is deployed those clients have this local policy. Weekly is likely frequent enough.

And... that's it.  You are really done at this point. 

Optional Step 2:  The baseline disables those two agents on clients that run it (with remediation enabled), but it was mentioned to me (Todd Hemsell and Eswar Koneti pointed it out) that it may still be possible for a person interactively logged on to the machine might still be able to see deployments in Software Center, and choose to manually install them.  If you want to prevent that possibility as well this should work (but test, I didn't test this myself), in your console, Administration, Client Settings, create a Custom Client Agent Device Setting; add Computer Agent, and in there, set the "Install Permissions" to "No Users", and deploy that custom client device setting to the same collection.

...time passes...

So, now it's months/years later.  And you want to either find out how many local policies are out there in your environment, or want to remove those local policies.  If you want to just inventory to find them, implement this: http://myitforum.com/cs2/blogs/skissinger/archive/2009/07/06/hardware-inventory-mof-edit-for-local-policies.aspx .  If you want to undo those local policies which were created by the Baseline, first remove the existing deployment; and then deploy the other Baseline of "Remove Previously Created Local Policies to Disable SWDist and App Deployment" (One of the baselines which you imported from the .zip attachment above, but never deployed).  Once the local policy is removed, whatever site-wide settings you have in default Client settings will be applicable to those machines again.

 

CMCB

  • Created on .

Some Limited Windows Installer Event Information via Compliance Setting

I hesitated to blog this particular compliance setting for two reasons.

#1: the script inside could potentially (unlikely but it COULD) open an instance of a local exe (wevtutil.exe) and not close it properly.
#2: This is very 'big brother' like; it's got nothing to do with maintaining a system and keeping it healthy, and is completely about some governing body / management team wanting to point fingers.  If the user "sherry" isn't supposed to be installing things, then don't let "sherry" have local admin rights. Don't point fingers later.

With that said, use this baseline at your own risk, and I make no promises about it at all.  It's on shaky ground for how useful it is, and how reliable it is.  For example, I would NEVER tell Management "why yes, we can 100% get that data for you."  You would instead have to make it VERY clear that "this data is unreliable, and we may have to disable it at any time, but we can run it for a (week/month) and get you some data to prove that you really need to work toward removing local Admin rights to <some group of people>, but then we should disable it, we shouldn't keep it enabled forever."

Step 1: Attached --> Here <-- are two files, in your Console, Compliance Settings, import the "EventLogWindowsInstallerInfo.cab" by right-clicking on Configuration Baselines, Import and pointing to that Cab file.  Deploy that baseline (monitor only, no remediation needed) to a pilot collection of computers to verify, and once verified, deploy it to the correct target collection (whatever correct means to you).

Step 2: In your console, Administration, Client Settings, right-click properties on Default Client Agent, Hardware Inventory, Classes... Import... the file "To_import_into_Default_Client_Settings.mof"  The reason to import that file vs. browsing to WMI and selecting the class from a client which has this new custom class is because this mof to be imported has a custom name for the table and view; "hopefully" people, when looking at the console for collection queries, will NOT want to use this data for creating a collection--it makes no sense to do so.  This data should be used for reports only, and never to build a collection.

Various notes and caveats about the script inside the Compliance Setting.  It relies on data regarding Windows Installer installations reported to the Event Log.  With that in mind, if you have EventLog management policies or practices which clear the event log or your event logs quickly roll over, the data may simply not be there to be gathered.  Only Windows Installer events which resulted in a 0 or 3010 return code (a success, no errors or failures) will be gathered.  When I built this originally, the point was to "find rogue users installing things instead of using ConfigMgr", so only if a username is associated with that 0 or 3010 Windows Installer entry, will it be recorded.  The script inside relies on wevtutil.exe being available in %windir%\system32--if it's not, the script just exits and does nothing.  So as you can see there are several ways and places this routine is only marginally reliable.  Again--USE AT YOUR OWN RISK, and CAREFULLY explain to management (who are likely the ones asking you for this information) how unreliable it is or could be.  It is NOT foolproof by any stretch of the imagination.

To confirm, on your pilot box(s), confirm that the Compliance Setting Baseline has run, and that Hardware Inventory has run since receiving the updated hardware policy, and since running the Compliance Setting.

Potential sample report... You don't really care about installs from 'NT AUTHORITY\SYSTEM' (because that's either ConfigMgr or some other approved process), so you really just want those event where it's NOT that username.  If you have other "approved" user IDs you could also exclude those.

select s1.Netbios_Name0, rii.InstallDate0, rii.InstallUser0,
rii.Manufacturer0, rii.Name0, rii.Version0, rii.ScriptLastRan0
from v_GS_ReportOnlyInstallInfo0 rii
join v_R_System s1 on s1.ResourceID=rii.ResourceID
where s1.Netbios_Name0 like @ComputerName
and rii.InstallUser0 not in ('NT AUTHORITY\SYSTEM',s1.Netbios_Name0 + '\SomeLocalAdminIDWhichIsKnownToBeOK')
order by rii.InstallDate0 desc

Which if I run that against my lab CM12 server, some account called "TheAdmin" was installing stuff (see below).  It would be up to a human to determine if whatever "TheAdmin" was installing was acceptable or not to be doing that.  In this case, yes, it's acceptable--some account had to install the elements of R2 and Cumulative Update 1 for R2 onto the CM12 server.  As a human, I know that particular account isn't a domain admin, and has been granted rights to that server via the local Administrators group on that server--but nowhere else.  So it's acceptable use--but again... only a human can make those logical determinations.  Note that there's no indication of anything being installed prior to March.  The log on the server had rolled, and windows installer events prior to March 2014 simply weren't there to be gathered.

EventVwrInfo

  • Created on .
Copyright © 2020 - The Twin Cities Systems Management User Group