SCCM/MECM Basic SQL Queries: -

 ******************Devices with OS details********************************

Select vr.Netbios_Name0,VGS.caption0 from V_GS_OPERTING_SYSTEM VGS

inner join v_r_system vr on vr.resourceid =vgs.resourceid

******* Device with LastHW, Last SW, Lastpolicyrequest and ClientActive Status***

select distinct vr. Netbios_Name0, vch. LastHw, vch.LastSW, vch.lastpolicyrequest,c=vch.clientactivestatus from v_ch_clienthealth vch

Inner join v_R_System vr on vr.resourceid=vch.machineid

**************ARP report********************************************

SELECT  

    VR.Name0 AS 'Computer Name',

    ar.DisplayName0 AS 'Application Name',

    ar.Version0 AS 'Version',

    ar.Publisher0 AS 'Publisher',

    ar.InstallDate0 AS 'Install Date'

FROM 

    v_R_System AS VR

INNER JOIN  v_Add_Remove_Programs AS ar  ON SYS.ResourceID = ar.ResourceID

*******************ARP report with Install source file details. ********

select VR.netbios_Name0, VR.Operating_System_Name_ando, VGS.ARPDisplayName0, vgs.ProductVersion0, vgs. InstallSource0, vgs.LocalPackage0 from v_R_System VR

Inner join v_GS_INSTALLED_SOFTWARE VGS on VGS.ResourceID=VR.ResourceID

Where vgs.ARPDisplayName0 like '%Enter the AppName%

**Application group Apps details**

select vapp.DisplayName, vapp.AdminComments, vapp.Manufacturer, vapp.SoftwareVersion, vapp.ObjectTypeID from v_applications vapp where vapp.ObjectTypeID='224'

*****Application group apps details***

select from v_AppGroupDisplayProperties -- It will show application group apps details

**Application group reference Apps details**

select fn.DisplayName, fn.Manufacturer, fn.SoftwareVersion, fn.NumberOfApplicationGroups, fn.NumberOfDeployments, fn. NumberOfDependedDTs, fn.NumberOfDevicesWithApp, fn.DateCreated, fn.DateLastModified, fn.LastModifiedBy, fn.CreatedBy from fn_ListLatestApplicationCIs(1033) fn 

where fn.NumberOfApplicationGroups >=1 

How to delete older user profile PowerShell script

#PowerShell Script to Remove Old User Profiles, #Deleting Outdated User Profiles with PowerShell, #How to Clean Up User Profiles Older Than One Year Using PowerShell, #PowerShell Method for Deleting Aged User Profiles, #Scripted Cleanup: Remove Older User Profiles with PowerShell

This PowerShell script scans Windows user profiles and removes any that are older than one year. It helps reclaim disk space, enhance system performance, and streamline workstation maintenance by clearing out stale or unused profiles. The script also generates a detailed report in C:\Temp\SystemName_Date_UserProfile.csv for review. Be sure to test it in your own environment before deployment—although it has been successfully tested and is working in ours.

# Define the date 1 year ago from today

$oneYearAgo = (Get-Date).AddMonths(-12)

#Create a log for user profile deletions

$FileSave="C:\temp\$(gc env:computername)_" + (get-date -format "MM-d-yy-HH-mm")+ "_userprofiledeletion.csv"

If(!(test-path -path $FileSave))

{

New-Item -ItemType file -Path $FileSave -Force

}

# Profiles to exclude

$profilesToExclude = @("KKG","specificProfileName")

# Get user profile directories under C:\Users

$UserFolders = Get-ChildItem -Path "C:\Users" -Force -Directory

# Initialize an array to store profile details

$profilesToExport = @()

# Iterate through each user profile directory

