对 CMPivot 的更改Changes to CMPivot

请参阅以下信息来了解 Configuration Manager 版本之间对 CMPivot 所做的更改:Use the following information to learn about changes made to CMPivot between Configuration Manager versions:

版本 2006 的 CMPivot 更改CMPivot changes for version 2006

自版本 2006 起,对 CMPivot 进行了以下改进:Starting in version 2006, the following improvements have been made for CMPivot:

  • 聚合了 CMPivot 独立应用和从管理控制台启动的 CMPivot。CMPivot standalone and CMPivot launched from the admin console have converged. 从管理控制台启动 CMPivot 时,它使用与 CMPivot 独立版相同的基础技术来进行场景奇偶校验。When you launch CMPivot from the admin console, it uses the same underlying technology as CMPivot standalone to give you scenario parity.

  • 改进了 CMPivot 中的键盘导航。Improvements for keyboard navigation in CMPivot.

  • 可以在“设备”节点中的单台或多台设备中运行 CMPivot,而不必选择设备集合。You can run CMPivot from an individual device or multiple devices from the devices node without needing to select a device collection. 此项改进使用户(例如作为支持人员角色的人员)可以在预先创建的集合之外为特定设备轻松创建 CMPivot 查询。This improvement makes it easier for people, such as those working as the Helpdesk persona, to create CMPivot queries for specific devices outside a pre-created collection.

    • 选择设备集合中的单台或多台设备,然后选择“启动 CMPivot”。Select an individual device or multi-select devices in a device collection or then select Start CMPivot.
  • 在查询列表视图中返回设备后,可以在单台或多台设备上选择“设备透视”,然后只对这些设备进行透视和查询,以便进一步钻取。Upon returning devices within a query list view, you can select Device Pivot on one or more devices and then pivot and query on just those devices to drill in further. 此更改允许你进行钻取,而无需查询原始集合中的更大设备集。This change allows you to drill in without querying the larger set of devices from the original collection. “透视到”已替换为“设备透视”。Device Pivot replaced Pivot to.

    • 在现有的 CMPivot 操作中,从输出中选择单台或多台设备。Within an existing CMPivot operation, select an individual device or multi-select devices from the output. 右键单击,然后使用“设备透视”选项进行透视。Right-click and pivot using the Device Pivot option. 此操作启动限定为只包含你所选设备的单独 CMPivot 实例。This action launches a separate CMPivot instance scoped to just the devices you selected. 这样就可以更轻松地对所需设备进行透视和查询,而无需为它们创建集合。This makes it easier to pivot and just query on devices desired without needing to create a collection for them.
  • 当你为单台设备运行 CMPivot 时,设备名会列在窗口顶部。When you run CMPivot for an individual device, the device name is listed at the top of the window. 对于多台设备,所选设备的数量会列在窗口顶部。For multiple devices, the number of devices selected is listed at the top of the window.

  • 删除了“查询摘要”选项卡中的“创建集合”选项,因为 CMPivot 不再需要对集合进行查询。The Create Collection option in the Query Summary tab was removed since CMPivot no longer requires querying against a collection. 执行“设备透视”,以打开限定为只包含你要执行查询的设备的新 CMPivot 实例。Perform a Device Pivot to open a new instance of CMPivot scoped to just the devices you want to query on. 主菜单上仍有“创建集合”。Create Collection is still available on the main menu.

使用 CMPivot 对多台设备进行设备透视

版本 2002 的 CMPivot 更改CMPivot changes for version 2002

我们简化了 CMPivot 实体的导航。We've made it easier to navigate CMPivot entities. 从 Configuration Manager 版本 2002 开始,可以搜索 CMPivot 实体。Starting in Configuration Manager version 2002, you can search CMPivot entities. 还添加了新图标,以轻松地区分实体和实体对象类型。New icons have also been added to easily differentiate the entities and the entity object types.

搜索 CMPivot 实体

版本 1910 的 CMPivot 更改CMPivot changes for version 1910

从版本 1910 开始,CMPivot 进行了显著的优化,以减少服务器上的网络流量和负载。Starting in version 1910, CMPivot was significantly optimized to reduce network traffic and load on your servers. 此外,还添加了一些实体和实体增强功能,以帮助进行故障排除和搜寻。Additionally, a number of entities and entity enhancements were added to aid in troubleshooting and hunting. 版本 1910 中为 CMPivot 引入了以下更改:The following changes were introduced for CMPivot in version 1910:

对 CMPivot 引擎的优化Optimizations to the CMPivot engine

