sqlsccm

query SCCM apps


Is there any table in the SCCM database that returns all applications located in a subfolder ? This table returns all application and packages: "v_Package" but i need to filter only applications and only the ones in a specific subfolder. i also found the table "v_Applications" in my SQL server that returns only the applications but it is not present in the microsoft docs: https://learn.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/application-management-views-configuration-manager is that normal ? Could someone point me in the right direction ?

Thank you very much


Solution

  • The best "trick" if you want to do something that is possible in the SCCM Console via SQL is to do it and then watch SMSProv.log on your siteserver which will tell you what SQL and WQL command was used to produce your result. That is always a good point towards the direction you should take.

    In this case you will see that the query takes the applications info from a function call fn_ListApplicationCIs_List(1031) which features a Column ObjectPath that is your folder so a quick

    select DisplayName, ObjectPath from fn_ListApplicationCIs_List(1031) 
    

    should probably give you what you want.

    The real info if you wanna go deeper is as far as I understand it in a view called vFolderMembers (which weirdly only contains those that are not in root) but going from there you would have to join some internal IDs to get to a readable name.

    To also answer your other question: Yes v_Applications is a normal table but it does not contain the object Path (which is only relevant within the context of the console and not deployment) and yes it is (sadly) normal that Microsofts documentation is not always the best and never really complete in regards to SCCM matters.