Tenant attach: CMPivot (preview) sample scripts

Applies to: Configuration Manager (current branch)

Important

  • This information relates to a preview feature which may be substantially modified before it's commercially released. Microsoft makes no warranties, express or implied, with respect to the information provided here.

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)

Next steps

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