为了减少服务器上的网络流量和负载,CMPivot 在版本 1910 中进行了优化。To reduce network traffic and load on your servers, CMPivot was optimized in 1910. 许多查询操作现在直接在客户端上执行,而不是在服务器上执行。Many query operations are now performed directly on the client rather than on the servers. 此更改还意味着某些 CMPivot 操作从第一个查询返回的数据最少。This change also means that some CMPivot operations return minimal data from the first query. 如果决定钻取数据以获取更多信息,则可能会运行一个新查询以从客户端获取其他数据。If you decide to drill into the data for more information, a new query might run to fetch the additional data from the client. 例如,以前运行“汇总计数”查询时,会向服务器返回一个大型数据集。For instance, previously a large data set was returned to the server when you ran a "summarized count" query. 虽然返回大型数据集可立即进行向下钻取,但很多时候只需要汇总计数即可。While returning a large data set offered immediate drill-down, many times only the summarized count was needed. 在版本 1910 中,当选择钻取到特定客户端时,会发生另一个数据集合,以返回你请求的其他数据。In 1910 when you choose to drill into a specific client, another collection of the data occurs to return the additional data you've requested. 此更改有助于改善针对大量客户端进行查询的性能和可伸缩性。This change brings better performance and scalability to queries against a large number of clients.

示例Examples

CMPivot 优化大大减少了运行 CMPivot 查询所需的网络和服务器 CPU 负载。The CMPivot optimizations drastically reduce the network and server CPU load needed to run CMPivot queries. 通过这些优化,现在可以实时筛选千兆字节的客户端数据。With these optimizations, we can now sift through gigabytes of client data in real time. 以下查询说明了这些优化:The following queries illustrate these optimizations:

  • 搜索企业中所有客户端的所有事件日志中的身份验证失败。Search all event logs on all clients in your enterprise for authentication failures.

    EventLog('Security')
    | where  EventID == 4673
    | summarize count() by Device
    | order by count_ desc
    
  • 按哈希搜素文件。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')
    

WinEvent(<logname>,[<timespan>])WinEvent(<logname>,[<timespan>])

该实体用于从事件日志和事件跟踪日志文件中获取事件。This entity is used to get events from event logs and event tracing log files. 该实体从 Windows 事件日志技术生成的事件日志中获取数据。The entity gets data from event logs that are generated by the Windows Event Log technology. 该实体还获取由 Windows 事件跟踪 (ETW) 生成的日志文件中的事件。The entity also gets events in log files generated by Event Tracing for Windows (ETW). 默认情况下,WinEvent 会查看最近 24 小时内发生的事件。WinEvent looks at events that have occurred within the last 24 hours by default. 但是,可以通过包含时间跨度来覆盖 24 小时默认值。However, the 24-hour default can be overridden by including a timespan.

WinEvent('Microsoft-Windows-HelloForBusiness/Operational', 1d)
| where LevelDisplayName =='Error'
| summarize count() by Device

FileContent(<filename>)FileContent(<filename>)

FileContent 用于获取文本文件的内容。FileContent is used to get the contents of a text file.

FileContent('c:\\windows\\SMSCFG.ini')
| where Content startswith  'SMS Unique Identifier='
| project Device, SMSId= substring(Content,22)

ProcessModule(<processname>)ProcessModule(<processname>)

该实体用于枚举给定进程加载的模块 (dll)。This entity is used to enumerate the modules (dlls) loaded by a given process. 在搜寻合法进程中隐藏的恶意软件时,ProcessModule 非常有用。ProcessModule is useful when hunting for malware that hides in legitimate processes.

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

AadStatusAadStatus

该实体可用于从设备获取当前 Azure Active Directory 的标识信息。This entity can be used to get the current Azure Active Directory identity information from a device.

AadStatus
| project Device, IsAADJoined=iif( isnull(DeviceId),'No','Yes')
| summarize DeviceCount=count() by IsAADJoined
| render piechart

EPStatusEPStatus

EPStatus 用于获取计算机上安装的反恶意软件的状态。EPStatus is used to get the status of antimalware software installed on the computer.

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

使用 CMPivot 独立应用的本地设备查询评估Local device query evaluation using CMPivot standalone

在 Configuration Manager 控制台外使用 CMPivot 时,可以仅查询本地设备,而无需 Configuration Manager 基础结构。When using CMPivot outside of the Configuration Manager console, you can query just the local device without the need for the Configuration Manager infrastructure. 现在可以利用 CMPivot Azure Log Analytics 查询快速查看本地设备上的 WMI 信息。You can now leverage the CMPivot Azure Log Analytics queries to quickly view WMI information on the local device. 还可以在较大的环境中运行 CMPivot 查询之前对其进行验证和优化。This also enables validation and refinement of CMPivot queries, before running them in a larger environment. CMPivot 独立应用仅提供英语版本。CMPivot standalone is only available in English. 若要详细了解 CMPivot 独立应用,请参阅 CMPivot 独立应用For more information about CMPivot standalone, see CMPivot standalone.

本地设备查询评估的已知问题Known issues for local device query evaluation

  • 如果在“此电脑”上查询你无权访问的 WMI 实体(例如锁定的 WMI 类),则可能会在 CMPivot 中出现故障。If you query on This PC for a WMI entity that you don't have access to, such as a locked down WMI class, you may see a crash in CMPivot. 使用具有提升权限的帐户运行 CMPivot 以查询这些实体。Run CMPivot using an account with elevated privileges to query those entities.
  • 如果在“此电脑”上查询非 WMI 实体,你将看到“无效的命名空间”或不明确的异常 。If you query non-WMI entities on This PC , you'll see an Invalid namespace or an ambiguous exception.
  • 从“开始”菜单快捷方式运行 CMPivot 独立应用,而不是直接从可执行文件的路径运行。Run CMPivot standalone from the start menu shortcut, not directly from the path of the executable file.

