CM Display OS Marketing version in SQL reports
Have you ever noticed that by default, the devices (Windows 10, 11, Servers) don't report up their 'marketing name' in standard inventory? Like 22H2, or 21H2? Ever find that annoying? Sure, you can extend inventory to pull in the regkey of SOFTWARE\Microsoft\Windows NT\CurrentVersion\DisplayVersion, but... you don't have to. That information 'is' buried in the database, it's just not... super easy to find.
Here's a sample report to "Show ComputerName, OSBuild, MarketingName, Make, Model, SupportEndDate for the OS".
The gem, of course, is pulling out the MarketingName.
You could also do fun things like...
-only show records where MainstreamSupportEndDate < GetDate() (aka, stuff out of support)
-only show records where Manufacturer0 = 'Dell, Inc.'
etc etc etc...
Select
s1.netbios_name0 as 'ComputerName'
,s1.Build01 as 'OSBuild'
,Case when CHARINDEX('(',ldg.groupName) >0 then
rtrim(left(ldg.groupName, CHARINDEX('(',ldg.groupName) -1))
else ldg.groupName end as 'MarketingName'
,csys.Manufacturer0 as 'Make'
,csys.Model0 as 'Model'
,ldg.MainstreamSupportEndDate
,ldg.ExtendedSupportEndDate
from v_LifecycleDetectedGroups ldg
join v_LifecycleDetectedResourceIdsByGroupName lr on lr.GroupName=ldg.GroupName
join v_r_system s1 on s1.ResourceID=lr.ResourceID
left join v_GS_COMPUTER_SYSTEM csys on csys.ResourceID=s1.ResourceID
where ldg.Category in ('WinClient','WinServer')
OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
CM Icons for Applications, Packages, and Task Sequences
An important, but often neglected, feature of Software Center is to associate icons with the visible deployments in Software Center. You can add a visible icon to your Available things in Software Center: Applications, Packages(Advertisements), and Task Sequences (Advertisements... or OSD deployments).
A few 'things to know'
- Icons can be added to Applications, Packages (for Advertisements) and Task Sequences (for OSD or more complex deployments)
- "In general", icons in 512x512 format look the best. pictures or icons GREATER than 512x512 may not be able to be selected. That doesn't mean you can't use 256x256, or really, any .png as long as it is smaller than 512x512.
- "The file" known as "usethisPicture.png" doesn't get imported when you select it. What gets imported is a string which "represents" that image.
Where to 'get' icons to use.
- The already-installed application itself. For example, you can go to \\SomeWorkstation\c$\Program Files\TheApp\TheApp.exe, and there will usually be an icon that the vendor themselves decided represents that application.
- There are multiple 'icon repositories' out there, for example, iconarchive.com. Perhaps you are looking for "the perfect" AutoCAD icon. Search for AutoCad, and you will be presented with multiple (for free!) options. Select the one you like, download it. Now that you have the .png file, save it in a known location to use later.
- Image manipulation + online .ico converter. If there simply isn't ANYTHING you can find that you like at all, pre-created, you can screen shot something, edit it in a image editor, and/or web browse for something like "free icon converter", which will usually take an input file, and output a .png or .ico for you.
- "for fun" in %windir%\system32 is a file called 'moricons.dll' (yes, there is no e in that). It's a hold over from... wait for it... Windows 3.1! Select that file for some old skool nostagia.
How to associate icons with a..
Application
- Console, Software Library, Application Management, Applications
- Find the application, right-click and select 'Properties'
- Software Center tab, and near the bottom by "icon", Browse...
- Browse to where you identified the icon to use (program files on a workstation, a downloaded .png or .ico file...)
- Once selected, in the console you will see a representation of the icon, OK.
Package
- Console, Software Library, Application Management, Packages
- Find the Package, right-click and select 'Properties'
- Near the bottom by "icon", Browse...
- Browse to where you identified the icon to use (program files on a workstation, a downloaded .png or .ico file...)
- Once selected, in the console you will see a representation of the icon, OK.
Task Sequence
- Console, Software Library, Operating Systems, Task Sequences.
- Find the Task Sequence, right-click and select 'Properties'
- on the 'More Options' tab, near the bottom by "icon", Browse...
- Browse to where you identified the icon to use (program files on a workstation, a downloaded .png or .ico file...)
- Once selected, in the console you will see a representation of the icon, OK.
How to know what Apps, Packages, or Task sequences are missing an icon
So, that's all great that you can... but how do you know which ones are missing an icon, and/or what the icon looks like, without deploying 'everything' to yourself? Why, use a report of course!
Although it's not perfect (the sql doesn't filter by deployed AND visible in SC, just 'deployed', so if it is required and hidden from the user in SC, it will still show up on this report, even if you select @deployed=1), but if you want to see if something is 'missing an icon', if the Iconb column is blank... there is no icon defined.
--DECLARE @Deployed int = 1
IF @Deployed = 1
BEGIN
;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules', 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' as p1)
select
cci.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:Title)[1]', 'nvarchar(max)') AS [Name]
,cci.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Resources/p1:Icon/p1:Data)[1]', 'nvarchar(max)') AS [Iconb],
Type='Application'
,cci.CI_UniqueID as 'Identifier'
from vCI_ConfigurationItems cci
join v_CIAssignment cia on LEFT(cia.AssignmentName, CHARINDEX('_', cia.AssignmentName + '_') - 1)=cci.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:Title)[1]', 'nvarchar(max)')
where citype_id=10
and cci.IsLatest=1
UNION ALL
select SMSPackages.Name,
Cast('' AS XML).value('xs:base64Binary(sql:column("SMSPackages.icon"))','varchar(MAX)')
,case when SMSpackages.Packagetype=0 then 'Package'
when SMSPackages.Packagetype=4 then 'Task Sequence'
else cast(SMSPackages.PackageType as varchar) end as 'Type'
,SMSPackages.PkgID as 'Identifier'
from SMSPackages
join v_DeploymentSummary ds on ds.PackageID=SMSPackages.PkgID
where smspackages.packagetype in (0,4)
END
If @Deployed = 0
BEGIN
;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules', 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' as p1)
select
cci.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:Title)[1]', 'nvarchar(max)') AS [Name]
,cci.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Resources/p1:Icon/p1:Data)[1]', 'nvarchar(max)') AS [Iconb],
Type='Application'
,cci.CI_UniqueID as 'Identifier'
from vCI_ConfigurationItems cci
where citype_id=10
and cci.IsLatest=1
UNION ALL
select SMSPackages.Name,
Cast('' AS XML).value('xs:base64Binary(sql:column("SMSPackages.icon"))','varchar(MAX)')
,case when SMSpackages.Packagetype=0 then 'Package'
when SMSPackages.Packagetype=4 then 'Task Sequence'
else cast(SMSPackages.PackageType as varchar) end as 'Type'
,SMSPackages.PkgID as 'Identifier'
from SMSPackages
where smspackages.packagetype in (0,4)
END
Now, let's say you've created a report in Report Builder, and added all the columns... but that iconb column..where everything starts with iVBORw... means nothing to your human eye.
How to make that look like a icon in Report Builder: Add a column, and for that square in the table, right-click, Insert, Image, Select "Database" as the image source. Now, select 'Iconb' for 'Use this field', and for the MIME Type, select image/png.
CM Scheduled Maintenance Tasks Info
Because of this reddit post, Maintenance Task does not finish, I got inspired to look closer at maintenance tasks. There is already a view for seeing what your tasks are set to, and how long they have taken to run the last time they ran.
There is also a very helpful instructions here, How site maintenance tasks can make your life much easier, including some powershell, for asking any of the tasks to "run now", instead of waiting for a scheduled time.
I've included the powershell code for 'run now', just in case (as sometimes happens for me) a linked post disappears.
Why this might be interesting to know... if a task takes "too long", or, say, Backups run because a task triggers at midnight, backup triggers at 1am, and the midnight-task gets cancelled and never finishes, you might want to adjust some of the timings, or 'DeleteOlderThan', because your site might need adjusting.
select
SQLTaskStatus.TaskName,
SQLTaskStatus.BeginTime,
SQLTaskStatus.LatestBeginTime,
SQLTaskStatus.IsEnabled,
SQLTaskStatus.CompletionStatus,
SQLTaskStatus.LastStartTime,
SQLTaskStatus.LastCompletionTime,
DATEDIFF(SECOND, SQLTaskStatus.LastStartTime, SQLTaskStatus.LastCompletionTime ) as 'RunTime in seconds',
CASE WHEN (1&SQLTaskStatus.DaysOfWeek)=1 THEN 'SUNDAY' END as 'Sunday',
CASE WHEN (2&SQLTaskStatus.DaysOfWeek)=2 THEN 'MONDAY' END as 'Monday',
CASE WHEN (4&SQLTaskStatus.DaysOfWeek)=4 THEN 'TUESDAY' END as 'Tuesday',
CASE WHEN (8&SQLTaskStatus.DaysOfWeek)=8 THEN 'WEDNESDAY' END as 'Wednesday',
CASE WHEN (16&SQLTaskStatus.DaysOfWeek)=16 THEN 'THURSDAY' END as 'Thursday',
CASE WHEN (32&SQLTaskStatus.DaysOfWeek)=32 THEN 'FRIDAY' END as 'Friday',
CASE WHEN (64&SQLTaskStatus.DaysOfWeek)=64 THEN 'SATURDAY' END as 'Saturday',
SQLTaskStatus.RunNow,
SQLTaskStatus.SiteCode,
SQLTaskStatus.DeleteOlderThan
from vSMS_SQLTaskStatus AS SQLTaskSTatus
order by SQLTaskStatus.TaskName
OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
Powershell for triggering a task to 'run now'.
-replace SiteCode with your site code
-replace the name with the name of the task you want to run
and you do have to run it while connected to your Provider
$SiteCode = 'ABC'
$MT = Get-CMSiteMaintenanceTask -SiteCode $SiteCode -Name 'Delete Aged Scenario Health History'
$MethodParam = New-Object 'System.Collections.Generic.Dictionary[String,Object]'
$MethodParam.Add('SiteCode',$($SiteCode))
$MethodParam.Add('TaskName',$($MT.TaskName))
$ConfigMgrCon = Get-CMConnectionManager
$ConfigMgrCon.ExecuteMethod('SMS_SQLTaskStatus','RunTaskNow',$MethodParam)
CM Inventory per-user browser extensions
At my company, there was recently a need to verify a custom vendor browser extension, specifically for Edge. I found several methods for gathering Chrome extensions (including a clumsy attempt by myself several years ago), but then stumbled across this method, which could be modified to run as a Configuration Item + Mof Edit.
Source for this routine: https://github.com/vastlimits/uberAgent-Scripts/blob/main/Get-BrowserExtensionInfo/Get-BrowserExtensionInfo.ps1
It looks like that routine was last updated by Helge Klein; which I suspect is this Helge Klein (but I can't be sure): https://helgeklein.com/ Mr. Klein has done some other work which you may have heard of, like "DelProf2" (Delprof2 deletes inactive user profiles), or "SetACL" (SetACL manages permissions, auditing and ownership information.) Check him out if you have a need for something like that.
For Browser Extensions, if you want to use this with CM, the steps are...
1) deploy the CI inside a Baseline
2) Import the .mof and enable inventory.
That's the simple and short explanation. For the nitty-gritty details and background story. Browser Extensions are recorded in the user context.
What this routine does is multi-layered, and solves some (but not all) of the various issues I've felt "could" be encountered with inventorying per-user information.
First, a script inside the CI, running as system (not the logged in users) creates (if it doesn't already exist), a custom WMI Namespace called "CustomCMClasses". If you so choose, you can change that if you like. I've seen other examples using "ITLocal" as the custom namespace. But for purposes of this blog, we'll assume you won't be modifying that name. Then, it uses the well-known SIDs for "Everyone" and "Authenticated Users", to open up that namespace to allow those types of logins (aka, everyone and authenticated users) to write entries to classes in that namespace, like, for example... the per-user browser extensions.
Second, a script inside the CI runs under user context. It will first delete any records ALREADY in that class for that specific user, and then repopulate the class with anything found in the per-user browser extensions, for chrome, edge (chromium based), and firefox. What's nice about that is that if this is a multi-user device, you will continue to get information for all of the users who log in.
POTENTIAL drawback is that let's surmise that Bob Smith logged on in July, and entries were created for him in WMI then. Since then, he has not used this box or has even left the company. There might be stale entries for Bob being inventoried... potentially for the life of the device. That means that you will want to create reports where you filter on the 'ScriptRunTime' within the last xx days, so you don't pull stale data into reports.
--> Here <-- is the .zip containing the Configuration Item .cab to be imported into your CM Console (rename it before importing). If you successfully import the CAB file, you don't have to do anything with the .renameAsPS1 files in the Zip. Those .RenameAsPS1 files would be *IF* the .cab import fails, you could create your own CI, and add each of those as a Rule in the CI; one where you leave it to run as system, and the other where you carefully check the box for 'run scripts by using the logged on user credentials'. Also in the .zip is a BrowserExtension-ImportMe.mof file. Presuming you didn't change the custom class from being called 'CustomCMClasses', you would rename that to just .MOF, and import that into your Console, Administration, Client Settings, Default Client Settings, Hardware Inventory.
Once you have the CI and a baseline including the CI created, you deploy the Baseline potentially to a small collection of devices. On those few devices, interactively do policy refreshes, and run the Baseline (from the Control Panel applet). Note that you MIGHT have to run the baseline twice--the first time to create the initial custom class and set permissions. Once that is done, it'll skip over that next time. Then run the baseline again. Using your favorite WMI browser (wmiexplorer?) look at customcmclasses, and the class inside. See if it contains what you expect it to contain. If so, hooray!
If you are happy with the results, import the .mof (you'll get a view likely called v_gs_browserextensions0... usually). Enable inventory for that. Deploy the baseline to the rest of your environment where you want to get browser extensions. Note, I would NOT have the baseline run frequently. Perhaps every 4 days? or every 7 days? This information isn't mission critical, imo; it's a nice-to-have; for those (hopefully few) times when manager-types want to know about browser extensions.
Sample report to get you started (once you have deployed the CI as a Baseline, tested it, and inventory is enabled). Note that the InstallTime returned by the script is in that "seconds since 1970", so it's a bit annoying to tease out. And 'default' extensions for chrome/Edge just have an installtime of -11644473600000, which doesn't translate to a 'real time', so in the report I just Null that out.
select
Case when be.ExtensionInstallTime0 = '-11644473600000' then
NULL
else
dateadd(ms, cast(be.ExtensionInstallTime0 as BIGINT)%(3600*24*1000), dateadd(day, cast(be.ExtensionInstallTime0 as BIGINT)/(3600*24*1000), '1970-01-01 00:00:00.0') )
end as 'InstallTime'
,
be.Browser0 as 'Browser',
be.ExtensionFromWebStore0 as 'ExtensionInstalledFromWebStore',
be.ExtensionID0 as 'ExtensionID',
be.ExtensionInstalledByDefault0 as 'ExtensionInstalledByDefault',
be.ExtensionName0 as 'ExtensionName',
be.ExtensionVersion0 as 'ExtensionVersion',
case when be.ExtensionState0 = 1 then 'Active' else 'Disabled' end as 'ExtensionState',
be.OSUser0 as 'User',
be.ProfileDir0 as 'ProfileDir',
be.ProfileGaiaName0 as 'ProfileGaiaName',
be.ProfileName0 as 'Browser ProfileName (if Browsers have multiple profiles)',
be.ProfileUserName0 as 'Browser Profile UserName',
be.ScriptLastRan0 as 'LastTime This information was updated locally'
from v_GS_BrowserExtensions0 be
===============
Example results (from my mini-lab, for example)
InstallTime | Browser | ExtensionInstalledFromWebStore | ExtensionID | ExtensionInstalledByDefault | ExtensionName | ExtensionVersion | ExtensionState | User | ProfileDir | Browser ProfileName (if Browsers have multiple profiles) | LastTime This information was updated locally |
NULL | Edge | FALSE | jmjflgjpcpepeafmmgdpfkogkghcpiha | TRUE | Edge relevant text changes | 1.1.3 | Active | smsadmin | Default | Profile 1 | 7/6/2023 9:56 |
NULL | Chrome | TRUE | aapbdbdomjkkjkaonfhkkikfgjllcleb | FALSE | Google Translate | 2.0.13 | Active | sherry | Default | Person 1 | 7/6/2023 8:19 |
NULL | Chrome | TRUE | nmmhkkegccagdldgiimedpiccmgmieda | TRUE | Chrome Web Store Payments | 1.0.0.6 | Active | sherry | Default | Person 1 | 7/6/2023 8:19 |
NULL | Chrome | TRUE | ghbmnnjooekpmoecnnnilnnbdlolhkhi | TRUE | Google Docs Offline | 1.63.3 | Active | sherry | Default | Person 1 | 7/6/2023 8:19 |
NULL | Edge | FALSE | jmjflgjpcpepeafmmgdpfkogkghcpiha | TRUE | Edge relevant text changes | 1.1.3 | Active | sherry | Default | Profile 1 | 7/6/2023 8:19 |
7/3/2023 13:41 | Firefox | TRUE | customscrollbars@computerwhiz | NULL | Custom Scrollbars | 4.2.2 | Active | sherry | 9fpff8kr.default-release | default-release | 7/6/2023 8:19 |