SMS_Application WMI class's equivalent table in SCCM database

Question

I'm having an AppModel application in my System Center Configuration Manager(SCCM) console. Please see below screenshot of the properties window of the SCCM application:

I need to know the value of 'Allow this application to be installed from the Install Application task sequence.....' checkbox. It is highlighted in yellow.

I tried to get it through wbemtest tool using below details:

Namespace: root\sms\site_[siteCode] e.g. root\sms\site_lab
Query: select * from SMS_Application

I hope I've got the correct Windows Management Instrumentation(WMI) class for AppModel CM applications.

But the output in wbemtest tool doesn't give any field in the result output which can reflect the current condition of checkbox:

Instance of SMS_Application
{
    ApplicabilityCondition = "";
    CategoryInstance_UniqueIDs = {};
    CI_ID = 16777532;
    CI_UniqueID = "ScopeId_6AFF9AA6-E784-4BB8-8DCF-816FCF7A7C09/Application_8e417c4c-da5d-4f1c-91af-ed63d63f9a62/3";
    CIType_ID = 10;
    CIVersion = 3;
    CreatedBy = "NTL\\administrator";
    DateCreated = "20190724122559.000000+000";
    DateLastModified = "20200422051705.000000+000";
    EULAAccepted = 2;
    EULAExists = FALSE;
    ExecutionContext = 0;
    Featured = 0;
    HasContent = TRUE;
    IsBundle = FALSE;
    IsDeployable = TRUE;
    IsDeployed = FALSE;
    IsEnabled = TRUE;
    IsExpired = FALSE;
    IsHidden = FALSE;
    IsLatest = TRUE;
    IsQuarantined = FALSE;
    IsSuperseded = FALSE;
    IsSuperseding = FALSE;
    IsUserDefined = TRUE;
    IsVersionCompatible = TRUE;
    LastModifiedBy = "NTL\\estateadministrator";
    LocalizedCategoryInstanceNames = {};
    LocalizedDescription = "";
    LocalizedDisplayName = "7-Zip 19.00 (x64 edition)";
    LocalizedInformativeURL = "";
    LocalizedPropertyLocaleID = 65535;
    LogonRequirement = 0;
    Manufacturer = "";
    ModelID = 16777500;
    ModelName = "ScopeId_6AFF9AA6-E784-4BB8-8DCF-816FCF7A7C09/Application_8e417c4c-da5d-4f1c-91af-ed63d63f9a62";
    NumberOfDependentDTs = 0;
    NumberOfDependentTS = 0;
    NumberOfDeployments = 0;
    NumberOfDeploymentTypes = 1;
    NumberOfDevicesWithApp = 0;
    NumberOfDevicesWithFailure = 0;
    NumberOfSettings = 0;
    NumberOfUsersWithApp = 0;
    NumberOfUsersWithFailure = 0;
    NumberOfUsersWithRequest = 0;
    NumberOfVirtualEnvironments = 0;
    PermittedUses = 0;
    PlatformCategoryInstance_UniqueIDs = {};
    PlatformType = 1;
    SDMPackageVersion = 3;
    SecuredScopeNames = {"Default"};
    SedoObjectVersion = "9B99BA03-D0FA-417C-8BFF-6095B88AD179";
    SoftwareVersion = "";
    SourceCIVersion = 0;
    SourceModelName = "";
    SourceSite = "LAB";
    SummarizationTime = "20200422125059.533000+***";
};

Is it possible that WMI class is not exposing all the columns of the database backed by this WMI class. Hence, I'm looking for the SQL DB table of SCCM DB so that I can query it directly at DB level. Can anyone help me in this regard?

Update: I also need the table which I can update to set/reset the Boolean field corresponding to the checkbox shown on UI.

If you open that view in the console it queries fn_listApplicationCIs_List(1031), the problem is this includes a column with XML data that itself again contains some encoded "data" field so it might only be in there but I'm trying to look a little deeper tomorrow if this info can be found somehow — Syberdoor, Apr 22 '20 at 14:23
So it could be done via powershell sccm cmdlets, that much I found out so far. Is this an option for you? Still looking into whether it can be done with sql as well (wql is definitely impossible it is for sure part of some xml I have yet to find in the db, but sql can query inside xml properties so if I find the xml I might be able to devise a query) — Syberdoor, Apr 23 '20 at 07:05
Hey @Syberdoor Thanks for putting all the effort into my question. I'm looking for a database table and its corresponding column. If that column is an XML then we might have to parse it. — RBT, Apr 23 '20 at 07:28

Syberdoor · Answer 1 · 2020-04-24T07:44:44.430