其他增强功能Other enhancements

  • 可以使用新的 like 运算符执行正则表达式类型查询。You can do regular expression type queries using the new like operator. 例如:For example:

    //Find BIOS manufacture that contains any word like Micro, such as Microsoft
    Bios
    | where Manufacturer like '%Micro%'
    
  • 我们已将 CcmLog() 和 EventLog() 实体更新为在默认情况下仅查看最近 24 小时内的消息 。We've updated the CcmLog() and EventLog() entities to only look at messages in the last 24 hours by default. 通过传入可选的时间跨度可以覆盖此行为。This behavior can be overridden by passing in an optional timespan. 例如,以下查询将查看最近 1 小时内的事件:For example, the following query will look at events in the last 1 hour:

    CcmLog('Scripts',1h)
    
  • File() 实体已更新为收集有关隐藏文件和系统文件的信息,并包含 MD5 哈希。The File() entity has been updated to collect information about Hidden and System files, and include the MD5 hash. 虽然 MD5 哈希不像 SHA256 哈希那么准确,但它通常是大多数恶意软件公告中的常见哈希。While an MD5 hash isn't as accurate as the SHA256 hash, it tends to be the commonly reported hash in most malware bulletins.

  • 您可以在查询中添加注释。You can add comments in queries. 共享查询时,此行为很有用。This behavior is useful when sharing queries. 例如:For example:

    //Get the top ten devices sorted by user
    Device
    | top 10 by UserName
    
  • CMPivot 会自动连接到最后一个站点。CMPivot automatically connects to the last site. 启动 CMPivot 后,可以根据需要连接到新站点。After you start CMPivot, you can connect to a new site if necessary.

  • 从“导出”菜单中,选择新选项“查询链接到剪贴板” 。From the Export menu, select the new option to Query link to clipboard. 此操作会将链接复制到剪贴板,以便与他人共享。This action copies a link to the clipboard that you can share with others. 例如:For example:

    cmpivot:Ly8gU2FtcGxlIHF1ZXJ5DQpPcGVyYXRpbmdTeXN0ZW0NCnwgc3VtbWFyaXplIGNvdW50KCkgYnkgQ2FwdGlvbg0KfCBvcmRlciBieSBjb3VudF8gYXNjDQp8IHJlbmRlciBiYXJjaGFydA==

    此链接将打开 CMPivot 独立版本并包含以下查询:This link opens CMPivot standalone with the following query:

    // Sample query
    OperatingSystem
    | summarize count() by Caption
    | order by count_ asc
    | render barchart
    

    提示

    要使此链接正常工作,请安装 CMPivot 独立版本For this link to work, install CMPivot standalone.

  • 在查询结果中,如果设备已在 Microsoft Defender 高级威胁防护 (ATP) 中注册,请右键单击该设备以启动 Microsoft Defender 安全中心在线门户。In query results, if the device is enrolled in Microsoft Defender Advanced Threat Protection (ATP), right-click the device to launch the Microsoft Defender Security Center online portal.

版本 1910 中 CMPivot 的已知问题Known issues for CMPivot in version 1910

  • 当达到限制时,不显示最大结果横幅。The maximum results banner may not be displayed when the limit is reached.
    • 每个客户端每次查询的数据大小限制为 128 KB。Each client is limited to 128 KB worth of data per query.
    • 如果查询的结果超过 128 KB,可能会截断结果。Results may be truncated if the results of the query exceed 128 KB.

版本 1906 的 CMPivot 更改CMPivot changes for version 1906

从版本 1906 开始,已向 CMPivot 添加以下项:Starting in version 1906, the following items were added to CMPivot:

在 CMPivot 中添加联接、其他运算符和聚合器Add joins, additional operators, and aggregators in CMPivot

你现在有更多的算术运算符和聚合器,还可以添加查询联接(例如可以同时使用注册表和文件)。You now have additional arithmetic operators, aggregators, and the ability to add query joins such as using Registry and File together. 已添加以下项:The following items have been added:

表运算符Table operators

表运算符Table operators 说明Description
联接join 通过匹配同一设备的行来合并两个表的行,以便形成新的表Merge the rows of two tables to form a new table by matching row for the same device
呈现render 将结果呈现为图形输出Renders results as graphical output

CMPivot 中已存在呈现运算符。The render operator already exists in CMPivot. 已添加对多序列和“with”语句的支持。Support for multiple series and the with statement were added. 有关详细信息,请参阅示例部分和 Kusto 的联接运算符一文。For more information, see the examples section and Kusto's join operator article.

联接的限制Limitations for joins

  1. 联接列始终在“Device”字段上隐式完成。The join column is always implicitly done on the Device field.
  2. 每个查询最多可使用 5 个联接。You can use a maximum of 5 joins per query.
  3. 最多可使用 64 个合并列。You can use a maximum of 64 combined columns.

标量运算符Scalar operators

运算符Operator 说明Description 示例Example
+ 添加Add 2 + 1, now() + 1d
- Subtract 2 - 1, now() - 1d
* Multiply 2 * 2
/ Divide 2 / 1
% 取模Modulo 2 % 1