foreach ($Folder in $UserFolders) {

    # Skip profiles that are in the exclusion list

    if ($profilesToExclude -contains $Folder.Name) {

        continue

    }

    # Path to IconCache.db

    $iconCachePath = [System.IO.Path]::Combine($Folder.FullName, "AppData\Local\IconCache.db")

    Write-Host $iconCachePath

    # Initialize status

    #$status = "not eligible for deletion"

    # Get the LastWriteTime of the profile directory itself

    $profileLastWriteTime = $Folder.LastWriteTime

    # Calculate the size of the profile directory in GB

    [UInt64] $FolderSize = (Get-ChildItem -Path $Folder.FullName -Force -Recurse -ErrorAction SilentlyContinue | Measure-Object -Property Length -Sum).Sum

    $profileSizeGB = [math]::Round($FolderSize / 1GB, 4) # Rounds to 4 decimal places

   

    # Check if IconCache.db exists and get its last write time

    if (Test-Path -Path $iconCachePath) {

        $file = Get-Item -Path $iconCachePath -Force

        $iconCacheLastWriteTime = $file.LastWriteTime

        Write-Host $iconCacheLastWriteTime

        # Determine if IconCache.db is older than one year

        if ($iconCacheLastWriteTime -lt $oneYearAgo) {

            # Delete the profile directory

            try {

                Remove-Item -Path $Folder.FullName -Recurse -Force

                $status = "Profile deleted"

            } catch {

                $status = "failed to delete"

            } }

             else {

            $status = "Not eligible for deletion"

        }

    } else {

        $iconCacheLastWriteTime = "NA"

        

# Determine if User profile is older than two years

if ($profileLastWriteTime -lt $oneYearAgo) 

{

#Delete the user profile directory

try {

Remove-Item -Path $Folder.FullName -Recurse -Force

$status = "Deleting profile considering user profile timestamp >1 Years."

} catch {

$status= "Failed to delete profile." 

}

 }

 else {

$status = "Iconcache.db not found and user profile time < 1 Years. not eligible for deletion"

 }

  }

    

    # Create a custom object with profile details

    $profileDetails = [PSCustomObject]@{

        "User Profile" = $Folder.FullName

        "Last Write Time of User profile" = $profilelastwritetime

        "Last Write Time of IconCache file" = $iconCacheLastWriteTime

        "Profile Size (GB)" = $profileSizeGB

        "Status" = $status

    }

   

    # Add profile details to the array

    $profilesToExport += $profileDetails

}

# Export the profile details to a CSV file

#$csvFilePath = "C:\temp\profiles.csv"

$profilesToExport | Export-Csv -Path $Filesave -NoTypeInformation

Write-Output "Exported profile details with size and status to: $Filesave"

SCCM SQL query to find the application installed

SCCM SQL query to find the application installed

select r.Netbios_Name0,Operating_System_Name_and0,User_Name0,

, r.Full_Domain_Name0, vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0, vgs.InstallDate0 from v_r_system r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

where vgs.ProductName0 like '%Adobe%'

SQL Query for application installed with users:-

select r.netbios_name0, r.User_name0, vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0 from v_R_System r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

where vgs.ProductName0 like '%Office 365%' and r.User_name0 in ('Lav.kush',’abishek.c’)

SQL Query for application installed with Devices:-

select r.Netbios_Name0,Operating_System_Name_and0,User_Name0, r.Full_Domain_Name0,

vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0, vgs.InstallDate0 from v_r_system r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

where vgs.ProductName0 like '%Adobe%' and r.Netbios_Name0 in ('device1’, 'device2’, 'device2’)

SQL Query for application installed with Domain:-

select r.Netbios_Name0,Operating_System_Name_and0,User_Name0, r.Full_Domain_Name0,

vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0, vgs.InstallDate0 from v_r_system r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

where vgs.ProductName0 like '%Adobe%' and r.Full_Domain_Name0 like '%INDIA%'

SQL Query for all application installed in all system: -

select r.Netbios_Name0,Operating_System_Name_and0,User_Name0, r.Full_Domain_Name0,

vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0, vgs.InstallDate0 from v_r_system r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

SQL Query for  all application installed for all devices in a Collection id: -

Select DISTINCT SYS.Netbios_Name0,SYS.Resource_Domain_OR_Workgr0,SP.CompanyName, SP.ProductName, SP.ProductVersion

FROM v_GS_SoftwareProduct SP

JOIN v_R_System SYS ON SP.ResourceID=SYS.ResourceID

JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID

WHERE fcm.CollectionID = 'Collection id'

ORDER By SYS.Netbios_Name0, SP.CompanyName, SP.ProductName, SP.ProductVersion

SQL Query for  all application installed for all virtual machine:-

select r.Netbios_Name0,Operating_System_Name_and0,User_Name0, r.Full_Domain_Name0,

vgs.ProductName0, vgs.ProductVersion0, vgs.Publisher0, vgs.InstallDate0 from v_r_system r

INNER JOIN v_gs_Installed_Software vgs ON vgs.ResourceID = r.ResourceID

where r.Netbios_Name0 like '%VM%' and vgs.ProductName0 like '%adobe%'