Let’s have a quick walkthrough of ConfigMgr CMPivot Logs and Background processes. Microsoft introduced CMPivot with the ConfigMgr 1806 version.
You can launch the CMPivot from two places. First, it can be used as an in-console tool. Second, it can be used as a standalone CMPivot tool.
Follow the post, SCCM CMPivot Architecture Fast Channel Notification | ConfigMgr, to learn the details of the architecture and how CMPivot works.
However, in this post, I will concentrate more on CMPivot log file entries and backend processes.
Table of Contents
CMPivot Queries
I have many blog posts related to CMPivot queries, some of which are listed below. These posts explain how to start running CMPivot queries against ONLINE clients.
- Out of Support Office 365 ProPlus
- Patches Installed in Last 90 Days
- Windows 10 English Language Devices
- Find Devices Missing Patches
- Devices with Greater than 15 GB Free Disk Space
NOTE! – You want to find out who Initiated #CMPivot Query | ConfigMgr Audit Reports. You can use the following https://www.anoopcnair.com/sccm-audit-reports-who-initiated-cmpivot-query-configmgr/
Some other of the sample CMPivot Quires
Disk | summarize dcount( Device ) by Name OS | summarize countif( (Version == '10.0.17134') ) by Device | where (countif_ > 0) OS | summarize countif( (Version == '10.0.17134') ) by Device | where (countif_ == 0) | project Device Service | summarize dcount( Device ) by Name Service | where (Name == 'Browser') | summarize count() by Device Bios | summarize countif( (Version == 'LENOVO - 1140') ) by Device | where (countif_ > 0) Disk | where (Description == 'Local Fixed Disk') | where isnotnull( FreeSpace ) | order by FreeSpace asc
- Analyze SCCM Client Logs Using CMPivot | ConfigMgr
- SCCM CMPivot Browser Related Queries Default List of Browsers
- SCCM CMPivot Architecture and Sample Queries
- CMPivot Query for SCCM BitLocker Management Event Logs
ConfigMgr CMPivot Logs
Let’s quickly look at the ConfigMgr CMPivot Logs (server-side and client-side logs) associated with CMPivot. You can also refer to the list of all the client-side and server-side Configuration Manager logs to get more details of each log.
| Server-side CMPivot Logs: C:\Program Files\Microsoft Configuration Manager\Logs | Client-side CMPivot Logs: C:\Windows\CCM\Logs |
|---|---|
| SmsProv.log | CcmNotificationAgent.log |
| BgbServer.log | Scripts.log |
| StateSys.log | StateMessage.log |
SMSProv.log
The SMSProv.log is the first log initiated when you run a CMPivot query. Let’s quickly look at the process and essential search keywords to help you troubleshoot CMPivot.
ConfigMgr CMPivot Log file location – C:\Program Files\Microsoft Configuration Manager\Logs
- CExtProviderClassObject::DoExecuteMethod InitiateClientOperationEx
- Auditing: User MEMCM\anoop initiated client operation 145 to collect SMS00001.
- Context: ApplicationName=CMPivot.exe
- Context: ApplicationVersion=5.2010.1093.1900
- WMI Query to find out ONLINE clients
ExecQueryAsync: START select count(*) from SMS_CM_RES_COLL_SMS00001 where (ClientType is not null and ClientType=1 and CNIsOnline is not null and CNIsOnline =0) OR (ClientType is not null and ClientType=1 and IsClient=1 and CNIsOnline is null)
- SQL query to find Online clients
Execute SQL =select count ( * ) from _RES_COLL_SMS00001 AS SMS_CM_RES_COLL_SMS00001 where ((((SMS_CM_RES_COLL_SMS00001.ClientType is not null AND SMS_CM_RES_COLL_SMS00001.ClientType = 1) AND SMS_CM_RES_COLL_SMS00001.CNIsOnline is not null) AND SMS_CM_RES_COLL_SMS00001.CNIsOnline = 0) OR (((SMS_CM_RES_COLL_SMS00001.ClientType is not null AND SMS_CM_RES_COLL_SMS00001.ClientType = 1) AND SMS_CM_RES_COLL_SMS00001.IsClient = 1) AND SMS_CM_RES_COLL_SMS00001.CNIsOnline is null ))
- CExtProviderClassObject::DoCreateInstanceEnumAsync (SMS_CMPivotTask)
- SQL and WMI queries to details of CMPivot Tasks
Execute WQL =Select TaskID, ScriptGuid, ScriptName, ClientOperationId, CollectionId, LastUpdateTime, OverallScriptExecutionState, TotalClients, OfflineClients, CompletedClients, ReturnCodeCount from SMS_CMPivotTask where ClientOperationId=16782812 Execute SQL =select all SMS_CMPivotTask.TaskID,SMS_CMPivotTask.ScriptGuid,SMS_CMPivotTask.ScriptName,SMS_CMPivotTask.ClientOperationId,SMS_CMPivotTask.CollectionId,SMS_CMPivotTask.LastUpdateTime,SMS_CMPivotTask.OverallScriptExecutionState,SMS_CMPivotTask.TotalClients,SMS_CMPivotTask.OfflineClients,SMS_CMPivotTask.CompletedClients,SMS_CMPivotTask.ReturnCodeCount from vSMS_CMPivotTask AS SMS_CMPivotTask where SMS_CMPivotTask.ClientOperationId = 16782812 ExecQueryAsync: COMPLETE Select TaskID, ScriptGuid, ScriptName, ClientOperationId, CollectionId, LastUpdateTime, OverallScriptExecutionState, TotalClients, OfflineClients, CompletedClients, ReturnCodeCount from SMS_CMPivotTask where ClientOperationId=16782812
CExtProviderClassObject::DoExecuteMethod CMPivotQuery
BgpServer.log
The BGP Server log (BgpServer.log) file helps to understand the Notification channel-related activities from the server side. The notification framework is the key component CMPivot uses to deliver real-time details of SCCM clients.
ConfigMgr CMPivot Logs file location – C:\Program Files\Microsoft Configuration Manager\Logs
- Get one push message from database.
- Starting to send push task (PushID: 88 TaskID: 88 TaskGUID: 05BE74E2-1FE8-460E-9D32-314B740BC3F9 TaskType: 15 TaskParam:……
- Finished sending push task (PushID: 88 TaskID: 88) to 2 clients
- Total online clients: 2 (TCP: 2 HTTP: 0)
- Generated BGB task status report F:\Program Files\Microsoft Configuration Manager\inboxes\bgb.box\Bgby3i0o.BTS at 03/05/2021 04:33:31. (PushID: 88 ReportedClients: 2 FailedClients: 0)
StateSys.log
I have not seen any activity on the SMS_State_System component apart from a few lines of summarization, so I’m not sure how relevant this log file is for now.
ConfigMgr CMPivot Logs file location- C:\Program Files\Microsoft Configuration Manager\Logs
- Started task ‘Save State Message Statistics’
- Task ‘Save State Message Statistics‘ completed successfully after running for 15 seconds, with status 1.
CCMNotificationAgent.log
Let’s analyze the Windows 10 client (ConfigMgr client) side log file called CCM Notification Agent log. This is the client-side component (BGB agent) of the BGB server component.
ConfigMgr CMPivot Logs File Location – C:\Windows\CCM\Logs
Updating MDM_ConfigSetting.ClientHealthLastSyncTime with value 2021-03-05T05:50:09Z
- Receive task from server with pushid=88, taskid=88, taskguid=D65A895A-A9BC-42B7-B20E-F2E5AAAF6EF1, tasktype=15 and taskParam=PF……
Script.log
Script.log is the log where the CMPivot query execution at client-side related details are stored. Does this mean CMPivot executes the instructions using PowerShell at the client end? Well, I will let you find out from the log snippets below.
ConfigMgr CMPivot Logs file location – C:\Windows\CCM\Logs
- Parsing the Script Execution Message …
- Script Guid: 7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14
- Run the PowerShell Script: [7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14]
- PowerShell path: C:\WINDOWS\system32\WindowsPowerShell\v1.0\PowerShell.exe
- Launching the PowerShell.exe for Script [7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14]
- Creating the task object…
- Executing the task…
- Started script handler job.
- The execution task is kicked off.
- Running PS script…
- Creating state message…
- Sending script state message (fast): {D65A895A-A9BC-42B7-B20E-F2E5AAAF6EF1}
- Result are sent for ScriptGuid: 7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14 and TaskID: {D65A895A-A9BC-42B7-B20E-F2E5AAAF6EF1}
StateMessage.log
The State Message log on the client side contains details of state messages sent to the server. I have not noticed any activity in this log file for the CMPivot query.
NOTE! – I have not tested this by enabling verbose logging for the ConfigMgr client.
Resources
- CMPivot for real-time data in Configuration Manager – https://docs.microsoft.com/en-us/sccm/core/servers/manage/cmpivot
- SCCM CMPivot Architecture Fast Channel Notification | ConfigMgr
- How to use the standalone CMPivot tool
PowerShell Script Sample
The script is copied from the C:\Windows\CCM\ScriptStore folder, where the CMPivot-related scripts are stored. DO NOT use this script anywhere. This is just for educational purposes.
param([string] $kustoquery, [string] $wmiquery, [string] $select)
# Read the queries and selects
$kustoquery = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($kustoquery.Substring(2))).Split([Environment]::NewLine, [StringSplitOptions]::RemoveEmptyEntries)
$wmiqueries = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($wmiquery.Substring(2))).Split([Environment]::NewLine, [StringSplitOptions]::RemoveEmptyEntries)
$selects = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($select.Substring(2))).Split([Environment]::NewLine, [StringSplitOptions]::RemoveEmptyEntries)
#create the result xml writer
$sb = New-Object System.Text.StringBuilder
$sw = New-Object System.IO.StringWriter($sb)
$writer = New-Object System.Xml.XmlTextWriter($sw)
$writer.WriteStartDocument()
$writer.WriteStartElement("result")
$writer.WriteAttributeString("ResultCode", 0x00000000 )
# A helper function to create a datatable of properties
function CreateTableFromPropertyList
{
param ([string[]]$properties, [String[]]$propertyTypes)
$dt = New-Object system.Data.DataTable
# Add Device column first
$col_device = New-Object system.Data.DataColumn 'Device',([Microsoft.ConfigurationManagement.AdminConsole.CMPivotParser.Device])
$dt.Columns.Add($col_device)
# Add the rest properties to columns
for( $index = 0; $index -lt $properties.Length; $index++ )
{
# Get the column datatype
switch($propertyTypes[$index])
{
"Boolean"
{
$colType = [System.Boolean]
break
}
"Number"
{
$colType = [System.Int64]
break
}
"String"
{
$colType = [System.String]
break
}
"TimeSpan"
{
$colType = [System.TimeSpan]
break
}
"DateTime"
{
$colType = [System.DateTime]
break
}
default
{
throw
}
}
$column = New-Object system.Data.DataColumn $properties[$index], ($colType)
$dt.Columns.Add($column)
}
return ,$dt
}
Try
{
# Lookup the CCM directory
$key = [Microsoft.Win32.RegistryKey]::OpenBaseKey([Microsoft.Win32.RegistryHive]::LocalMachine, [Microsoft.Win32.RegistryView]::Registry64)
$subKey = $key.OpenSubKey("SOFTWARE\Microsoft\SMS\Client\Configuration\Client Properties")
$ccmdir = $subKey.GetValue("Local SMS Path")
$key.Close()
$binName = 'AdminUI.CMPivotParser.dll'
$binPath = (join-path $ccmdir $binName)
# Try to load AdminUI.CMPivotParser.dll from ccm binary folder
try
{
[System.Reflection.Assembly]::LoadFile($binPath) | Out-Null
}
# If there is any exception, fall back to load dll from memory
catch
{
# Write the file to the system temp dir
$binPath = (Join-Path $ccmdir 'SystemTemp')
If(!(Test-Path $binPath))
{
Throw 'Missing SystemTemp directory'
}
$binPath = (join-path $binPath $binName)
if(!(Test-Path $binPath))
{
$bin64String = <GUID>
$bytes = [system.convert]::FromBase64String($bin64String)
$bytes | Set-Content $binPath -Encoding Byte
}
$cert = (Get-AuthenticodeSignature $binPath).SignerCertificate
if( !$cert.Verify() )
{
Throw 'Invalid CMPivotParser'
}
# Load the CMPivotParser.dll
[System.Reflection.Assembly]::LoadFile($binPath) | Out-Null
}
# Create the resultant data table list
$datatables = New-Object System.Collections.Generic.List[Data.DataTable]
# For each query
for( $queryIndex = 0; $queryIndex -lt $wmiqueries.Length; $queryIndex++ )
{
# For this index
$wmiquery = $wmiqueries[$queryIndex]
$select = $selects[$queryIndex]
# Parse the select parameter
$propertyFilter = @()
$propertyTypes = @()
$propertySerializer = @()
foreach($p in $select.Split(','))
{
# Parse property definition
$p = $p.Split(':')
# Generate a property serializer
if( $p[2] -eq "KiloBytes" )
{
$propertyFilter+= $p[0]
$propertyTypes+= $p[1]
$propertySerializer += { Param( [Object] $val ) return [Int64]::Parse($val.ToString()) -shr 10 }
}
elseif( $p[2] -eq "MegaBytes" )
{
$propertyFilter+= $p[0]
$propertyTypes+= $p[1]
$propertySerializer += { Param( [Object] $val ) return [Int64]::Parse($val.ToString()) -shr 20 }
}
elseif( $p[2] -eq "GigaBytes" )
{
$propertyFilter+= $p[0]
$propertyTypes+= $p[1]
$propertySerializer += { Param( [Object] $val ) return [Int64]::Parse($val.ToString()) -shr 30 }
}
elseif( $p[2] -eq "Seconds" )
{
$propertyFilter+= $p[0]
$propertyTypes+= $p[1]
$propertySerializer += { Param( [Object] $val ) return [Int64]::Parse($val.ToString())/1000 }
}
elseif( $p[2] -eq "HexSring" )
{
$propertyFilter+= $p[0]
$propertyTypes+= $p[1]
$propertySerializer += { Param( [Object] $val ) return "0x"+[Int64]::Parse($val.ToString()).ToString("X") }
}
elseif( $p[2] -eq "DateString" )
{
# The DateString field format is "ddddddddHHMMSS.mmmmmm:000" --> %d Days 02:01:01 Hours
$propertyFilter+= $p[0]
$propertyTypes+= $p[1]
$propertySerializer += {
Param( [Object] $val )
$days = [Int64]::Parse( $val.SubString(0, 8))
$hours = $val.SubString(8, 2)
$min = $val.SubString(10, 2)
$seconds = $val.SubString(12, 2)
return "${days} Days ${hours}:${min}:${seconds} Hours"
}
}
elseif ( $p[1] -eq 'Number' )
{
$propertyFilter+= $p[0]
$propertyTypes+= $p[1]
$propertySerializer += { Param( [Object] $val ) return [Int64]::Parse($val.ToString()) }
}
elseif ( $p[1] -eq 'Boolean' )
{
$propertyFilter+= $p[0]
$propertyTypes+= $p[1]
$propertySerializer += { Param( [Object] $val ) return [Boolean]::Parse($val.ToString()) }
}
elseif( $p[1] -eq 'DateTime' )
{
$propertyFilter+= $p[0]
$propertyTypes+= $p[1]
$propertySerializer += {
Param( [Object] $val )
try
{
$val = [System.Management.ManagementDateTimeconverter]::ToDateTime($val)
#Sql.MinDateTime -> Null
if( $val -lt (get-date -Year 1753 -Month 1 -Day 1 -Hour 0 -Minute 0 -Second 0 -Millisecond 0))
{
return $null
}
else
{
return $val
}
}
catch
{
return $null
}
}
}
elseif( $p[1] -ne 'Device' )
{
$propertyFilter+= $p[0]
$propertyTypes+= $p[1]
$propertySerializer += { Param( [Object] $val ) return $val.ToString() }
}
}
# Create a data table to store the results of this query
$dt = CreateTableFromPropertyList -properties $propertyFilter -propertyTypes $propertyTypes
#Create the result set
$results = New-Object System.Collections.Generic.List[Object]
#deal with one-offs that don't work well over WMI
if( $wmiquery -eq 'SMBConfig' )
{
# Get Smb Config
$smbConfig = Get-SmbServerConfiguration -ErrorAction Stop| Select-object -Property $propertyFilter
#Add to results list
$results.Add($smbConfig)
}
elseif( $wmiquery -eq 'Users' )
{
$users = New-Object System.Collections.Generic.List[String]
foreach( $user in (get-WmiObject -class Win32_LoggedOnuser -ErrorAction Stop | Select Antecedent))
{
$parts = $user.Antecedent.Split("""")
# If this is not a built-in account
if(( $parts[1] -ne "Window Manager" ) -and (($parts[1] -ne $env:COMPUTERNAME) -or (($parts[3] -notlike "UMFD-*")) -and ($parts[3] -notlike "DWM-*")))
{
# add to list
$users.Add($parts[1] + "\" + $parts[3])
}
}
# Create unique set of users
$users | sort-object -Unique | foreach-object { $results.Add(@{ UserName = $_ }) }
}
elseif( $wmiquery -eq 'IPConfig' )
{
$ipconfigs = (Get-NetIPConfiguration -ErrorAction Stop)
foreach( $ipconfig in $ipconfigs )
{
$hash = @{
InterfaceAlias = $ipconfig.InterfaceAlias
Name = $ipconfig.NetProfile.Name
InterfaceDescription = $ipconfig.InterfaceDescription
Status = $ipconfig.NetAdapter.Status
IPV4Address = $ipconfig.IPv4Address.IPAddress
IPV6Address = $ipconfig.IPv6Address.IPAddress
IPV4DefaultGateway = $ipconfig.IPv4DefaultGateway.NextHop
IPV6DefaultGateway = $ipconfig.IPv6DefaultGateway.NextHop
DNSServerList = ($ipconfig.DNSServer.ServerAddresses -join "; ")
}
$results.add($hash)
}
}
elseif( $wmiquery -eq 'Connections' )
{
$netstat = "$Env:Windir\system32\netstat.exe"
$rawoutput = & $netstat -f
$netstatdata = $rawoutput[3..$rawoutput.count] | ConvertFrom-String | select p2,p3,p4,p5 | where p5 -eq 'established' | select P4
foreach( $data in $netstatdata)
{
#Add to results list
$hash = @{ Server = $data.P4.Substring(0,$data.P4.LastIndexOf(":")) }
$results.Add($hash )
}
}
elseif( $wmiquery -eq 'EPStatus' )
{
$epStatus = (Get-MpComputerStatus -ErrorAction Stop)
$hash = @{
AMServiceEnabled = $epStatus.AMServiceEnabled
AntispywareEnabled = $epStatus.AntispywareEnabled
AntispywareSignatureLastUpdated = $epStatus.AntispywareSignatureLastUpdated
AntispywareSignatureVersion = $epStatus.AntispywareSignatureVersion
AntivirusEnabled = $epStatus.AntivirusEnabled
AntivirusSignatureLastUpdated = $epStatus.AntivirusSignatureLastUpdated
AntivirusSignatureVersion = $epStatus.AntivirusSignatureVersion
BehaviorMonitorEnabled = $epStatus.BehaviorMonitorEnabled
IoavProtectionEnabled = $epStatus.IoavProtectionEnabled
IsTamperProtected = $epStatus.IsTamperProtected
NISEnabled = $epStatus.NISEnabled
NISSignatureLastUpdated = $epStatus.NISSignatureLastUpdated
NISSignatureVersion = $epStatus.NISSignatureVersion
OnAccessProtectionEnabled = $epStatus.OnAccessProtectionEnabled
QuickScanEndTime = $epStatus.QuickScanEndTime
RealTimeProtectionEnabled = $epStatus.RealTimeProtectionEnabled
}
$results.Add($hash)
}
elseif( $wmiquery.StartsWith('Updates') )
{
# Default server selection
$serverSelection = 0
# if server selection has been specified then use it
$first = $wmiquery.IndexOf("(")
if( $first -ne -1 )
{
$last = $wmiquery.LastIndexOf(")")
$serverSelection = [Int32]::Parse( $wmiquery.Substring($first+1, $last-$first-1))
}
# Create an update session object
$Session = [activator]::CreateInstance([type]::GetTypeFromProgID("Microsoft.Update.Session",$null))
$Searcher = $Session.CreateUpdateSearcher()
$Searcher.ServerSelection = $serverSelection
# Search for any uninstalled updates
$MissingUpdates = $Searcher.Search("DeploymentAction=* and IsInstalled=0 and Type='Software'")
if ($MissingUpdates.Updates.Count -gt 0)
{
foreach( $Update in $MissingUpdates.Updates )
{
$KBArticleIDs = ""
foreach( $KB in $Update.KBArticleIDs)
{
if( $KBAticleIDs.Length -gt 0 )
{
$KBArticleIDs = $KBArticleIDs + ","
}
$KBArticleIDs = $KBArticleIDs + "KB$KB"
}
$SecurityBulletinIDs = ""
foreach( $BulletinID in $Update.SecurityBulletinIDs)
{
if( $SecurityBulletinIDs.Length -gt 0 )
{
$SecurityBulletinIDs = $SecurityBulletinIDs + ","
}
$SecurityBulletinIDs = $SecurityBulletinIDs + $BulletinID
}
$Categories = ""
foreach( $Category in $Update.Categories)
{
if( $Categories.Length -gt 0 )
{
$Categories = $Categories + ","
}
$Categories = $Categories + $Category.Name
}
#Add to results list
$hash = @{
Title = $Update.Title
RebootRequired = $Update.RebootRequired
LastDeploymentChangeTime = $Update.LastDeploymentChangeTime
UpdateID = $Update.Identity.UpdateID
KBArticleIDs = $KBArticleIDs
SecurityBulletinIDs = $SecurityBulletinIDs
Categories = $Categories
}
$results.Add($hash)
}
}
}
elseif( $wmiquery -eq 'AppCrash' )
{
Try
{
$crashes = get-eventlog -ErrorAction Stop -LogName Application -After (Get-Date).AddDays(-7) -InstanceId 1000 -Source 'Application Error'
foreach ($crash in $crashes)
{
$hash = @{
FileName = $crash.ReplacementStrings[0]
Version = $crash.ReplacementStrings[1]
ReportId = $crash.ReplacementStrings[12]
DateTime = $crash.TimeGenerated
}
$results.Add($hash)
}
}
Catch
{
}
}
elseif( $wmiquery -eq 'AadStatus' )
{
$dsregcmd = "$Env:Windir\system32\dsregcmd.exe"
$rawoutput = & $dsregcmd /status
$hash = @{}
foreach( $line in $rawoutput )
{
$sep = $line.IndexOf(":")
if( $sep -ne -1 )
{
$propName = $line.SubString(0, $sep).Trim()
$propValue = $line.SubString($sep+1).Trim()
if( $propValue -eq 'YES' )
{
$propValue = $true
}
elseif( $propValue -eq 'NO' )
{
$propValue = $false
}
$hash.Add($propName,$propValue)
}
}
if( $hash.Count -eq 0 )
{
throw 'dsregcmd returned invalid response'
}
$results.Add($hash)
}
elseif( $wmiquery -eq 'Administrators' )
{
$admins = (get-localgroupmember -SID S-1-5-32-544 -ErrorAction Stop)
foreach( $admin in $admins )
{
$hash = @{
ObjectClass = $admin.ObjectClass
Name = $admin.Name
PrincipalSource = $admin.PrincipalSource
}
$results.Add($hash)
}
}
elseif ($wmiquery.StartsWith("File(") )
{
$first = $wmiquery.IndexOf("'")+1
$last = $wmiquery.LastIndexOf("'")
$fileSpec = [System.Environment]::ExpandEnvironmentVariables( $wmiquery.Substring($first, $last-$first))
foreach( $file in (Get-Item -Force -ErrorAction SilentlyContinue -Path $filespec))
{
$fileSHA256 = ""
$fileMD5 = ""
Try
{
$fileSHA256 = (get-filehash -ErrorAction SilentlyContinue -Path $file).Hash
$fileMD5 = (get-filehash -ErrorAction SilentlyContinue -Path $file -Algorithm MD5).Hash
}
Catch
{
}
$hash = @{
FileName = $file.FullName
Mode = $file.Mode
LastWriteTime = $file.LastWriteTime
Size = $file.Length
Version = $file.VersionInfo.ProductVersion
SHA256Hash = $fileSHA256
MD5Hash = $fileMD5
}
$results.Add($hash)
}
}
elseif ($wmiquery.StartsWith("FileContent(") )
{
$first = $wmiquery.IndexOf("'")+1
$last = $wmiquery.LastIndexOf("'")
$filepath = [System.Environment]::ExpandEnvironmentVariables( $wmiquery.Substring($first, $last-$first) )
#verify if the file exists
if( [System.IO.File]::Exists($filepath) )
{
$lines = (get-content -path $filepath -ErrorAction Stop)
for ($index = 0; $index -lt $lines.Length; $index++)
{
$line = $lines[$index]
$hash = @{
Line = $index+1
Content = $line
}
$results.Add($hash)
}
}
}
elseif ($wmiquery.StartsWith("EventLog(") )
{
$first = $wmiquery.IndexOf("'")+1
$last = $wmiquery.LastIndexOf("'")
$logName = $wmiquery.Substring($first, $last-$first)
$first_time = $wmiquery.LastIndexOf(",")+1
$last_time = $wmiquery.LastIndexOf(")")
$secondsAgo = [System.Int64]::Parse($wmiquery.Substring($first_time, $last_time-$first_time))
$events = get-eventlog -LogName $logName -ErrorAction Stop -After (Get-Date).AddSeconds(-1*$secondsAgo)
foreach ($event in $events)
{
$hash = @{
DateTime = $event.TimeGenerated
EntryType = $event.EntryType
Source = $event.Source
EventID = $Event.EventID
Message = $Event.Message
}
$results.Add($hash)
}
}
elseif ($wmiquery.StartsWith("CcmLog(") )
{
$first = $wmiquery.IndexOf("'")+1
$last = $wmiquery.LastIndexOf("'")
$logFileName = $wmiquery.Substring($first, $last-$first)
$first_time = $wmiquery.LastIndexOf(",")+1
$last_time = $wmiquery.LastIndexOf(")")
$secondsAgo = [System.Int64]::Parse($wmiquery.Substring($first_time, $last_time-$first_time))
$key = [Microsoft.Win32.RegistryKey]::OpenBaseKey([Microsoft.Win32.RegistryHive]::LocalMachine, [Microsoft.Win32.RegistryView]::Registry64)
$subKey = $key.OpenSubKey("SOFTWARE\Microsoft\CCM\Logging\@Global")
$ccmlogdir = $subKey.GetValue("LogDirectory")
$key.Close()
$logPath = (join-path $ccmlogdir ($logFileName+".log"))
#verify format of file name
if(( $logFileName -match '[\w\d-_@]+' ) -and ([System.IO.File]::Exists($logPath)))
{
$lines = (get-content -path $logpath -ErrorAction Stop)
[regex]$ccmLog = '<!\[LOG\[(?<logtext>.*)\]LOG\]!><\s*time\s*\=\s*"(?<time>\d\d:\d\d:\d\d)[^"]+"\s+date\s*\=\s*"(?<date>[^"]+)"\s+component\s*\=\s*"(?<component>[^"]*)"\s+context\s*\=\s*"(?<context>[^"]*)"\s+type\s*\=\s*"(?<type>[^"]+)"\s+thread\s*\=\s*"(?<thread>[^"]+)"\s+file\s*\=\s*"(?<file>[^"]+)"\s*>'
for( $index = $lines.Length-1; $index -ge 0; $index-- )
{
$line = $lines[$index]
$m = $ccmLog.Match($line)
if( $m.Success -eq $true )
{
$hash = @{
LogText = $m.Groups["logtext"].Value
DateTime = ([DateTime]($m.Groups["date"].Value +' '+ $m.Groups["time"].Value)).ToUniversalTime()
Component = $m.Groups["component"].Value
Context = $m.Groups["context"].Value
Type = $m.Groups["type"].Value
Thread = $m.Groups["thread"].Value
File = $m.Groups["file"].Value
}
# Filter out logs based on timespan
if ( [System.DateTime]::Compare($hash.DateTime, (Get-Date).AddSeconds(-1*$secondsAgo).ToUniversalTime()) -lt 0 )
{
break
}
else
{
$results.Add($hash)
}
}
}
# Reverse the results list to ascending datetime
$results.Reverse()
}
}
elseif ($wmiquery.StartsWith("WinEvent("))
{
$first = $wmiquery.IndexOf("'")+1
$last = $wmiquery.LastIndexOf("'")
$logFileName = $wmiquery.Substring($first, $last-$first)
$first_time = $wmiquery.LastIndexOf(",")+1
$last_time = $wmiquery.LastIndexOf(")")
$secondsAgo = [System.Int64]::Parse($wmiquery.Substring($first_time, $last_time-$first_time))
$ComputerName = [System.Environment]::MachineName
$EventStartDate = (Get-Date).AddSeconds(-1*$secondsAgo)
$EventEndTime = (Get-Date)
$filterTable = @{logname = $logFileName; StartTime=$EventStartDate; EndTime=$EventEndTime}
# Filter out the winEvent logs that we need
try
{
$winEvents = Get-WinEvent -ComputerName $ComputerName -FilterHashTable $filterTable -ErrorAction Stop
}
catch
{
}
foreach ($winEvent in $winEvents)
{
$hash = @{
DateTime = $winEvent.TimeCreated
LevelDisplayName = $winEvent.LevelDisplayName
ProviderName = $winEvent.ProviderName
ID = $winEvent.ID
Message = $winEvent.Message
}
$results.Add($hash)
}
}
elseif ($wmiquery.StartsWith("Registry(") )
{
$first = $wmiquery.IndexOf("'")+1
$last = $wmiquery.LastIndexOf("'")
$regSpec = $wmiquery.Substring($first, $last-$first)
$result = New-Object System.Collections.Generic.List[Object]
foreach( $regKey in (Get-Item -ErrorAction SilentlyContinue -Path $regSpec) )
{
foreach( $regValue in $regKey.Property )
{
$val = $regKey.GetValue($regValue)
if( $val -ne $null)
{
if( $val.GetType() -eq [Byte[]] )
{
$val = [System.BitConverter]::ToString($val)
}
elseif( $val.GetType() -eq [String[]] )
{
$val = [System.String]::Join(", ", $val)
}
$hash = @{
Property = $regValue
Value = $val.ToString()
}
}
$results.Add($hash)
}
}
}
elseif ($wmiquery.StartsWith("ProcessModule(") )
{
$first = $wmiquery.IndexOf("'")+1
$last = $wmiquery.LastIndexOf("'")
$processName = $wmiquery.Substring($first, $last-$first)
$modules = get-process -name $processName -module -ErrorAction SilentlyContinue
foreach ($module in $modules)
{
$hash = @{
ModuleName = $module.ModuleName
FileName = $module.FileName
FileVersion = $module.FileVersion
Size = $module.Size
MD5Hash = (get-filehash -ErrorAction SilentlyContinue -Path $module.FileName -Algorithm MD5).Hash
}
$results.Add($hash)
}
}
else
{
$namespace = "root/cimv2"
# if there is a namespace
if( ($wmiquery.StartsWith("root/")) -and ($wmiquery.Contains(":")))
{
$seperator = $wmiquery.IndexOf(":")
$namespace = $wmiquery.Substring(0, $seperator)
$wmiquery = $wmiquery.Substring($seperator+1)
}
# Execute the query
$wmiresult = (get-wmiobject -query $wmiquery -Namespace $namespace -ErrorAction Stop)
# create result set
$result = New-Object System.Collections.Generic.List[Object]
foreach( $obj in $wmiresult )
{
$hash = @{}
for( $i=0; $i -lt $propertyFilter.Length; $i++ )
{
$propName = $propertyFilter[$i]
$propValue = $obj."$propName"
if( $propValue -ne $null)
{
$hash[$propName] = $($propertySerializer[$i].Invoke($propValue))
}
else
{
$hash[$propName] = $null
}
}
$results.Add($hash)
}
}
# Write the results to the data table
foreach( $obj in $results )
{
# Add a row in data table
$insertRow = $dt.NewRow()
$device = New-Object Microsoft.ConfigurationManagement.AdminConsole.CMPivotParser.Device ([System.Environment]::MachineName), 1
$insertRow.Device = [Microsoft.ConfigurationManagement.AdminConsole.CMPivotParser.Device]$device
for( $i=0; $i -lt $propertyFilter.Length; $i++ )
{
$propName = $propertyFilter[$i]
$propValue = $obj."$propName"
if( $propValue -ne $null)
{
switch($propertyTypes[$i])
{
"Boolean"
{
$insertRow."$propName" = [System.Boolean]$propValue
break
}
"Number"
{
$insertRow."$propName" = [System.Int64]$propValue
break
}
"TimeSpan"
{
$insertRow."$propName" = [System.TimeSpan]$propValue
break
}
"DateTime"
{
$insertRow."$propName" = ([System.DateTime]$propValue).ToUniversalTime()
break
}
default
{
$insertRow."$propName" = $propValue
}
}
}
else
{
$insertRow."$propName" = [System.DBNull]::Value
}
}
$dt.Rows.Add($insertRow)
}
# Add the data table to list
$datatables.Add($dt)
}
# Call the static method to evaluate the pivot query
$maxResultSize = 128000
$moreResults = $false
$eval_result = [Microsoft.ConfigurationManagement.AdminConsole.CMPivotParser.KustoParser]::Evaluate($kustoquery, $datatables, $maxResultSize, [ref]$moreResults)
# Add an attribute to result node to indicate if there should be more results
$writer.WriteAttributeString("moreResults", $moreResults.ToString())
# Write the results to Xml
foreach ( $dr in $eval_result.Rows )
{
$writer.WriteStartElement("e")
$writer.WriteAttributeString("_i", 0 )
foreach ( $dc in $eval_result.Columns )
{
$prop = $dc.ColumnName
# Skip Device column in writing to xml
if ($prop -eq 'Device')
{
continue
}
$Value = $dr."$prop"
if( !([DBNull]::Value).Equals($Value) )
{
if( $Value.GetType() -eq [DateTime] )
{
$writer.WriteAttributeString("$prop", $Value.ToString("yyyy-MM-dd HH:mm:ss", [CultureInfo]::InvariantCulture))
}
else
{
$writer.WriteAttributeString("$prop", $Value.ToString() )
}
}
}
$writer.WriteEndElement()
}
}
Catch
{
#format the exception as an xml
$sb = New-Object System.Text.StringBuilder
$sw = New-Object System.IO.StringWriter($sb)
$writer = New-Object System.Xml.XmlTextWriter($sw)
$writer.WriteStartDocument()
$writer.WriteStartElement("result")
$writer.WriteAttributeString("ResultCode", 0x80004005 )
$writer.WriteStartElement("error")
$writer.WriteAttributeString("ErrorMessage", $_.Exception.Message )
$writer.WriteEndElement()
# Dispose the datatable if catch an exception
if( $dt -ne $null )
{
$dt.Dispose()
$datatables = $null
}
}
# Finish off Xml
$writer.WriteEndElement()
$writer.WriteEndDocument()
$writer.Flush()
$writer.Close()
$writer.Close()
$sw.Dispose()
$Bytes = [System.Text.Encoding]::Unicode.GetBytes($sb.ToString())
if( $Bytes.Length -lt 4096 )
{
return [Convert]::ToBase64String($Bytes)
}
else
{
# Otherwise compress
[System.IO.MemoryStream] $output = New-Object System.IO.MemoryStream
$gzipStream = New-Object System.IO.Compression.GzipStream $output, ([IO.Compression.CompressionMode]::Compress)
$gzipStream.Write( $Bytes, 0, $Bytes.Length )
$gzipStream.Close()
$output.Close()
return [Convert]::ToBase64String($output.ToArray())
}
We are on WhatsApp now. To get the latest step-by-step guides, news, and updates, Join our Channel. Click here – HTMD WhatsApp.
Author
Anoop C Nair has been Microsoft MVP for 10 consecutive years from 2015 onwards. He is a Workplace Solution Architect with more than 22+ years of experience in Workplace technologies. He is a Blogger, Speaker, and Local User Group Community leader. His primary focus is on Device Management technologies like SCCM and Intune. He writes about technologies like Intune, SCCM, Windows, Cloud PC, Windows, Entra, Microsoft Security, Career, etc.