聚合函数Aggregation functions

函数Function 说明Description
percentile()percentile() 针对由 Expr 定义的填充,返回其中指定的最接近排名百分位数的估计值Returns an estimate for the specified nearest-rank percentile of the population defined by Expr
sumif()sumif() 返回谓词计算结果为 True 的 Expr 总和Returns a sum of Expr for which Predicate evaluates to true

标量函数Scalar functions

函数Function 说明Description
case()case() 计算谓词的列表,并返回满足其谓词的第一个结果表达式Evaluates a list of predicates and returns the first result expression whose predicate is satisfied
iff()iff() 计算第一个参数,并根据谓词计算结果为 True(第二个)还是 False(第三个),返回第二个或第三个参数的值Evaluates the first argument and returns the value of either the second or third arguments depending on whether the predicate evaluated to true (second) or false (third)
indexof()indexof() 该函数报告输入字符串中指定字符串第一次出现时从零开始的索引Function reports the zero-based index of the first occurrence of a specified string within input string
strcat()strcat() 连接 1 个到 64 个自变量Concatenates between 1 and 64 arguments
strlen()strlen() 返回输入字符串的长度(以字符为单位)Returns the length, in characters, of the input string
substring()substring() 从源字符串中提取从某个索引开始到字符串结尾的 substringExtracts a substring from a source string starting from some index to the end of the string
tostring()tostring() 将输入转换为字符串操作Converts input to a string operation

示例Examples

  • 显示设备、制造商、模型和 OSVersion:Show device, manufacturer, model, and OSVersion:

    ComputerSystem
    | project Device, Manufacturer, Model
    | join (OperatingSystem | project Device, OSVersion=Caption)
    
  • 显示设备的启动时间图:Show graph of boot times for a device:

    SystemBootData
    | where Device == 'MyDevice'
    | project SystemStartTime, BootDuration, OSStart=EventLogStart, GPDuration, UpdateDuration
    | order by SystemStartTime desc
    | render barchart with (kind=stacked, title='Boot times for MyDevice', ytitle='Time (ms)')
    

    以毫秒为单位显示设备启动时间的堆积条形图

已向安全管理员角色添加 CMPivot 权限Added CMPivot permissions to the Security Administrator role

从版本 1906 开始,已向 Configuration Manager 的内置安全管理员角色添加以下权限:Starting in version 1906, the following permissions have been added to Configuration Manager's built-in Security Administrator role:

  • 读取 SMS 脚本Read on SMS Script
  • 在集合上运行 CMPivotRun CMPivot on Collection
  • 读取清单报表Read on Inventory Report

备注

“运行脚本”是“运行 CMPivot”权限的超集。Run Scripts is a super set of the Run CMPivot permission.

CMPivot 独立应用CMPivot standalone

从版本 1906 开始,可以将 CMPivot 用作独立应用。Starting in version 1906, you can use CMPivot as a standalone app. CMPivot 独立应用仅提供英语版本。CMPivot standalone is only available in English. 在 Configuration Manager 控制台外部运行 CMPivot,可以查看环境中设备的实时状态。Run CMPivot outside of the Configuration Manager console to view the real-time state of devices in your environment. 借助此变化,无需先安装控制台,即可在设备上使用 CMPivot。This change enables you to use CMPivot on a device without first installing the console.

提示

此功能在版本 1906 中作为预发行功能首次引入。This feature was first introduced in version 1906 as a pre-release feature. 从版本 2002 开始,此功能不再属于预发行功能。Beginning with version 2002, it's no longer a pre-release feature.

可以与其他尚未在计算机上安装控制台的角色(例如支持人员或安全管理员)共享功能强大的 CMPivot。You can share the power of CMPivot with other personas, such as helpdesk or security admins, who don't have the console installed on their computer. 这些其他角色可以将 CMPivot 与他们传统上使用的其他工具并行使用,以查询 Configuration Manager。These other personas can use CMPivot to query Configuration Manager alongside the other tools that they traditionally use. 通过共享此类丰富的管理数据,你们可以一起工作,共同主动解决跨角色的业务问题。By sharing this rich management data, you can work together to proactively solve business problems that cross roles.

安装 CMPivot 独立应用Install CMPivot standalone

  1. 设置运行 CMPivot 所需的权限。Set up the permissions needed to run CMPivot. 有关详细信息,请参阅先决条件For more information, see prerequisites. 如果这些权限适用于用户,则还可以使用安全管理员角色You can also use the Security Administrator role if the permissions are appropriate for the user.

  2. 在下面的路径找到 CMPivot 应用安装程序:<site install path>\tools\CMPivot\CMPivot.msiFind the CMPivot app installer in the following path: <site install path>\tools\CMPivot\CMPivot.msi. 可以从此路径运行它,也可以将其复制到其他位置。You can run it from that path, or copy it to another location.

  3. 运行 CMPivot 独立应用时,系统将要求你连接到站点。When you run the CMPivot standalone app, you'll be asked to connect to a site. 指定管理中心或主站点服务器的完全限定的域名或计算机名。Specify the fully qualified domain name or computer name of either the Central Administration or primary site server.

    • 每次打开 CMPivot 时,系统将提示你连接到站点服务器。Each time you open CMPivot standalone you'll be prompted to connect to a site server.
  4. 浏览到要在其上运行 CMPivot 的集合,然后运行查询。Browse to the collection on which you want to run CMPivot, then run your query.

    浏览到要对其运行查询的集合

