ConfigMgr CMPivot Logs | Background Process Guide | SCCM | Configuration Manager | Endpoint Manager

Let’s have a quick walkthrough of ConfigMgr CMPivot Logs and Background processes. Microsoft introduced CMPivot with 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.

You can get the architecture details of CMPivot and how it works details from following the post. SCCM CMPivot Architecture Fast Channel Notification | ConfigMgr. However, in this post, I will concentrate more on CMPivot log file entries and backend processes.

CMPivot Queries

I have many blog posts related to CMPivot queries and some of them are listed below. All of these posts explain how to start running CMPivot queries against ONLINE clients.

Patch My PC

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
ConfigMgr CMPivot Logs | Background Process Guide | SCCM
ConfigMgr CMPivot Logs | Background Process Guide | SCCM

ConfigMgr CMPivot Logs

Let’s have a quick look at the ConfigMgr CMPivot Logs (serverside 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.

1E Nomad
Server-side CMPivot LogsC:\Program Files\Microsoft Configuration Manager\LogsClient-side CMPivot Logs C:\Windows\CCM\Logs
SmsProv.logCcmNotificationAgent.log
BgbServer.logScripts.log
StateSys.logStateMessage.log
Server-side/Client-Side CMPivot Logs

SMSProv.log

The SMSProv.log is the first log that will get initiated when you run a CMPivot query. Let’s have a quick look at the process and important search keywords that can help you with CMPivot troubleshooting.

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 the 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
ConfigMgr CMPivot Logs | Background Process Guide | SCCM
ConfigMgr CMPivot Logs | Background Process Guide | SCCM

BgpServer.log

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 used in CMPivot to deliver the 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)
ConfigMgr CMPivot Logs | Background Process Guide | SCCM
ConfigMgr CMPivot Logs | Background Process Guide | SCCM

StateSys.log

I have not seen any activity on the SMS_State_System component apart from few lines of summarization. So I’m not sure how relevant is this log file 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……
ConfigMgr CMPivot Logs | Background Process Guide | SCCM
ConfigMgr CMPivot Logs | Background Process Guide | SCCM

Script.log

Script.log is the actual log where the CMPivot query execution at client-side related details are stored. Does this mean CMPivot is using PowerShell at the client end to execute the instructions? Well, I will let you find out the from the below log snippets.

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.
  • 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}
ConfigMgr CMPivot Logs | Background Process Guide | SCCM
ConfigMgr CMPivot Logs | Background Process Guide | SCCM

StateMessage.log

The State Message log at the client-side is a place where you can get the 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

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\[email protected]]+' ) -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())
}

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.