mem-zone / sccm-zone.com Goto Github PK

Hi Ioan,

Firstly I would like to say Thank you very much for writing the ‘SQL Version Detection and Reporting with SCCM’ article. We have implemented the code and are currently monitoring the results.

I have been asked by my manager to “get all the SQL Licences/product keys for every SQL instance”. Would it be possible for you to modify the code so that the SQL Licences/product keys are pulled from the registry and parsed using your SCCM code?

JAKOB BINDSLET has posted some PS code which parses the key from the registry:

http://mspowershell.blogspot.com/2010/11/sql-server-product-key.html

This would be extremely helpful to me (and many others I suspect).

Again – Many Thanks,
Matthew Lue

First of all thank you for this amazing script and making it publicly available!

This bug is for v4.0 version of the script which is the latest at the time of this writing.

Through testing I noticed that the function Get-CCMCachedApplications only handles install content for deployment types. This means that if a deployment type has separate uninstall content (which will have a different content id) the script will miss it and fail to clean it up.

Looking at the script, line 730 defines the action type of 'Install'. If you switch that to 'Uninstall' the script correctly finds uninstall content but then misses the install content. This also has the potential to error out since not all deployment types my have the action type of 'Uninstall'.

A potential fix would be to add another foreach statement to handle multiple action types.

Ex:

