Tenant attach: CMPivot sample scripts

Applies to: Configuration Manager (current branch)

Run CMPivot queries from Microsoft Endpoint Manager admin center. Below are a few common query needs and how CMPivot can be used to meet them. CMPivot uses a subset of the Kusto Query Language (KQL).

Below are a few common query needs and how CMPivot can be used to meet them. CMPivot uses a subset of the Kusto Query Language (KQL).

Operating system

Gets operating system information.

// Sample query for OS information
OperatingSystem

Recently used applications

The following query gets recently used applications (last 2 hours):

CCMRecentlyUsedApplications
| where (LastUsedTime > ago(2h))
| project CompanyName, ProductName, ProductVersion, LastUsedTime

Device start times

The following query shows when devices have started in the last seven days:

OperatingSystem
| where LastBootUpTime <= ago(7d)
| summarize count() by bin(LastBootUpTime,1d)

Free disk space

The following query shows free disk space:

LogicalDisk
| project Device, DeviceID, Name, Description, FileSystem, Size, FreeSpace
| order by DeviceID asc

Device information

Show device, manufacturer, model, and OSVersion:

ComputerSystem
| project Device, Manufacturer, Model
| join (OperatingSystem | project Device, OSVersion=Caption)

Boot times for a device

Show boot times for devices:

SystemBootData
| project Device, SystemStartTime, BootDuration, OSStart=EventLogStart, GPDuration, UpdateDuration
| order by SystemStartTime desc

Authentication failures

Search the event logs for authentication failures.

EventLog('Security')
| where  EventID == 4673

ProcessModule(<processname>)

Enumerates all the modules (dlls) loaded by a given process. ProcessModule is useful when hunting for malware that hides in legitimate processes.

ProcessModule('powershell')
| summarize count() by ModuleName
| order by count_ desc

Antimalware software status

Gets the status of antimalware software installed on the computer gathered by the Get-MpComputerStatus cmdlet. The entity is supported on Windows 10 and Server 2016, or later with defender running. |

EPStatus
| project Device, QuickScanAge=datetime_diff('day',now(),QuickScanEndTime)
| summarize DeviceCount=count() by QuickScanAge

Find BIOS Manufacturer that contains any word like Micro

Bios
// Find BIOS Manufacturer that contains any word like Micro, such as Microsoft
| where Manufacturer like '%Micro%'

Find file by its hash

Search for a file by hash.

Device
| join kind=leftouter ( File('%windir%\\system32\\*.exe')
| where SHA256Hash == 'A92056D772260B39A876D01552496B2F8B4610A0B1E084952FE1176784E2CE77')
| project Device, MalwareFound = iif( isnull(FileName), 'No', 'Yes')

Find 'Scripts' in the CCM logs in the last hour

The following query will look at events in the last 1 hour:

CcmLog('Scripts',1h)

Find information in the registry

Search for registry information.

// Change the path to match your desired registry hive query
// The RegistryKey entity (added in version 2107) isn't supported with CMPivot for tenant attached devices.  

Registry('hklm:\SOFTWARE\Microsoft\EnterpriseCertificates\Root\Certificates\*')
RegistryKey('hklm:\SOFTWARE\Microsoft\EnterpriseCertificates\Root\Certificates\*')

RegistryKey('hklm:\SOFTWARE\Microsoft\SMS\*')
Registry('hklm:\SOFTWARE\Microsoft\SMS\*')

Next steps

For more information, see Launch CMPivot from the admin center For more information on entities for your queries, see Microsoft Endpoint Manager tenant attach: CMPivot usage overview.