备注

  • 右键单击操作(例如,“运行脚本”、“资源浏览器”)和 Web 搜索在 CMPivot 独立应用中不可用 。Right-click actions, such as Run Scripts, Resource Explorer, and web search aren't available in CMPivot standalone. CMPivot 独立应用的主要用途是独立于 Configuration Manager 基础结构进行查询。CMPivot standalone's primary use is querying independently from the Configuration Manager infrastructure. 为了帮助安全管理员,CMPivot 独立应用确实包含连接到 Microsoft Defender 安全中心的功能。To help security administrators, CMPivot standalone does include the ability to connect to Microsoft Defender Security Center.
  • 自版本 1910 起,可以执行使用 CMPivot 独立应用的本地设备查询评估Starting in version 1910, you can do local device query evaluation using CMPivot standalone.

版本 1902 的 CMPivot 更改CMPivot changes for version 1902

从 Configuration Manager 版本 1902 开始,可以在层次结构中从管理中心站点 (CAS) 运行 CMPivot。Starting in Configuration Manager version 1902, you can run CMPivot from the central administration site (CAS) in a hierarchy. 主站点仍可处理与客户端的通信。The primary site still handles the communication to the client. 从管理中心站点运行 CMPivot 时,它将通过高速消息订阅通道与主站点通信。When running CMPivot from the central administration site, it communicates with the primary site over the high-speed message subscription channel. 该通信不依赖于站点之间的标准 SQL Server 复制。This communication doesn't rely upon standard SQL Server replication between sites.

当 SQL Server 或 SMS 提供程序不在同一台计算机上时,或在 SQL Server Always On 可用性组配置的情况下,在 CAS 上运行 CMPivot 将需要其他权限。Running CMPivot on the CAS will require additional permissions when SQL Server or the SMS Provider aren't on the same machine or in the case of SQL Server Always On availability group configuration. 使用这些远程配置,即可为 CMPivot 配置“双跃点方案”。With these remote configurations, you have a "double hop scenario" for CMPivot.

若要在这种“双跃点方案”中让 CMPivot 使用 CAS,可以定义约束委派。To get CMPivot to work on the CAS in such a "double hop scenario", you can define constrained delegation. 若要了解此配置的安全隐患,请阅读 Kerberos 约束委派一文。To understand the security implications of this configuration, read the Kerberos constrained delegation article. Kerberos 需要使用计算机之间的所有跃点。Kerberos needs to work through all of the hops between the machines. 如果正在使用或未使用 CAS 并置多个远程配置(例如 SQL Server 或 SMS 提供程序),或有多个受信任林,则可能需要权限设置组合。If you have more than one remote configuration such as SQL Server or SMS Provider being colocated with the CAS or not, or multiple trusted forests, you may require a combination of permission settings. 下面是你可能需要遵循的步骤:Below are the steps that you may need to take:

CAS 具有远程 SQL ServerCAS has a remote SQL Server

  1. 转到每个主站点的 SQL Server。Go to each primary site's SQL Server.

    1. 将 CAS 远程 SQL Server 和 CAS 站点服务器添加到 Configmgr_DviewAccess 组。Add the CAS remote SQL Server and the CAS site server to the Configmgr_DviewAccess group. 主站点 SQL Server 上的 Configmgr_DviewAccess 组Configmgr_DviewAccess group on a primary site's SQL Server
  2. 转到 Active Directory 用户和计算机。Go to Active Directory Users and Computers.

    1. 对于每个主站点服务器,请右键单击并选择“属性”。For each primary site server, right click and select Properties.
      1. 在委托选项卡上,选择第三个选项,“仅信任此计算机委派指定的服务”。In the delegation tab, choose the third option, Trust this computer for delegation to specified services only.
      2. 选择“仅使用 Kerberos”。Choose Use Kerberos only.
      3. 使用端口和实例添加 CAS SQL Server 服务。Add the CAS's SQL Server service with port and instance.
      4. 请确保这些更改与公司安全策略保持一致!Make sure these changes align with your company security policy!
    2. 对于 CAS 站点,请右键单击并选择“属性”。For the CAS site, right click and select Properties.
      1. 在委托选项卡上,选择第三个选项,“仅信任此计算机委派指定的服务”。In the delegation tab, choose the third option, Trust this computer for delegation to specified services only.
      2. 选择“仅使用 Kerberos”。Choose Use Kerberos only.
      3. 使用端口和实例添加每个主站点的 SQL Server 服务。Add each primary site's SQL Server service with port and instance.
      4. 请确保这些更改与公司安全策略保持一致!Make sure these changes align with your company security policy!

    双跃点的 CMPivot AD 委派示例