## Get application content ID
ForEach ($DeploymentType in $ApplicationDTs) {
  Foreach ($ActionType in $DeploymentType.AllowedActions) {
#The rest as is with ActionType using the $ActionType variable instead of a static string

This may solve the problem as well as handle the new repair action that can also have a separate content id. I'm still doing testing on a fix but that's what I have so far. If I am way off please let me know.

Jay-r Barrios
Hi, thanks for sharing your report working just fine. Can I ask how do I add a “TopConsoleUser0” under /* Device collection query */ I want to reference it as the PrimaryUser in my report as I want to pinpoint who’s the primary user of the computer. Thank you in advance.

Hello,

I had to revert to the version on Technet (v2.6 iirc) because your last version is cleaning only 10% of what the other script is cleaning. I tried with the most permissive switches but no luck, it ignores most of the content.

Regards

Trying to simplify to see what is going on.

DECLARE @LocaleID AS INTEGER = 1033;
--Select * from fn_DCMDeploymentCompliantDetailsPerAsset(@LocaleID) Where TargetCollectionID='xyx002D2'
--Select top 100 * from fn_DCMDeploymentErrorDetailsPerAsset(@LocaleID) Where TargetCollectionID='xyz002D2'
Select top 100 * from fn_DCMDeploymentErrorDetailsPerAsset(@LocaleID)

First query (compliant and most machines are compliant) 3 minutes 373K records
2nd query - 15 minutes when I killed it (should be fewer errors)
3rd query - over 4 minutes now to just get the top 100 and I killed it.

Even top 100 BLName, seems to run forever.

Thoughts?

-John

Howdy Ioan,

I found an issue if you have no users that have logged onto the device yet, at least in my environment, you get an error when trying to populate the $Packages variable. The error is a generic failure when trying to access the namespace. I would suggest adding ErrorAction SilentlyContinue to that line as so. I will try to attach the full error if I can get to it.
$Packages = Get-CimInstance -Namespace 'Root\ccm\ClientSDK' -ClassName 'CCM_Program' -Verbose:$false -ErrorAction SilenetlyContinue

Jeffrey Ng
nice report. possible to add in computer model and last user login id?
thanks

I'm gathering the data correctly via the Hardware Inventory, but I'm unable to get the report in Reporting Services to work. The file mentions that has a few requirements that I'm not familiar with. Can you provide some basic instructions on how to meet these requirements?

• Requires the usp_PivotWithDynamicColumns stored procedure.
• Requires SQL Property and ProductID extensions.

I'm using the latest HINV Classes and Configuration.mof properties and have let them sit in our environment for four days or so (with daily hardware inventories). I'm also using the latest copy of the SQL report. I'm getting an error when trying to generate a report.

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'SQLProductInfo'. (rsErrorExecutingCommand)
Error converting data type nvarchar to bit. | An error has occurred during report processing. (rsProcessingAborted)

Hi - When, I run the report, I get this error. I have full access to DB. Do, I need to change any permission within the DB?

Error Message:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'AdminID'. (rsErrorExecutingCommand)
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_rbac_GetAdminIDsfromUserSIDs", or the name is ambiguous.

I have followed your steps to update our SQL version info in SCCM but our SQL 2019 server does not report having SQL 2019 installed. I have done the following:

Added the HWI Extension to configuration.mof and compiled
Added HWI definitions to Default Client Settings and deployed an updated Client settings to the 2019 SQL server
I see the new extensions listed in Hardware Inventory Classes
I see the new views created in the SCCM database

But when I run "Get-CimClass -ClassName SQL_2019_Property", I get this error:
get-cimclass : Not found
+ CategoryInfo : ObjectNotFound: (root\cimv2:sql_2019_property:String) [Get-CimClass], CimException
+ FullyQualifiedErrorId : HRESULT 0x80041002,Microsoft.Management.Infrastructure.CimCmdlets.GetCimClassCommand

I can however retrieve the SQL_2017_Property and SQL_2014_Property classes from the same server.

@Yevrag35

Running the report gives me:

How do I begin debugging this?

Hello,

I read the code of the file HWI extension for reporting sql server in sccm

I think the line 231 should be Release = "2019"; and not Release = "2017";

Thanks

Hi loan,
I was looking to upgrade from the Technet version of this script (as this version seems so much more polished!) however I'm noticing in testing that most of the content in my test devices cache's is being skipped over.
I'm using the following Parameters:
Action = All
FreeSpace = 100
OlderThan = 0
$RemovePersisted = $true
SkipSuperPeer = $false

For Example on one Server I have 10 Cache Entries mostly containing Update CAB's at around 1.5gig each some of which are from January this year, but running the script doesn't seem to detect they're to be removed.
Checking the last referenced time in WMI shows they're old.
$Cachepath = ([wmi]"ROOT\ccm\SoftMgmtAgent:CacheConfig.ConfigKey='Cache'").Location
$cache = get-wmiobject -query "SELECT * FROM CacheInfoEx" -namespace "ROOT\ccm\SoftMgmtAgent"
Foreach ($cacheitem in $cache) {
$cacheitem.contentID
[Management.ManagementDateTimeConverter]::ToDateTime($cacheitem.LastReferenced)
}

NCU00008
21 January 2020 00:40:45
448ef3f9-86e9-497b-954e-653286ad418f
01 April 2020 01:33:28
NCU00003
28 November 2019 14:14:37
NCU00003
13 February 2020 16:19:56
8a7ad16e-b78e-4b88-91fe-35362d9b69a7
02 June 2020 04:41:12
9b842fe4-2040-40fe-be18-4b88dd88950f
05 February 2020 01:50:05
381e77dc-f15f-4c71-84e1-32d60f26e71a
01 April 2020 01:32:12
a0203bcb-ff0e-42cb-99de-72f265c2426e
08 January 2020 01:25:04
24a6254e-dea0-4acd-ac4f-ae70abd62cdc
19 February 2020 01:22:35
aff2af4a-fb0d-4371-9c90-24a42b3fe359
15 February 2020 04:54:03

Can't import the report into SQL 2014 SSRS instance. I did some reading and even changed the report definition to 2010 version but no luck.
I am getting the following alert:

The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: The report definition has an invalid target namespace
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition/' which cannot be upgraded. (rsInvalidReportDefinition) Get Online Help

Thanks.

Kapil

Hi,
I’m getting odd results
Deplyment Status shows 3 Non-compliant machines
https://imgur.com/2WJQJIo
but in the report all machines are grouped into compliant.
https://imgur.com/yXoA7V7

Sccm version is 2012

Just as a disclaimer this may be an edge case because it mostly effects an aggressive and automated use of this script.

If Software Center is actively downloading an application to install and this script runs then the Remove-CCMOrphanedCache function will try to delete the directories being used to download the content (Example Folders Being "1" and "1.BDRTEMP")

Script Version = 4.0
Command Run = .\Clean-CMClientCache.ps1 -CleanupActions All -LoggingOptions Host -Verbose

Hello Ioan,
My colleague has successfully imported this report and gotten everything to work. Thank you, this makes us look good!
Would it be possible for an updated report with the count of processor cores on the server? I tried to it is way beyond my SQL knowledge.

For Migration Projects or other use cases, the size of custom folders created by users or the user profiles size report (with actual values) could be very useful. As described before on twitter, the CI detection scripts are stored in this Technet Forum thread https://social.technet.microsoft.com/Forums/en-US/0c315e9b-fe44-477b-9964-818a4089fb8b/folder-size-report-for-spcific-device-collection?forum=ConfigMgrCBGeneral

In Clean-CMClientCache.ps1 commit 3f596a9 on Nov 30, 2018 there is this comment:
"I don't know if there can be multiple cache items with the same ContentID"

I do know: yes it can be. In our context it is for the same package that was changed and then re-run : this creates new cache items with new location (subdirectory in the cache) where the new version is downloaded.

1. this is probably not managed at all in the function Get-CCMCachedPackages. This code can return a list, so the script then crashes:
   "$PkgCacheInfo = $CacheInfo | Where-Object { $_.ContentID -eq $Package.PackageID }"

2. Managing this correctly is probably not feasible until you have a way to associate the program / package with the only true item cache that matches. (I do not see how yet)