MEMCM Inventory Installed Windows Capabilities

It used to be (prior to 1809 Windows 10) that one could inventory the wmi class win32_optionalfeatures and know if RSAT was installed or not. Apparently that is no longer the case; and from what I could discover online, the only supported method is to use the powershell command Get-WindowsCapability -online (with additional filters if desired).

Gary Blok also found that the information for "Installed" things would also show up HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\ComponentDetect -- as subkeys. Unfortunately, that isn't easily translated into a "just a mof edit".

In order to tease out this information, it will need to be a script to populate a custom WMI class anyway, using the POSH command is much easier than parsing those regkeys. (at least, in my opinion)

If, like me, you are tasked with "Create reports to know who has RSAT feature enabled on Windows 10", this may be a solution for you.

Attached --> Here <-- is a zip file, with the script.

To use this...

  1. in your console create a new Configuration Item, call it whatever you want. Example: Inventory Staging for Get-WindowsCapability
  2. under Settings, it will be a "Script", "String"
  3. Paste in the contents of the text file. The "test for compliance" will be Existential, that any value is returned at all.
    1. OPTIONAL; the script is written presuming you only want the "Installed" results to be inventoried. If you want both installed and not installed capabilities, change the variable "$TypesToGet" from "Installed" to instead say "Both"
  4. Add this CI to a Baseline, deploy the baseline.
    1. Optional, testing. On a device which has run the baseline, using your favorite WMI viewing tool (I use WMIExplorer) go check out the local results in root\cimv2\cm_WindowsCapability. If you have results, yay, it worked.
  5. In your Console, Administration, Client Settings, Default Client Settings, right-click properties, Hardware Inventory. Set Classes... and click on "Add..." connect to that sample computer, root\cimv2, and find cm_WindowsCapability in the results, and add that. Monitor your server's dataldr.log to confirm all is well.
  6. Wait. Wait some more. After you've waited long enough, go look at select * from v_gs_cm_windowscapability0

Sample SQL query, if you are looking for Machines with RSAT Active Directory Tools

select s1.netbios_name0, wc.name0, wc.state0
from v_r_system s1
join v_gs_cm_windowscapability wc on wc.resourceid=s1.resourceid
where wc.name0 like 'Rsat.ActiveDirectory.DS-LDS.Tools%'
order by s1.netbios_name0

CMCB, SQL

  • Created on .

CM All Members of All Local Groups - Powershell

"Back in the Day", --> Here <-- a vbscript was created to allow for ConfigMgr (version 2012 at the time was the version I was using) to be able to custom inventory the members of Local Groups. This was mostly in response to manager-type requests to know "what individuals or groups are inside the local Administrators group". That has been working fine for years... but times change, and it may be more palatable to use Powershell.  Powershell is more widely used and understood instead of vbscript. Additionally, if your company requires it, you can sign powershell scripts so you know they are tested and trusted internally.

**WARNING** as of August, 2021... This has been tested just in ONE and only ONE small lab environment, with only 2 clients. If/when I hear from people that this works "fine", I'll update this blog. Until that time...I strongly suggest that you test this in your lab environments thoroughly. Don't just blindly trust this. It is definitely a work in progress, and may have so many flaws that you'll break something, horribly. Test. Test. Test.  I also suggest you read the scripts; I did try to over-explain and add comments everywhere; but as with anything you might randomly find on the internet--I suggest you read it through first.  Know what it is and what it is trying to do, and/or test it interactively on a standalone lab box.  "Trust, but verify".

--> Attached <-- is a zip file containing 2 ps1 scripts (renamed from .ps1; in case your anti-malware flags and blocks script files), a mof file to be imported, and a basic sql query to get you started.