CAS 具有远程提供程序CAS has a remote provider

  1. 转到每个主站点的 SQL Server。Go to each primary site's SQL Server.
    1. 将 CAS 提供程序计算机帐户和 CAS 站点服务器添加到 Configmgr_DviewAccess 组。Add the CAS provider machine account and the CAS site server to the Configmgr_DviewAccess group.
  2. 转到 Active Directory 用户和计算机。Go to Active Directory Users and Computers.
    1. 选择 CAS 提供程序计算机,右键单击并选择“属性”。Select the CAS provider machine, right click and select Properties.
      1. 在委托选项卡上,选择第三个选项,“仅信任此计算机委派指定的服务”。In the delegation tab, choose the third option, Trust this computer for delegation to specified services only.
      2. 选择“仅使用 Kerberos”。Choose Use Kerberos only.
      3. 使用端口和实例添加每个主站点的 SQL Server 服务。Add each primary site's SQL Server service with port and instance.
      4. 请确保这些更改与公司安全策略保持一致!Make sure these changes align with your company security policy!
    2. 选择 CAS 站点服务器,右键单击并选择“属性”。Select the CAS site server, right click and select Properties.
      1. 在委托选项卡上,选择第三个选项,“仅信任此计算机委派指定的服务”。In the delegation tab, choose the third option, Trust this computer for delegation to specified services only.
      2. 选择“仅使用 Kerberos”。Choose Use Kerberos only.
      3. 使用端口和实例添加每个主站点的 SQL Server 服务。Add each primary site's SQL Server service with port and instance.
      4. 请确保这些更改与公司安全策略保持一致!Make sure these changes align with your company security policy!
  3. 重启 CAS 远程提供程序计算机。Restart the CAS remote provider machine.

SQL Server Always On 可用性组SQL Server Always On availability groups

  1. 转到每个主站点的 SQL Server。Go to each primary site's SQL Server.
    1. 将 CAS 站点服务器添加到 Configmgr_DviewAccess 组。Add the CAS site server to the Configmgr_DviewAccess group.
  2. 转到 Active Directory 用户和计算机。Go to Active Directory Users and Computers.
    1. 对于每个主站点服务器,请右键单击并选择“属性”。For each primary site server, right click and select Properties.
      1. 在委托选项卡上,选择第三个选项,“仅信任此计算机委派指定的服务”。In the delegation tab, choose the third option, Trust this computer for delegation to specified services only.
      2. 选择“仅使用 Kerberos”。Choose Use Kerberos only.
      3. 使用端口和实例为 SQL Server 节点添加 CAS SQL Server 服务帐户。Add the CAS's SQL Server service accounts for the SQL Server nodes with port and instance.
      4. 请确保这些更改与公司安全策略保持一致!Make sure these changes align with your company security policy!
    2. 选择 CAS 站点服务器,右键单击并选择“属性”。Select the CAS site server, right click and select Properties.
      1. 在委托选项卡上,选择第三个选项,“仅信任此计算机委派指定的服务”。In the delegation tab, choose the third option, Trust this computer for delegation to specified services only.
      2. 选择“仅使用 Kerberos”。Choose Use Kerberos only.
      3. 使用端口和实例添加每个主站点的 SQL Server 服务。Add each primary site's SQL Server service with port and instance.
      4. 请确保这些更改与公司安全策略保持一致!Make sure these changes align with your company security policy!
  3. 请确保 SPN 发布使用 CAS 侦听器名称和每个主侦听器名称。Make sure the SPN is published for the CAS listener name and each primary listener name.
  4. 重启主 SQL Server 节点。Restart the primary SQL Server nodes.
  5. 重启 CAS 站点服务器和 CAS SQL Server 节点。Restart the CAS site server and the CAS SQL Server nodes.

版本 1810 的 CMPivot 更改CMPivot changes for version 1810

从 Configuration Manager 版本 1810 开始,CMPivot 就包括以下改进:CMPivot includes the following improvements starting in Configuration Manager version 1810:

CMPivot 实用工具和性能CMPivot utility and performance

  • CMPivot 最多返回 100,000 个单元格而不是 20,000 行。CMPivot will return up to 100,000 cells rather than 20,000 rows.

    • 如果实体有 5 个属性,即表示将显示 5 列和最多 20,000 行。If the entity has 5 properties, meaning 5 columns, up to 20,000 rows will be shown.
    • 对于有 10 个属性的实体,最多显示 10,000 行。For an entity with 10 properties, up to 10,000 rows will be shown.
    • 显示的总数据将小于或等于 100,000 个单元格。The total data shown will be less than or equal to 100,000 cells.
  • 在“查询摘要”选项卡上,选择“故障”或“脱机”设备的计数,然后选择“创建集合”选项。On the Query Summary tab, select the count of Failed or Offline devices, and then select the option to Create Collection. 使用此选项,可通过修正部署轻松定位这些设备。This option makes it easy to target those devices with a remediation deployment.

    • 版本 2006 删除了此选项,因为 CMPivot 不再需要对集合进行查询。This option was removed in version 2006 since CMPivot no longer requires querying against a collection.
  • 通过单击文件夹图标保存收藏夹查询。Save Favorite queries by clicking the folder icon. 在 CMPivot 中保存收藏夹查询的示例Example of saving a favorite query in CMPivot

  • 更新至 1810 版本的客户端会通过快速信道将不超过 80 KB 的输出返回到站点。Clients updated to the 1810 version return output less than 80 KB to the site over a fast communication channel.

    • 这一更改提高了查看脚本或查询输出的性能。This change increases the performance of viewing script or query output.
    • 如果脚本或查询输出大于 80 KB,客户端会通过状态消息发送数据。If the script or query output is greater than 80 KB, the client sends the data via a state message.
    • 如果客户端未更新至 1810 客户端版本,它将继续使用状态消息。If the client isn't updated to the 1810 client version, it continues to use state messages.
  • 启动 CMPivot 时,可能会看到以下错误: 由于脚本版本不兼容,现在无法使用 CMPivot。这个问题可能是因为层次结构正在升级站点所导致的。等待升级完成,然后重试。You may see the following error when you start CMPivot: You can't use CMPivot right now due to an incompatible script version. This issue may be because the hierarchy is in the process of upgrading a site. Wait until the upgrade is complete and then try again.

    • 如果看到此消息,则表示:If you see this message, it could mean:
      • 安全作用域设置不正确。The security scope isn't set up properly.
      • 升级过程中存在一些问题。There are issues with Upgrade in the process.
      • 基础 CMPivot 脚本不兼容。The underlying CMPivot script is incompatible.

