多拠点にWindows更新プログラムを展開していると、「コンソールの既定レポートでは見えるけれど、最終適用状態ごとに端末をExcelで一覧化して分析したい」「特定端末にインストール済みのKBをすぐSQLで棚卸ししたい」というニーズが必ず出てきます。本記事では、System Center Configuration Manager（SCCM/ConfigMgr）のソフトウェア更新デプロイメントから、PrePilot／Pilot／Productionごとに最終適用状態別の端末一覧をSQLで抽出し、あわせて端末別のインストール済みパッチ一覧を取得する実践手順を詳しく解説します。
ConfigMgrの「最終適用状態」をSQLで引き出す狙い
ソフトウェア更新の既定レポート：
Software Updates – C Deployment States > States 1 – Enforcement states for a deployment
は、デプロイメントごとの「最終適用状態（Last Enforcement State）」を確認するのに便利ですが、次のような場面では少し物足りません。
- PrePilot／Pilot／Productionを横並びで比較したい
- 状態別に端末一覧をExcelに出して、フィルタやピボットで深掘りしたい
- 「Failed」だけ抽出し、担当者ごとにチケットを切りたい
このような分析を柔軟に行うには、SQLで生データを直接引き出すのが最も効率的です。ConfigMgrでは、ソフトウェア更新デプロイメントの状態は v_CIAssignment / v_AssignmentState_Combined / v_StateNames / v_R_System に格納されており、これらを結合することで「最終適用状態ごとの端末一覧」をそのまま取得できます。
よく使う「最終適用状態」と意味の整理
本記事で主に対象とする代表的な最終適用状態（StateName）は次のとおりです。
| StateName（英語） | 管理者向け日本語イメージ | 典型的な状況 | 対応優先度 |
|---|---|---|---|
| Failed to install update(s) | インストール失敗 | インストール処理は走ったが、エラーで終了 | 最優先 |
| Pending system restart | 再起動待ち | 更新自体は入ったが、再起動が未実施 | 高 |
| Installing update(s) | インストール中 | インストール処理が実行中（長時間継続時は要調査） | 中 |
| Downloading update(s) | ダウンロード中 | 配布ポイントやネットワーク経由でコンテンツ取得中 | 中 |
| Downloaded update(s) | ダウンロード済み | ダウンロード完了、適用待ち（メンテナンスウィンドウ等） | 中 |
| Enforcement state unknown | 不明 | クライアントから状態が上がってきていない／古い | 状況により高 |
実際のトラブルシュートでは、上記の状態を一覧で出したうえで、Failed > Pending restart > Downloading / Downloaded > Unknown の順に確認していくと効率的です。
デプロイメントID単位で最終適用状態を取得する基本SQL
まずは、1つのデプロイメント（AssignmentID）に対して、最終適用状態ごとに端末一覧を出すSQLです。'xxxxxxxx' の部分を対象の AssignmentID に置き換えて実行します。
SELECT
sn.StateName AS LastEnforcementState, -- 最終適用状態（英語表記）
vrs.Name0 AS ComputerName, -- 端末名
a.AssignmentName AS DeploymentName, -- デプロイ名
assc.StateTime AS StateTime, -- 状態が記録された日時
a.CollectionName AS CollectionName -- 対象コレクション
FROM v_CIAssignment a
JOIN v_AssignmentState_Combined assc
ON a.AssignmentID = assc.AssignmentID
JOIN v_StateNames sn
ON assc.StateType = sn.TopicType
AND sn.StateID = ISNULL(assc.StateID, 0)
JOIN v_R_System vrs
ON vrs.ResourceID = assc.ResourceID
WHERE a.AssignmentID = 'xxxxxxxx' -- ←デプロイメントID
ORDER BY LastEnforcementState, ComputerName;
これを実行すると、指定したデプロイメントに属する全端末について、最終適用状態・端末名・コレクション名・状態時刻が一覧で取得できます。レポート画面の「States 1 – Enforcement states for a deployment」を、そのままExcelに落とせる形でSQL化したイメージです。
使用テーブルの役割を整理
| ビュー名 | 役割 | 本SQLでの使いどころ |
|---|---|---|
v_CIAssignment | デプロイメント情報（AssignmentID、AssignmentName、CollectionNameなど） | どのデプロイメントを対象にするかを決める起点 |
v_AssignmentState_Combined | クライアントごとのデプロイメント状態 | 各端末の最終適用状態・状態時刻を取得 |
v_StateNames | 状態コードと表示名の対応表 | StateID から英語の状態名（StateName）を解決 |
v_R_System | クライアント（端末）情報 | 端末名（Name0）を取得し、人間に分かる形にする |
AssignmentID（デプロイメントID）の探し方
上記SQLを実行するには、対象デプロイメントの AssignmentID を知る必要があります。デプロイ名に「PrePilot」「Pilot」「Production」といった識別子を含めている場合は、次のようなSQLでまとめて取得できます。
SELECT
AssignmentID,
AssignmentName,
CollectionName
FROM v_CIAssignment
WHERE AssignmentName LIKE '%PrePilot%' -- 例：名前の一部で検索
OR AssignmentName LIKE '%Pilot%'
OR AssignmentName LIKE '%Production%';
ここで取得した AssignmentID をメモしておき、先ほどのメインSQLの WHERE a.AssignmentID = 'xxxxxxxx' に差し替えて使います。
PrePilot／Pilot／Productionを一括で出力するSQL
PrePilot／Pilot／Production それぞれに別デプロイメントを作っている場合でも、IDさえ分かっていれば1本のSQLでまとめて一覧を出せます。
SELECT
sn.StateName AS LastEnforcementState,
vrs.Name0 AS ComputerName,
a.AssignmentName AS DeploymentName,
assc.StateTime AS StateTime,
a.CollectionName AS CollectionName
FROM v_CIAssignment a
JOIN v_AssignmentState_Combined assc
ON a.AssignmentID = assc.AssignmentID
JOIN v_StateNames sn
ON assc.StateType = sn.TopicType
AND sn.StateID = ISNULL(assc.StateID, 0)
JOIN v_R_System vrs
ON vrs.ResourceID = assc.ResourceID
WHERE a.AssignmentID IN ('<PrePilotのID>', '<PilotのID>', '<ProductionのID>')
ORDER BY DeploymentName, LastEnforcementState, ComputerName;
この形にしておくと、Excel側でピボットテーブルを使い、
- 行：コレクション／端末名
- 列：LastEnforcementState
- 値：端末数
といったクロス集計を簡単に作れるため、フェーズごとの進捗比較が非常にやりやすくなります。
画面の見た目と同じ状態だけに絞るフィルタ
トラブルシュート対象になりやすい状態だけに絞りたい場合は、WHERE に sn.StateName IN (...) を追加します。
AND sn.StateName IN (
'Enforcement state unknown',
'Installing update(s)',
'Downloading update(s)',
'Downloaded update(s)',
'Pending system restart',
'Failed to install update(s)'
)
StateNameは英語表記で保持されているため、フィルタ条件も英語で指定したほうが確実です。
結果をExcel/CSVに出力する
SQLの実行結果は、SSMS（SQL Server Management Studio）から次の手順で簡単にExcel/CSV化できます。
- クエリを実行して、結果を「結果をグリッド」で表示
- グリッド上で右クリック
- 「結果を保存」または「結果をファイルに保存」を選択
- CSV形式で保存し、Excelで開く
毎回同じクエリを実行する場合は、クエリを .sql として保存しておくか、ConfigMgrのカスタムレポートに組み込んでおくとさらに便利です。
最終適用状態から始めるトラブルシュート優先度
抽出した一覧をもとに、どこから手を付けるかの目安を整理しておきます。
| LastEnforcementState | 初動で見るべきログ | チェックポイント | 優先度 |
|---|---|---|---|
| Failed to install update(s) | UpdatesDeployment.log WUAHandler.log AppEnforce.log（アプリ型更新時） | エラーコード（0x8007…, 0x8024… など）の特定 前提条件（別KBや.NETバージョン）の不足 ディスク空き容量・アンチウイルス干渉など | 最優先 |
| Pending system restart | RebootCoordinator.log UpdatesDeployment.log | 再起動を抑制するGPOやメンテナンスウィンドウの有無 サーバーの場合、運用チームとの再起動調整が必要か | 高 |
| Downloading update(s) / Downloaded update(s) | ContentTransferManager.log DataTransferService.log CAS.log | 配布ポイントへのコンテンツ配布状況（100％か） 境界／境界グループ設定は正しいか BITSや帯域制御で極端に遅くなっていないか | 中 |
| Enforcement state unknown | ClientIDManagerStartup.log WUAHandler.log ScanAgent.log | クライアントがオンラインか（電源オフ／退役端末ではないか） ハートビートディスカバリ・ポリシー受信に問題がないか クライアントの再インストール／修復が必要か | 環境により高 |
最終適用状態ごとに端末一覧を出せれば、運用チーム内で担当を分けて対応したり、「今週は Failed をゼロにする」など明確な目標を立てやすくなります。
（補足）特定端末にインストール済みのパッチ一覧を取得する
コメントや現場からよく上がるのが、「あるPCにどのKBが入っているかを一気に見たい」「ConfigMgrで管理されていない手動インストール分も含めて棚卸ししたい」といった要望です。この場合は、次の2つの視点を使い分けると便利です。
| 視点 | 取得元 | 特徴 | 向いている用途 |
|---|---|---|---|
| A. SCCMの更新準拠ビュー | v_Update_ComplianceStatusAll + v_UpdateInfo | ConfigMgrに同期されている更新のみ対象 Status = 3 が「Installed」 Software Update Groupとのひも付けがしやすい | 監査・コンプライアンス、更新ポリシーの評価 |
| B. OSのQFEインベントリ | v_GS_QUICK_FIX_ENGINEERING | OSが把握しているホットフィックス情報（WMI） SCCM外で入った更新も拾える 累積更新が1件として表示される等、粒度は異なる | 実機の状態確認、トラブル時のKB棚卸し |
A. SCCMの「更新準拠」ビューから取得するSQL
ConfigMgrが管理している更新に限定して、「このPCにはどの更新が Status = Installed になっているか」を一覧化するSQLです。
SELECT
vrs.Name0 AS ComputerName,
ui.ArticleID AS KB, -- KB番号（例：KB5030211）
ui.Title AS UpdateTitle, -- 更新タイトル
ui.DatePosted AS DatePosted, -- 公開日
ucs.LastStatusChangeTime AS LastChangeTime -- 状態がInstalledになった最終時刻
FROM v_UpdateInfo ui
JOIN v_Update_ComplianceStatusAll ucs
ON ui.CI_ID = ucs.CI_ID
JOIN v_R_System vrs
ON vrs.ResourceID = ucs.ResourceID
WHERE vrs.Name0 = 'PC-NAME' -- ←対象端末名
AND ucs.Status = 3 -- 3 = Installed
ORDER BY ui.DatePosted DESC, ui.ArticleID;
ポイントは、ucs.Status = 3 が「インストール済み」を意味することです。特定KBだけ確認したい場合は、以下のように絞り込みます。
AND ui.ArticleID = '5030211' -- 「KB5030211」の場合
多くの環境では、ArticleIDには「KB」を除いた数値部分だけが入っているため、KB は付けずに指定します。
B. OS（WMI）のQFEインベントリから取得するSQL
OSが把握しているホットフィックス情報（Win32_QuickFixEngineering）をもとに、実際にインストールされているKB一覧を取得するSQLです。ConfigMgr外で手動インストールされたパッチや、他システム経由で適用された更新も含めて確認できます。
SELECT
vrs.Name0 AS ComputerName,
qfe.HotFixID00 AS KB, -- 例：KB5030211
qfe.InstalledOn0 AS InstalledOn, -- インストール日
qfe.Caption0 AS Caption,
qfe.Description0 AS Description
FROM v_R_System vrs
JOIN v_GS_QUICK_FIX_ENGINEERING qfe
ON vrs.ResourceID = qfe.ResourceID
WHERE vrs.Name0 = 'PC-NAME' -- ←対象端末名
ORDER BY qfe.InstalledOn0 DESC, qfe.HotFixID00;
環境によっては、列名の末尾が 0 / 00 で異なることがあります。その場合は、実際のビュー定義に合わせて HotFixID0 や HotFixID00 などに読み替えてください。
また、累積更新（Cumulative Update）は1件のホットフィックスとして表示されるため、個々の脆弱性パッチ単位ではなく、「どの累積更新が入っているか」を見る用途に向いています。
実務での使い分けパターン
- 監査・コンプライアンスレポート
「このソフトウェア更新グループに含まれる更新は、SCCM経由で端末Aに適用済みか？」といった問いには、A（更新準拠ビュー）を使うのが自然です。 - トラブル時の実機KB棚卸し
「ブルースクリーンが出るようになったので、最近のKBを全部洗い出したい」「別チームが手動でパッチを入れているかもしれない」という場面では、B（QFEインベントリ）も併用すると漏れを防げます。 - SCCMの結果と実機の食い違い調査
ConfigMgr上では Installed になっているのに、実機にはKBが見当たらない／その逆といった場合、AとBの差分を出すことで原因の切り分けがしやすくなります。
最終適用状態SQLとパッチ一覧SQLを組み合わせるアイデア
ここまで紹介したSQLは、それぞれ独立して利用しても十分有用ですが、組み合わせることでより高度な分析が可能になります。例えば次のようなイメージです。
- 「Failed」の端末だけを対象に、最近インストールされたKBを棚卸しする
1. 最終適用状態SQLでLastEnforcementState = 'Failed to install update(s)'の端末一覧を抽出
2. その端末名をもとに、QFEインベントリSQL（B）で最近のKBを確認
3. あるKBを境に失敗が増えていないか、傾向を確認 - 「Pending restart」が長期間続いているサーバーの洗い出し
1. 最終適用状態SQLでLastEnforcementState = 'Pending system restart'の端末一覧＋StateTimeを取得
2.StateTimeが古い順に並べ替え、「一定日数以上再起動されていないサーバー」をリストアップ
3. 必要に応じて運用チームと再起動調整
このように、ConfigMgrのデータベースから状態を直接引き出せるようにしておくと、「画面を眺める」運用から「データドリブンな運用」へと一歩進めることができます。
パフォーマンスと運用面でのちょっとしたTIPS
最後に、実運用でよくある注意点やTIPSをいくつかまとめておきます。
| ポイント | 内容 |
|---|---|
| 本番DBへの負荷 | 大規模環境で全デプロイメント・全端末を一気に集計すると負荷が高くなります。可能な限り、AssignmentIDやコレクション単位で絞ることをおすすめします。 |
| 時間帯の配慮 | レポートや分析用の重いクエリは、できるだけ業務時間外やピークを外した時間帯に実行すると安心です。 |
| クエリの再利用 | よく使うクエリは .sql ファイルとして共有フォルダーに保管したり、ConfigMgrのカスタムレポートに登録しておくと、属人化を防げます。 |
列末尾の 0 / 00 | インベントリクラス拡張などの影響で、列名の末尾が環境によって異なることがあります。クエリがエラーになった場合は、SSMSで対象ビューを右クリックして「上位200行の選択」などを実行し、実際の列名を確認してください。 |
| 英語／日本語混在環境 | StateNameなどは英語固定ですが、端末名・コレクション名・デプロイ名は日本語を含むことがあります。文字コード・フォントの問題で文字化けする場合は、CSV保存時のエンコード（UTF-8 / Shift-JISなど）に注意してください。 |
まとめ
- ConfigMgrのソフトウェア更新デプロイメントで、既定レポート「States 1 – Enforcement states for a deployment」と同等の情報をSQLで取得するには、
v_CIAssignment・v_AssignmentState_Combined・v_StateNames・v_R_Systemの4つを結合するのが基本です。 - PrePilot／Pilot／Production それぞれの AssignmentIDをIN句で指定すれば、フェーズをまたいだ状態比較も1本のクエリで可能になります。
- トラブルシュートでは、Failed > Pending restart > Downloading / Downloaded > Unknown の順に優先度を付け、状態ごとの典型的な原因と確認すべきログを押さえておくと効率的です。
- 特定端末にインストール済みのパッチ一覧を出したい場合は、SCCMの「更新準拠」ビュー（Status=3）と、OSのQFEインベントリという二つの視点を使い分けることで、監査と実機確認の双方に対応できます。
- 本記事のSQLは、環境固有の名前（デプロイ名・コレクション名・端末名）だけ差し替えれば、そのままコピー＆ペーストで利用できます。まずは小さなコレクションやテスト環境で試し、運用に合わせて列の追加・条件の変更などを行ってみてください。
コメント