How to use the attached... If you are familiar with CM Configuration Items, and the concept of "script + mof edit", the below is over-explained. If you are already familiar with the concepts, just download the attachments and set it up in your lab for testing; and once you are comfortable, deploy it as you like.

  1. In your CM console, go to Assets and Compliance, then Compliance Settings, then Configuration Items.
  2. Create a Configuration Item. When prompted, give it a name (Name is up to you and your standards. For the purposes of this information, I'm calling the Configuration Item "Inventory Staging for Local Group Members with Logging"
  3. This is a "Windows Desktop and Servers" type; you *do* want to check the box for "This configuration item contains applications settings.
    1. Add a description if you like; perhaps the link to this blog, or the date you added this, and what manager-type wanted this information; whatever might be useful 2 years down the road when the person that comes after you is trying to figure out what this is for and why.
  4. Next.
  5. Detection Methods, select "Use a custom script to detect this application". That script will be the one in the attachment, labeled "ApplicabilityForTheCI.Rename-to-ps1". What that does is it checks the client to see if it's a Domain Controller. If it *is* a Domain Controller, then the Configuration Item is NOT APPLICABLE, and it won't run the script inside. The script itself also does a check, and bails; and hopefully you will also on purpose not EVER target your domain controller(s) with this... but mistakes happen. The more places to ensure that a DC won't be asked these types of questions, the better you'll feel about having this in your environment. You certainly don't want your DCs to try to do this.
  6. Next
  7. Settings, New... Give it a Name (any name), and a description.
    1. Setting Type = Script
    2. Data Type = String
    3. Add Script...Script Language=Powershell and copy and paste in the script contained in the attachment labeled "MainScript.Rename-To-ps1"
      • Optionally... Sign the script according to your company standards
      • Optionally... Change the logging location from %temp% to the CM client log folder (it's within the script, just comment/uncomment the correct lines
      • Optionally... Turn off a local log file completely, according to your company standards.
  8. within the Settings area, at the top change from "General" to "Compliance Rules".
    1. New...Rule Type = Existential, and you want the default choice of "The specified script returns at least one value".
  9. Ok. Ok. Hit Next/Next/Next however many times until it's done and saved.
  10. In your CM Console, go to Assets and Compliance, then Compliance Settings, Configuration Baselines
  11. Create Configuration Baseline, give it a name and description; again--according to your own standards, and try to leave a good description for the person coming after you to know what this is for and why.
  12. Add, Configuration Items, and find the one you created above. Assuming you called it exactly what I called it, it'll be called "Inventory Staging for Local Group Members with Logging". Click Add, then OK.
  13. Don't hit the next OK yet. Select that name in the middle, and you want to "Change Purpose" from Required to Optional. NOW hit OK.
  14. If you don't yet have a collection of Test devices, go make a collection of test workstations and/or Server clients. Once you have a collection of devices (ideally, ones to which you have rights to look at their %temp% or cm logs remotely), Deploy this baseline to that collection. Frequency to run is up to you, but I would suggest no more frequently than every 3 days--honestly, this inventory staging isn't that important. Every 7 days is most likely fine.

  15. TEST TEST TEST

    On those test devices, trigger policy refreshes, and when the baseline appears, have it run. Depending upon which log location you set, you can check that log location for the log file. Additionally, you can use your favorite WMI browser (WMIExplorer?) to check root\cimv2\cm_localgroupmembers and see if what will be reported, matches reality.

  16. Once you have confirmed it does what you want it to do, you will want to setup ConfigMgr to be able to inventory this custom WMI Class. NOTE!!! if you have previously used the vbscript Configuration Item; this is the exact same wmi class name--it may be that you will first have to delete the existing "CM_LocalGroupMembers". Every environment is different, so I can't predict what you may or may not need to do, in your environment for this customization. In general, in your CM Console, go to Administration, Client Settings, right-click "Default Client Settings", Properties, then Hardware Inventory. From the attachment, have the "ToBeImported.mof" available. Set Classes... then Import the .mof.
  17. MONITOR your <server, CM installed location>\Logs\dataldr.log and confirm the mof is imported successfully, and the view is created.
  18. On those test clients (remember, you have NOT deployed this yet to most devices); wait a bit, then policy refresh. Then do a Hardware Inventory action. Monitor the client's inventoryagent.log, and hopefully you'll see the wmi query for select...from cm_localgroupmembers. Wait a bit for your server to process that inventory, then using SQL (or I suppose, resource explorer) to check that box' inventory--confirm the values were reported.
  19. Once you've confirmed that all the sections work--from the CI/Baseline, to inventory, then you can deploy that Baseline to the devices you want to report; that's up to you of course. all workstations? all workstations and all servers (but NOT Domain Controllers)? Just that <insert annoying internal team that always tries to bypass the rules and puts a random local user in the local Administrators group, because "they need it" (even when every company policy says to never do that, so they need to get yelled at by upper management, and you have to tell upper management who they need to yell at, using this routine)> ?

Sample SQL to get you started... This would be "show me users and groups which are in the local Administrators group, where it's not "Domain Admins"
select
s1.netbios_name0 as 'ComputerName'
,lgm.Account0 as 'Account or GroupName'
,lgm.Category0 as 'Category'
,lgm.Domain0 as 'Domain or Local ComputerName, if Associated with this Account'
,lgm.Enabled0 as 'if local account, is it enabled'
,lgm.name0 as 'Name of the Local Group on this device'
,lgm.Type0 as 'Type of account according to Get-LocalGroupMember, PrincipalSource'
from v_GS_LocalGroupMembers0 lgm
join v_r_system s1 on s1.resourceid=lgm.ResourceID
Where lgm.Name0 = 'Administrators'
and lgm.account0 not in ('Domain Admins')

Or... "within the local Administrators group, show me groups which aren't Domain Admins, and anything else which is enabled or null"
select
s1.netbios_name0 as 'ComputerName'
,lgm.Account0 as 'Account or GroupName'
,lgm.Category0 as 'Category'
,lgm.Domain0 as 'Domain or Local ComputerName, if Associated with this Account'
,lgm.Enabled0 as 'if local account, is it enabled'
,lgm.name0 as 'Name of the Local Group on this device'
,lgm.Type0 as 'Type of account according to Get-LocalGroupMember, PrincipalSource'
from v_GS_LocalGroupMembers0 lgm
join v_r_system s1 on s1.resourceid=lgm.ResourceID
Where lgm.Name0 = 'Administrators'
and lgm.account0 not in ('Domain Admins')
and (lgm.Enabled0 = 'true' or lgm.Enabled0 is null)

CMCB, ConfigMgr

  • Created on .

MEMCM IIS Settings you may want for your Management Points and Distribution Points

Over the years we've uncovered various iis settings for our Management Points and Distribution Points, which we've found needed tweaking (for a company our size and complexity). Perhaps none of these settings will be relevant in your environment. If you have some issues with your clients' ability to communicate to IIS these settings may be a starting point for your troubleshooting or remediation of your MPs and DPs.

These are all Configuration Items.
1 Test and Remediation for any ManagementPoint role servers
4 Test and Remediation for any DistributionPoint role servers

Since I've been told that trying to import an exported .cab of these CIs often fails, I'm instead going to list out every setting and script inside, instead of trying to make it "easy" by offering a .cab for import.

You'll want to make all of these CIs "Application" Type CIs. That is so that you can add all 5 rules to a baseline, and target the baseline to a collection of "all your CM Servers", without having to break up and maintain collections for "these are the MP server", and "these are the DP servers". Let the CI do the "should I bother" check, using the application detection logic.


Management Point ones--you only want your servers with the MEMCM Management Point role to deserve this CI. This is what I currently have as the application detection logic:


<#
.SYNOPSIS
This is to check if the server has a MP role
#>

Param (
$VerbosePreference = 'SilentlyContinue',
$ErrorActionPreference = 'SilentlyContinue'
)
$Value = (get-itemproperty 'HKLM:\software\Microsoft\sms\mp' | Select IISPortsList).IISPortsList
if (-not ([string]::IsNullOrEmpty($Value))) {
write-host $Value
}

Distribution Point Ones--you only want your servers with the MEMCM DP role and IIS to deserve these 4 CIs. This is what I currently have as the application detection logic for the 4 CIs for the DP ones:

 


<#
.SYNOPSIS
This is to check if the server has a DP role
#>

Param (
$VerbosePreference = 'SilentlyContinue',
$ErrorActionPreference = 'SilentlyContinue',
$WebServerInstalled = (Get-WindowsFeature -Name Web-Server).InstallState
)
$Value = (get-itemproperty 'HKLM:\software\Microsoft\sms\dp' | Select ContentLibraryPath).ContentLibraryPath
if (-not ([string]::IsNullOrEmpty($Value)) -and ($WebServerInstalled -eq 'Installed')) {
write-host $Value
}


For each individual CI...
The single Management Point role CI is this:

  1. applicationPoolDefaults queueLength should be 4000
    Script, Integer
    Why is this needed? IIS default out of the box is 1000. MEMCM supports 4000. the reason you want the max is if you have a lot of clients (more than 1000) all trying to communicate to the server, the machines over 1000 may get communication failures. This can result in clients not able to download policy, nor able to transmit information to the Management Point.
    1. Discovery Script:
      import-Module webadministration
      (Get-WebConfiguration /system.applicationHost/applicationPools/applicationPoolDefaults).queueLength
    2. Remediation Script
      import-Module webadministration
      Set-WebConfigurationProperty /system.applicationHost/applicationPools/applicationPoolDefaults -Name queueLength -value 4000
    3. Compliance Rule is that this will be an Integer of 4000
      1. Make sure you check that box about 'Run the specified remediation script when this setting is noncompliant' (if you forget, then even if you deploy the baseline w/remediation, it still won't remediate)

So... that was the easy one; just the MP role one; to allow for more clients to chat. Distribution Point IIS settings; we've had to tweak multiple things over the years. The following 4 things are for your DP Role Servers.  The next 4 CIs would be ones you create using "application detection logic" of a DP role (mentioned above)

  1. applicationPoolDefaults queueLength should be 4000
    Script, Integer
    Why is this needed? IIS default out of the box is 1000. MEMCM supports 4000. the reason you want the max is if you have a lot of clients (more than 1000) all trying to communicate to the server, the machines over 1000 may get communication failures. This can result in clients not able to download content.
    1. Discovery Script:
      import-Module webadministration
      (Get-WebConfiguration /system.applicationHost/applicationPools/applicationPoolDefaults).queueLength
    2. Remediation Script
      import-Module webadministration
      Set-WebConfigurationProperty /system.applicationHost/applicationPools/applicationPoolDefaults -Name queueLength -value 4000
    3. Compliance Rule is that this will be an Integer of 4000
      1. Make sure you check that box about 'Run the specified remediation script when this setting is noncompliant' (if you forget, then even if you deploy the baseline w/remediation, it still won't remediate)
  2. SMS Distribution Points Pool appConcurrentRequestLimit should be 65535
    Script, Integer
    Why is this needed? If it's not max allowed, what could happen is 503.2 IIS errors on the Distribution Points, this alleviates those errors.
    1. Discovery Script
      <#
      .SYNOPSIS
      Query applicationHost.config, <configuration> , <system.webServer>,
      change <serverRuntime /> for appConcurrentRequestLimit
      .DESCRIPTION
      Query applicationhost.config, <configuration> <system.webServer>, <serverRuntime />
      .NOTES
      Why: Part of alleviate the 503.2 IIS errors on the Management Points
      2019-12-05 Sherry Kissinger
      .EXAMPLES
      #>
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      Import-Module WebAdministration
      (Get-WebConfigurationProperty -pspath 'MACHINE/WEBROOT/APPHOST' -filter "system.webServer/serverRuntime" -name "appConcurrentRequestLimit").Value
    2. Remediation Script
      <#
      .SYNOPSIS
      Edit applicationHost.config, <configuration> , <system.webServer>,
      change <serverRuntime />
      to <serverRuntime appConcurrentRequestLimit="65535" />
      .DESCRIPTION
      Modify applicationhost.config, <configuration> <system.webServer>, <serverRuntime />
      .NOTES
      Why: alleviate the 503.2 IIS errors on the Management Points
      2019-12-05 Sherry Kissinger
      .EXAMPLES
      #>
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      Import-Module WebAdministration
      Set-WebConfigurationProperty -pspath 'MACHINE/WEBROOT/APPHOST' -filter "system.webServer/serverRuntime" -name "appConcurrentRequestLimit" -value 65535
    3. what means compliant: 65535
      1. Make sure you check that box about 'Run the specified remediation script when this setting is noncompliant' (if you forget, then even if you deploy the baseline w/remediation, it still won't remediate)
  3. SMS Distribution Points Pool RapidFail Should be Disabled
    Script, String
    why is this needed? iis defaults to Stopping (and not restarting) Application Pools if "too many" errors are encountered. Well, in an environment our size... we get errors all the time. We'd rather client keep trying to communicate, even if it generates iiserrors. We certainly don't want the application pools to stop.
    1. Discovery Script
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      import-Module webadministration
      (get-itemproperty 'IIS:\AppPools\SMS Distribution Points Pool' -name failure.rapidFailProtection).Value
    2. Remediation Script
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      import-Module webadministration
      set-Itemproperty 'IIS:\AppPools\SMS Distribution Points Pool' -name failure.rapidFailProtection False
    3. what means compliant, the returned value = False
      1. Make sure you check that box about 'Run the specified remediation script when this setting is noncompliant' (if you forget, then even if you deploy the baseline w/remediation, it still won't remediate)
  4. SMS Distribution Points No FileExtensionFilters
    Script, Integer
    why is this needed? by default, IIS will filter some file extensions. For us, occasionally files within content attempting to be downloaded would include files with those exact extensions, like a .mdb or .vb or .config, etc. etc. This would result in the client claiming "hash mismatch", because quite correctly IIS had a Request Filtering rule denying the ability to download a file from IIS ending in .mdb / .vb / whatever. But... we *DO* need the ability for files of that type to be downloaded into cache; if that is what is in the source files for an application, that is what we need to support. This will remove all fileextension filters, if there is a DP role.
    1. Discovery Script
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      import-Module webadministration
      $CountFileExtensionFilters = (Get-WebConfigurationProperty -Filter 'System.WebServer/Security/requestFiltering/fileExtensions' -PSPath 'IIS:\Sites\Default Web Site' -Name 'Collection' | Measure-Object).Count
      Write-Host $CountFileExtensionFilters
    2. Remediation Script
      $VerbosePreference = 'SilentlyContinue'
      $ErrorActionPreference = 'SilentlyContinue'
      import-Module webadministration
      Remove-WebConfigurationProperty -Filter 'System.WebServer/Security/requestFiltering/fileExtensions' -PSPath 'IIS:\Sites\Default Web Site' -Name 'Collection'
    3. what means compliant, equals  0
      1. Make sure you check that box about 'Run the specified remediation script when this setting is noncompliant' (if you forget, then even if you deploy the baseline w/remediation, it still won't remediate)

 

Then of course.. TEST TEST TEST.

Add these 5 new CIs to a Baseline, and deploy to a single server with one of the roles; and see "what would happen if...".  If you are satisfied it might be helpful, you can then delete the deployment, and redeploy "with remediation", and test again.

 

CMCB

  • Created on .

Reporting on Attached Monitor info as available in WMIMonitorID

I thought this information was already blogged by someone else--I certainly know I stole it from someone else years ago. But now I can't find that blog. If this is your work; please accept my apologies for not crediting you correctly.

Reporting on "Attached Monitors" is occasionally something which your business requests. The best solution in my humble opinion is from Enhansoft.com, part of their Reporting suite includes a client-deployed utility for exhaustively being able to report on attached monitor information https://www.enhansoft.com/products-services/enhansoft-reporting/ 

However, if you don't have any budget at all; but are still tasked with getting "attached monitor information", although it's a poor second, you can get 'some information' out of a built-in WMI class. https://docs.microsoft.com/en-us/windows/win32/wmicoreprov/wmimonitorid 

Step 1: In your CM Console, Administration, Client Settings, right-click on Default Client Settings, select Properties. Go to the "hardware inventory" on the left, then "Set Classes..." on the right. Choose "Add..." and you want to connect to <some computer you have admin rights on> root\wmi (not root\cimv2), and find "WMIMonitoID". Add that. hit OK til out. Monitor <your server>\logs\dataldr.log; to see it being created, and the view created. Take note of the view which was created in your environment.

Step 2: Wait. You are waiting for clients to get this new policy, and then report hardware inventory using this new policy. Depending upon your environment, this could be minutes to hours to even a week--only you know your own environment and timings.

Step 3: Below is sql code to pull out the information from the 'not too friendly' info in that wmi class. Just make sure you replace the v_gs_wmimonitorID0 view with what it REALLY is for your environment. Your environment might not have called the view v_gs_wmimonitorID0; again; that could be unique to your environment.

If(OBJECT_ID('tempdb..#TempMonInfo') Is Not Null)
Begin
Drop Table #TempMonInfo
End

--###############################################
--Create #Temp Table, and insert specific data
--Data will be used later in the report
--##############################################

create table #TempMonInfo(
ResourceID int,
UserFriendlyName0 nvarchar(255),
UserFriendlyNameLength0 int,
UserFriendlyNameConv varchar(255),
ManufacturerName0 nvarchar(255),
ProductCodeID0 nvarchar(255),
SerialNumberID0 nvarchar(255),
WeekOfManufacture0 int,
YearOfManufacture0 int
)

insert Into #TempMonInfo
(ResourceID, ManufacturerName0, ProductCodeID0, SerialNumberID0, WeekOfManufacture0, YearOfManufacture0,
UserFriendlyName0, UserFriendlyNameLength0)
select
ResourceID, ManufacturerName0, ProductCodeID0, SerialNumberID0, WeekOfManufacture0, YearOfManufacture0,
UserFriendlyName0, UserFriendlyNameLength0
from v_GS_WMIMonitorID0

;WITH n AS
(
SELECT
NUMBER = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1, sys.all_objects AS s2
)
, final as (
SELECT
MON.ResourceID,
MON.UserFriendlyName0,
CONV_FN.VAL AS UserFriendlyNameConverted,
MON.ManufacturerName0 AS [Make],
CONV_MAKE.VAL AS MakeConverted,
MON.ProductCodeID0 AS [ProductCode],
MON.SerialNumberID0 AS [SerNum],
CONV_SN.VAL AS SerNumConverted,
MON.YearOfManufacture0 AS [YearOfManufacture],
MON.WeekOfManufacture0 AS [WeekOfManufacture]
FROM #TempMonInfo MON
CROSS APPLY ( SELECT
CASE
WHEN UserFriendlyName0 LIKE '%,%'
THEN (SELECT CHAR([value]) FROM (SELECT [Value] = SUBSTRING(MON.UserFriendlyName0, [Number],CHARINDEX(',', MON.UserFriendlyName0 + ',', [Number]) - [Number]) FROM n WHERE Number <= LEN(MON.UserFriendlyName0) AND SUBSTRING(',' + MON.UserFriendlyName0, [Number], 1) = ',') SPLT WHERE [value] > 20 FOR XML PATH(''),TYPE).value('.','varchar(max)')
ELSE UserFriendlyName0
END AS VAL) CONV_FN
CROSS APPLY ( SELECT
CASE
WHEN MON.ManufacturerName0 LIKE '%,%'
THEN (SELECT CHAR([value]) FROM (SELECT [Value] = SUBSTRING(MON.ManufacturerName0, [Number] ,CHARINDEX(',', MON.ManufacturerName0 + ',', [Number]) - [Number]) FROM n WHERE Number <= LEN(MON.ManufacturerName0) AND SUBSTRING(',' + MON.ManufacturerName0, [Number], 1) = ',') SPLT WHERE [value] > 20 FOR XML PATH(''),TYPE).value('.','varchar(max)')
ELSE MON.ManufacturerName0
END AS VAL) CONV_MAKE
CROSS APPLY ( SELECT
CASE
WHEN MON.SerialNumberID0 LIKE '%,%'
THEN (SELECT CHAR([value]) FROM (SELECT [Value] = SUBSTRING(MON.SerialNumberID0, [Number],CHARINDEX(',', MON.SerialNumberID0 + ',', [Number]) - [Number]) FROM n WHERE Number <= LEN(MON.SerialNumberID0) AND SUBSTRING(',' + MON.SerialNumberID0, [Number], 1) = ',') SPLT WHERE [value] > 20 FOR XML PATH(''),TYPE).value('.','varchar(max)')
ELSE MON.SerialNumberID0
END AS VAL) CONV_SN
)

Select
s1.Netbios_Name0 as 'Computername',
final.resourceid,
final.makeconverted
, UserFriendlyNameConverted
, final.ProductCode,
final.SerNumConverted, final.WeekOfManufacture, final.YearOfManufacture
,case when makeconverted = 'aaa' then 'Asus'
when makeconverted= 'ACI' then 'Asus'
when makeconverted= 'ACR' then 'Acer'
when makeconverted= 'APP' then 'Apple'
when makeconverted= 'ATL' then 'Atlona'
when makeconverted= 'BBY' then 'Insignia'
when makeconverted= 'BNQ' then 'Benq'
when makeconverted= 'CPQ' then 'Compaq'
when makeconverted= 'DCL' then 'DCLCD'
when makeconverted= 'DEL' then 'Dell'
when makeconverted= 'ELE' then 'Element'
when makeconverted= 'ELM' then 'Doublesight'
when makeconverted= 'EMA' then 'eMachines'
when makeconverted= 'ENC' then 'Eizo'
when makeconverted= 'EPI' then 'Envision'
WHEN makeconverted= 'FNI' then 'FUNAI/SYLVANIA'
when makeconverted= 'GSM' then 'LG'
when makeconverted= 'GWY' then 'Gateway'
when makeconverted= 'HKC' then 'V7'
when makeconverted= 'HPN' then 'HP'
when makeconverted= 'HRE' then 'Haier'
when makeconverted= 'ACR' then 'Acer'
when makeconverted= 'HSD' then 'Hanns.G'
when makeconverted= 'ACR' then 'Acer'
when makeconverted= 'HSP' then 'Hannspree'
when makeconverted= 'HTC' then 'Hitachi'
when makeconverted= 'HWP' then 'HP'
when makeconverted= 'IFS' then 'Infocus'
when makeconverted= 'IZI' then 'Vizio'
when makeconverted= 'LEN' then 'Lenovo'
when makeconverted= 'MED' then 'Medion'
when makeconverted= 'MEL' then 'NEC/Mitsubishi'
when makeconverted= 'NOK' then 'Nokia'
when makeconverted= 'PGS' then 'Princeton'
when makeconverted= 'PHL' then 'Philips'
when makeconverted= 'PLN' then 'Planar'
when makeconverted= 'PNR' then 'Planar'
when makeconverted= 'PTS' then 'Proview'
when makeconverted= 'SAM' then 'Samsung'
when makeconverted= 'SEK' then 'Seiki'
when makeconverted= 'SHP' then 'Sharp'
when makeconverted= 'SNY' then 'Sony'
when makeconverted= 'SPT' then 'Sceptre'
when makeconverted= 'SYN' then 'Olevia'
when makeconverted= 'TSB' then 'Toshiba'
when makeconverted= 'UPS' then 'Upstar'
when makeconverted= 'VIZ' then 'Vizio'
when makeconverted= 'VSC' then 'ViewSonic'
when makeconverted= 'WDE' then 'Westinghouse'
when makeconverted= 'WDT' then 'Westinghouse'
when makeconverted= 'WET' then 'Westinghouse'
else MakeConverted
end as 'BestGuessMake'
from Final
join v_R_System_Valid s1 on s1.resourceid=final.resourceid
-- Filtering out some Makes known to not be relevant ... at least when this report was created years ago.
-- Comment out the next line if you want these things anyway.
and Final.Make not in ('AUO','BOE','SEC','SDC','LGD','CMN','64, 64, 64, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0','77, 83, 95, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0','88, 72, 64, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0')

If(OBJECT_ID('tempdb..#TempMonInfo') Is Not Null)
Begin
Drop Table #TempMonInfo
END

CMCB, SQL

  • Created on .

ConfigMgr Inventory of Powershell Versions

If you happen to be curious about what versions of Powershell are installed/available on your clients, here's one way to pull out the information.  Note that the regkey locations for some of this information has changed from version 2 to higher versions, so it's completely possible that a future update to Powershell and the regkey location will change again; so if that happens a modification to these .mof files will be necessary.  As of Windows 8.1; these worked to report versions of Powershell installed.

Take the --> attached <-- and inside are two .mof files.  If you are ConfigMgr 2012, place the contents of the 'posh-configuration.mof.txt' at the bottom of your <inbox location>\clifiles.src \hinv\configuration.mof file.  In your configMgr 2012 console, in Client Settings, Default Agent Settings, Hardware Inventory, Classes... Import the 'posh-to-be-imported.mof'

Wait for clients to start reporting, once you get some clients reporting, the below sql query should get you started:

;with CTE as (
  select distinct resourceid
   ,RTRIM(substring(ISNULL((select ','+PSCompatibleVersion0  
        from v_GS_PowerShell0 p1
        where p1.ResourceID=t2.resourceid for XML path ('')),' '),2,2000)) as PSCompatibleVersions0
   ,RTRIM(substring(ISNULL((select ','+PowerShellVersion0
        from v_GS_PowerShell0 p1  where p1.ResourceID=t2.resourceid for XML path ('')),' '),2,2000)) as PowerShellVersions0
   ,RTRIM(substring(ISNULL((select ','+RuntimeVersion0
        from v_GS_PowerShell0 p1  where p1.ResourceID=t2.resourceid for XML path ('')),' '),2,2000)) as RunTimeVersions0
 from v_R_System t2
)
   select distinct sys1.netbios_name0 [ComputerName]
 ,cte.RunTimeVersions0 [RunTime Versions]
 ,cte.PSCompatibleVersions0 [PS Compatible Versions]
 ,cte.PowerShellVersions0 [PowerShell Versions]
 from v_R_System sys1
 left join CTE on cte.ResourceID=sys1.ResourceID

 

 

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