标量函数Scalar functions

CMPivot 支持下列标量函数:CMPivot supports the following scalar functions:

  • ago() :从当前的 UTC 时钟时间减去给定的时间跨度ago() : Subtracts the given timespan from the current UTC clock time
  • datetime_diff() :计算两个日期/时间值之间的日历间隔datetime_diff() : Calculates the calendar difference between two datetime values
  • now() :返回当前 UTC 时钟时间now() : Returns the current UTC clock time
  • bin() :将值舍入为给定装箱大小的整数倍数bin() : Rounds values down to an integer multiple of a given bin size

备注

日期/时间数据类型表示某个时刻,通常表示为当天的日期和时间。The datetime data type represents an instant in time, typically expressed as a date and time of day. 时间值以 1 秒为单位进行测量。Time values are measured in 1-second units. 日期/时间值始终位于 UTC 时区中。A datetime value is always in the UTC time zone. 始终采用 ISO 8601 格式表示日期时间文本,例如 yyyy-mm-dd HH:MM:ssAlways express date time literals in ISO 8601 format, for example, yyyy-mm-dd HH:MM:ss

示例Examples

  • datetime(2015-12-31 23:59:59.9):特定的日期时间文本datetime(2015-12-31 23:59:59.9): A specific date time literal
  • now():当前时间now(): The current time
  • ago(1d):当前时间减去一天ago(1d): The current time minus one day

呈现可视化效果Rendering visualizations

CMPivot 现在包括对 KQL render 运算符的基本支持。CMPivot now includes basic support for the KQL render operator. 此支持包括以下类型:This support includes the following types:

  • 条形图 :第一列是 x 轴,可以为文本、日期/时间或数值。barchart : First column is x-axis, and can be text, datetime or numeric. 第二个列必须是数字,并显示为水平条带。The second columns must be numeric and is displayed as a horizontal strip.
  • 柱形图 :与条形图类似,带有垂直条带而不是水平条带。columnchart : Like barchart, with vertical strips instead of horizontal strips.
  • 饼图 :第一列是颜色轴,第二列是数值。piechart : First column is color-axis, second column is numeric.
  • 时间图 :折线图。timechart : Line graph. 第一列是 x 轴,且应为日期/时间。First column is x-axis, and should be datetime. 第二列是 y 轴。Second column is y-axis.

示例:条形图Example: bar chart

以下查询以条形图呈现最近使用的应用程序:The following query renders the most recently used applications as a bar chart:

CCMRecentlyUsedApplications
| summarize dcount( Device ) by ProductName
| top 10 by dcount_
| render barchart

CMPivot 条形图可视化效果示例

示例:时间图Example: time chart

要呈现时间图,请使用新的 bin() 运算符对某段时间的事件进行分组。To render time charts, use the new bin() operator to group events in time. 以下查询显示过去七天内设备启动的时间:The following query shows when devices have started in the last seven days:

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

CMPivot 时间图可视化效果示例

示例:饼图Example: pie chart

以下查询显示饼图中的所有 OS 版本:The following query displays all OS versions in a pie chart:

OperatingSystem
| summarize count() by Caption
| render piechart

CMPivot 饼图可视化效果示例

硬件清单Hardware inventory

使用 CMPivot 查询任何硬件清单类。Use CMPivot to query any hardware inventory class. 这些类包括对硬件清单所做的任何自定义扩展。These classes include any custom extensions you make to hardware inventory. CMPivot 立即返回存储在站点数据库中的上次硬件清单扫描的缓存结果。CMPivot immediately returns cached results from the last hardware inventory scan stored in the site database. 同时,它会根据需要使用来自任何在线客户端的实时数据更新结果。At the same time, it updates the results if necessary with live data from any online clients.

