For one of those situations where you would like to get a list of SCCM Applications present inside a specific folder only, below is a way of retrieving the list of SCCM applications:

PART 1: FIND THE FOLDER ID

To find the folder ID for which you would like to retrieve the list of applications, execute the following query:

Select [ContainerNodeID]
,[Name]
,[ObjectType]
,[ParentContainerNodeID]
,[FolderGuid]
FROM [CM_XXX].[dbo].[vSMS_Folders]
WHERE Name like ‘XXX’

Ensure to replace CM_XXX with your CM database name.
Ensure to replace XXX with the folder name.

PART 2: RUN THE SCRIPT:

$FolderID = XXXXXXXX
$Sitecode = “XXX”
$CmServer = “XXXXXXXXXXX”
$Instancekeys = (Get-WmiObject -Namespace “ROOT\SMS\Site_$Sitecode” -ComputerName $CmServer -query “select InstanceKey from SMS_ObjectContainerItem where ObjectType=’6000′ and ContainerNodeID=’$FolderID'”).instanceKey
foreach ($key in $Instancekeys)
{
(Get-WmiObject -Namespace “ROOT\SMS\Site_$Sitecode” -ComputerName $CmServer -Query “select * from SMS_Applicationlatest where ModelName = ‘$key'”).LocalizedDisplayName
}