The information is stored inside an XML element called SDMPackageXML in WMI. The corresponding table the console uses is fn_listApplicationCIs_List(1031) (it is also present in fn_listApplicationCIs(1031) I am at the moment not sure what the exact difference here is) where it is called SDMPackageDigest. The element is simply called "AutoInstall" and it is only present if set to true. To query it with WQL is impossible, however in a script it could be done by not using a query but a Get() on the SMS_Application Object (because SDMPackageXML is a lazy property) and then parsing the XML.

In SQL however it is possible to directly query the XML Part of a column like this:

SELECT 
    DisplayName, 
    CI_UniqueID 
FROM 
    fn_ListApplicationCIs_List(1031) app 
WHERE
    app.isLatest = 1 
AND 
    app.SDMPackageDigest.value('declare namespace p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest";(p1:AppMgmtDigest/p1:Application/p1:AutoInstall)[1]', 'nvarchar(max)') = 'true'

(the isLatest is necessary to discard older versions of the application). It is also possible to include the value in the select with that syntax, just keep in mind that it will be true for all Applications that are enabled for TS deployment and NULL for the others.

Now as for how to update. The SCCM DB should never be written to. I don't know if it would even be possible to achieve a change this way (it could be that WMI is always leading and would overwrite it), and as this is all undocumented it could be that changes have to be done at specific places or multiple places at the same time. If this has to be updated you have to resort to scripts, that can work with the lazy WMI properties. However I do not know of a way that is not dependent on SCCM specific dlls (doesn't mean there is none but I do not know how to work with XML well enough for this) so you will need Microsoft.ConfigurationManagement.ApplicationManagement.dll from the bin directory of a ConfigMgr Console Installation dir. (I wrote the code so that it would automatically find it on a computer where the Console is installed). Then you can do it like this (can of course also be wrapped in a for):

[System.Reflection.Assembly]::LoadFrom((Join-Path (Get-Item $env:SMS_ADMIN_UI_PATH).Parent.FullName "Microsoft.ConfigurationManagement.ApplicationManagement.dll"))
$app = gwmi -computer <siteserver> -namespace root\sms\site_<sitecode> -class sms_application -filter "LocalizedDisplayName='<appname>' and isLatest = 'true'"
$app.Get()
$sdmPackageXML = [Microsoft.ConfigurationManagement.ApplicationManagement.Serialization.SccmSerializer]::DeserializeFromString($app.SDMPackageXML, $true)
$sdmPackageXML.AutoInstall = $true
$app.SDMPackageXML = [Microsoft.ConfigurationManagement.ApplicationManagement.Serialization.SccmSerializer]::SerializeToString($sdmPackageXML, $true)
$app.Put()

The nice thing is that this deserialization always presents us with an AutoInstall property even if it is not present in the xml because it was written for sccm so we can just set it true/false as we want.

The reason we need the .dll is the serialization. Deserialisation can be done with a simple cast to [xml] as well but I am not sure how it could be serialized again. If this can be done there is no need for external dlls. (However the xml manipulation is less easy).

If you run this code on a machine with the sccm console anyway you could also skip the wmi part and use Get-CMApllication from the sccm ps commandlets (contains SDMPackageXML as well) however while the dll can be moved to any computer, the cmdlets are only installed with the console so I wrote the sample without them.

This path is good for getting the values. But, I also need to set the value programmatically through SQL query. I need the underline table which contains `SDMPackageDigest` column. — RBT, Apr 23 '20 at 11:23
The table is CI_ConfigurationItems however you should NEVER change anything directly in SQL. I wouldn't even know if it works because it could well be that it gets overwritten with whatever is on the WMI side (or it needs to be changed at different places. So if it has to be changed you should use the sccm cmdlets or edit the wmi object in powershell (non cmdlet example here: https://www.reddit.com/r/SCCM/comments/9sfbul/updating_application_sdmpackagexml_example/) — Syberdoor, Apr 23 '20 at 13:28
I've only two appModel application in my SCCM console but a select query on `CI_ConfigurationItems` table is returning around 200 records. Why is that? — RBT, Apr 24 '20 at 05:24
Because Microsoft did not use some new form of technology to implement their applications but internally based them on the ConfigurationItems technology which they then also use for many other things (e.g. also the deployment types or some updates, you can look at CI_TypeNames for a list of all things they do with that tech.) And also because they store multiple revisions for a lot of things. Since you updated the question I will try to find an example for updating the XML, however I never did that before so might take a little time. — Syberdoor, Apr 24 '20 at 05:46

SMS_Application WMI class's equivalent table in SCCM database

1 Answers1

Linked