结果表或图表中数据的颜色饱和度表示数据是实时的还是缓存的。The color saturation of the data in the results table or chart indicates if the data is live or cached. 例如,深蓝色是来自在线客户端的实时数据。For example, dark blue is real-time data from an online client. 浅蓝色是缓存数据。Light blue is cached data.

示例Example

LogicalDisk
| summarize sum( FreeSpace ) by Device
| order by sum_ desc
| render columnchart

带柱形图可视化效果的 CMPivot 清单查询示例

限制Limitations

  • 以下硬件清单实体不受支持:The following hardware inventory entities aren't supported:
    • 数组属性,例如 IP 地址Array properties, for example IP address
    • Real32/Real64Real32/Real64
    • 嵌入的对象属性Embedded object properties
  • 清单实体名称必须以字符开头Inventory entity names must begin with a character
  • 不能通过创建具有相同名称的清单实体覆盖内置实体You can't overwrite the built-in entities by creating an inventory entity of the same name

标量运算符Scalar operators

CMPivot 包括以下标量运算符:CMPivot includes the following scalar operators:

备注

  • LHS:运算符左侧的字符串LHS: string to the left of the operator
  • RHS:运算符右侧的字符串RHS: string to the right of the operator
运算符Operator 说明Description 示例(生成 true)Example (yields true)
== 等于Equals "aBc" == "aBc"
!=!= 不等于Not equals "abc" != "ABC"
likelike LHS 包含 RHS 的匹配项LHS contains a match for RHS "FabriKam" like "%Brik%"
!like!like LHS 不包含 RHS 的匹配项LHS doesn't contain a match for RHS "Fabrikam" !like "%xyz%"
包含contains RHS 以 LHS 子序列的形式存在RHS occurs as a subsequence of LHS "FabriKam" contains "BRik"
!contains!contains LHS 中未出现 RHSRHS doesn't occur in LHS "Fabrikam" !contains "xyz"
startswithstartswith RHS 是 LHS 的初始子序列RHS is an initial subsequence of LHS "Fabrikam" startswith "fab"
!startswith!startswith RHS 不是 LHS 的初始子序列RHS isn't an initial subsequence of LHS "Fabrikam" !startswith "kam"
endswithendswith RHS 是 LHS 的闭合子序列RHS is a closing subsequence of LHS "Fabrikam" endswith "Kam"
!endswith!endswith RHS 不是 LHS 的闭合子序列RHS isn't a closing subsequence of LHS "Fabrikam" !endswith "brik"

查询摘要Query summary

选择 CMPivot 窗口底部的“查询摘要”选项卡。Select the Query Summary tab at the bottom of the CMPivot window. 此状态可帮助你识别离线的客户端,或排查可能发生的故障。This status helps you identify clients that are offline, or troubleshoot errors that may occur. 在“计数”列中选择一个值以打开具有该状态的特定设备的列表。Select a value in the Count column to open a list of specific devices with that status.

例如,选择状态为“故障”的设备的计数。For example, select the count of devices with a Failure status. 请查看特定的错误消息,并导出这些设备列表。See the specific error message, and export a list of these devices. 如果错误是无法识别的特定 cmdlet,请使用导出的设备列表创建集合以部署 Windows PowerShell 更新。If the error is that a specific cmdlet isn't recognized, create a collection from the exported device list to deploy a Windows PowerShell update.

CMPivot 审核状态消息CMPivot audit status messages

从版本 1810 开始,运行 CMPivot 时,MessageID 40805 会创建审核状态消息。Starting in version 1810, when you run CMPivot, an audit status message is created with MessageID 40805. 可通过转到“监视” > “系统状态” > “状态消息查询” 查看状态消息。You can view the status messages by going to Monitoring > System Status > Status Message Queries. 可为指定用户运行所有审核状态消息,为指定站点运行所有审核状态消息,或创建自己的状态消息查询 。You can run All Audit status Messages for a Specific User , All Audit status Messages for a Specific Site , or create your own status message query.

消息使用以下格式:The following format is used for the message:

MessageId 40805:User <UserName> ran script <Script-Guid> with hash <Script-Hash> on collection <Collection-ID>。MessageId 40805: User <UserName> ran script <Script-Guid> with hash <Script-Hash> on collection <Collection-ID>.

  • 7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14 是 CMPivot 的 Script-Guid。7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14 is the Script-Guid for CMPivot.
  • 可以在客户端的 scripts.log 文件中查看 Script-Hash。The Script-Hash can be seen in the client's scripts.log file.
  • 也可以查看存储在客户端脚本存储中的哈希。You can also see the hash stored in the client's script store. 客户端上的文件名为 <Script-Guid><Script-Hash>。The filename on the client is <Script-Guid><Script-Hash>.
    • 示例文件名:C:\Windows\CCM\ScriptStore\7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14_abc1d23e45678901fabc123d456ce789fa1b2cd3e456789123fab4c56789d0123.psExample file name: C:\Windows\CCM\ScriptStore\7DC6B6F1-E7F6-43C1-96E0-E1D16BC25C14_abc1d23e45678901fabc123d456ce789fa1b2cd3e456789123fab4c56789d0123.ps

CMPivot 审核状态消息示例

后续步骤Next steps

CMPivot 疑难解答Troubleshooting CMPivot