SSMインベントリデータのクエリで使えるAthenaテーブルを色々作成する

川原征大

2023.01.16

Systems Manager(SSM)インベントリはSSM管理下のインスタンスの棚卸しに役立つ機能です。インスタンスからメタデータを収集して、それらの情報をコンソールから確認できます。

また、リソースデータの同期機能を使って、収集したメタデータ(インベントリデータ)を S3バケットへ送信できます。 S3バケットのインベントリデータに対して Amazon AthenaやQuickSight を使って分析ができます。

例えば以下の記事では、Organizations環境のSSMインベントリデータを 1つのS3バケットへ集約しています。

AWS Organizations環境でなるべく楽にSSMインベントリデータを集約したい | DevelopersIO

今回はS3バケットのインベントリデータをAmazon Athenaで分析するための下準備(兼私の備忘録)として、それぞれのデータタイプで使えるテーブルを作成してみます。

前提

Athenaの初期セットアップ周りの詳細は割愛します。こちらの『ステップ 1: データベースを作成する』を参考ください。

今回は ssm_inventory データベースを作成して、その配下にテーブルを作成していきます。

CREATE DATABASE IF NOT EXISTS ssm_inventory

また、SSMインベントリで取得されるメタデータについては以下の公式情報を参考ください。

インベントリによって収集されたメタデータ - AWS Systems Manager

AWS:InstanceInformation

以下クエリを流してテーブルを作成します。

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_instanceinformation`(
  `agentstatus` string,
  `agenttype` string,
  `agentversion` string,
  `computername` string,
  `instanceid` string,
  `ipaddress` string,
  `platformtype` string,
  `platformname` string,
  `platformversion` string,
  `instancestatus` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:InstanceInformation/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

以下サンプルクエリです。

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_instanceinformation
SELECT
  resourceid,
  instancestatus,
  accountid,
  region
FROM aws_instanceinformation limit 10

AWS:Application

以下クエリを流してテーブルを作成します。

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_application`(
  `applicationtype` string,
  `installedtime` string,
  `architecture` string,
  `version` string,
  `summary` string,
  `packageid` string,
  `publisher` string,
  `release` string,
  `url` string,
  `name` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string,
  `epoch` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:Application/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

以下サンプルクエリです。

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_application
SELECT
  resourceid,
  name,
  version,
  accountid,
  region
FROM aws_application limit 10

AWS:ComplianceItem

以下クエリを流してテーブルを作成します。

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_complianceitem`(
  `status` string,
  `installedtime` string,
  `executiontype` string,
  `patchseverity` string,
  `title` string,
  `severity` string,
  `executiontime` string,
  `compliancetype` string,
  `classification` string,
  `id` string,
  `documentversion` string,
  `patchstate` string,
  `patchbaselineid` string,
  `documentname` string,
  `patchgroup` string,
  `executionid` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string,
  `cveids` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:ComplianceItem/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

以下サンプルクエリです。

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_complianceitem
SELECT
  resourceid,
  title,
  documentname,
  status,
  severity,
  accountid,
  region
FROM aws_complianceitem limit 10

AWS:Network

以下クエリを流してテーブルを作成します。

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_network`(
  `macaddress` string,
  `ipv6` string,
  `ipv4` string,
  `name` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string,
  `gateway` string,
  `dnsserver` string,
  `dhcpserver` string,
  `subnetmask` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:Network/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

以下サンプルクエリです。

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_network
SELECT
  resourceid,
  macaddress,
  name,
  ipv4,
  ipv6,
  accountid,
  region
FROM aws_network limit 10

AWS:Tag

以下クエリを流してテーブルを作成します。

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_tag`(
  `key` string,
  `value` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:Tag/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

以下サンプルクエリです。

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_tag
SELECT
  resourceid,
  key,
  value,
  accountid,
  region
FROM aws_tag limit 10

そのほか(兼おわりに)

以上、いくつかよく使いそうなインベントリデータのAthenaテーブルを作成してみました。

これ以降でそのほかインベントリデータのテーブル作成およびプレビューのSQLをまとめて記載します。

参考になれば幸いです。

AWS:AWSComponent

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_awscomponent`(
  `applicationtype` string,
  `installedtime` string,
  `architecture` string,
  `version` string,
  `summary` string,
  `packageid` string,
  `publisher` string,
  `release` string,
  `url` string,
  `name` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:AWSComponent/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_awscomponent
SELECT * FROM aws_awscomponent limit 10

AWS:BillingInfo

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_billinginfo`(
  `billingproductid` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:BillingInfo/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_billinginfo
SELECT * FROM aws_billinginfo limit 10

AWS:ComplianceSummary

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_compliancesummary`(
  `compliancetype` string,
  `patchgroup` string,
  `patchbaselineid` string,
  `status` string,
  `overallseverity` string,
  `executiontime` string,
  `executionid` string,
  `executiontype` string,
  `compliantcriticalcount` string,
  `complianthighcount` string,
  `compliantmediumcount` string,
  `compliantlowcount` string,
  `compliantinformationalcount` string,
  `compliantunspecifiedcount` string,
  `noncompliantcriticalcount` string,
  `noncomplianthighcount` string,
  `noncompliantmediumcount` string,
  `noncompliantlowcount` string,
  `noncompliantinformationalcount` string,
  `noncompliantunspecifiedcount` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:ComplianceSummary/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_compliancesummary
SELECT * FROM aws_compliancesummary limit 10

AWS:InstanceDetailedInformation

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_instancedetailedinformation`(
  `cpus` string,
  `osservicepack` string,
  `cpuhyperthreadenabled` string,
  `cpuspeedmhz` string,
  `cpusockets` string,
  `cpucores` string,
  `cpumodel` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string,
  `kernelversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:InstanceDetailedInformation/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_instancedetailedinformation
SELECT * FROM aws_instancedetailedinformation limit 10

AWS:PatchSummary

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_patchsummary`(
  `baselineid` string,
  `missingcount` string,
  `lastnorebootinstalloperationtime` string,
  `installedothercount` string,
  `operationendtime` string,
  `othernoncompliantcount` string,
  `criticalnoncompliantcount` string,
  `installedpendingrebootcount` string,
  `noncompliantseverity` string,
  `snapshotid` string,
  `securitynoncompliantcount` string,
  `notapplicablecount` string,
  `operationstarttime` string,
  `rebootoption` string,
  `failedcount` string,
  `operationtype` string,
  `installedcount` string,
  `executionid` string,
  `installedrejectedcount` string,
  `patchgroup` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string,
  `ownerinformation` string,
  `unreportednotapplicablecount` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:PatchSummary/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_patchsummary
SELECT * FROM aws_patchsummary limit 10

AWS:ResourceGroup

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_resourcegroup`(
  `arn` string,
  `name` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:ResourceGroup/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_resourcegroup
SELECT * FROM aws_resourcegroup limit 10

AWS:Service

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_service`(
  `status` string,
  `servicetype` string,
  `servicesdependedon` string,
  `displayname` string,
  `dependentservices` string,
  `starttype` string,
  `name` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:Service/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_service
SELECT * FROM aws_service limit 10

AWS:WindowsUpdate

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_windowsupdate`(
  `installedtime` string,
  `description` string,
  `hotfixid` string,
  `installedby` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:WindowsUpdate/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_windowsupdate
SELECT * FROM aws_windowsupdate limit 10

AWS:WindowsRole

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_windowsrole`(
  `path` string,
  `featuretype` string,
  `dependson` string,
  `description` string,
  `parent` string,
  `installed` string,
  `displayname` string,
  `installedstate` string,
  `subfeatures` string,
  `name` string,
  `servercomponentdescriptor` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:WindowsRole/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_windowsrole
SELECT * FROM aws_windowsrole limit 10

SSMインベントリデータのクエリで使えるAthenaテーブルを色々作成する

前提

AWS:InstanceInformation

AWS:Application

AWS:ComplianceItem

AWS:Network

AWS:Tag

そのほか(兼おわりに)

AWS:AWSComponent

AWS:BillingInfo

AWS:ComplianceSummary

AWS:InstanceDetailedInformation

AWS:PatchSummary

AWS:ResourceGroup

AWS:Service

AWS:WindowsUpdate

AWS:WindowsRole

参考

イベント

EVENT【4/28（金）リモート】クラスメソッドの会社説明会を開催します

EVENT【4/6（木）】サーバーレス環境をより高速化するMomentoのご紹介〜キャッシュの最適化、拡張、管理をSaaSで自動化する〜

EVENT【4/26（水）リモート】クラスメソッドの会社説明会〜フリーランスエンジニア編〜を開催します

EVENT【4/20（木）リモート】クラスメソッドの24卒向け会社説明会〜最新技術で爆速成長！新卒から活躍する秘訣〜

EVENT【4/12（水）】Alteryx Designer Cloudはじめの一歩

EVENT【4/19（水）リモート】第二新卒・若手エンジニア向け会社説明会～クラスメソッドで踏み出すクラウドエンジニアのキャリア～

SSMインベントリデータのクエリで使えるAthenaテーブルを色々作成する

前提

AWS:InstanceInformation

AWS:Application

AWS:ComplianceItem

AWS:Network

AWS:Tag

そのほか(兼 おわりに)

AWS:AWSComponent

AWS:BillingInfo

AWS:ComplianceSummary

AWS:InstanceDetailedInformation

AWS:PatchSummary

AWS:ResourceGroup

AWS:Service

AWS:WindowsUpdate

AWS:WindowsRole

参考

イベント

EVENT【4/28（金）リモート】クラスメソッドの会社説明会を開催します

EVENT【4/6（木）】サーバーレス環境をより高速化するMomentoのご紹介〜キャッシュの最適化、拡張、管理をSaaSで自動化する〜

EVENT【4/26（水）リモート】クラスメソッドの会社説明会〜フリーランスエンジニア編〜を開催します

EVENT【4/20（木）リモート】クラスメソッドの24卒向け会社説明会 〜最新技術で爆速成長！新卒から活躍する秘訣〜

EVENT【4/12（水）】Alteryx Designer Cloudはじめの一歩

EVENT【4/19（水）リモート】第二新卒・若手エンジニア向け会社説明会 ～クラスメソッドで踏み出すクラウドエンジニアのキャリア～

関連記事

AWS Organizations環境でなるべく楽にSSMインベントリデータを集約したい

Windows 用 CloudWatch エージェントのバージョンを一括で取得したい

Systems Manager インベントリの実行履歴が S3 バケットに保存されない場合の対処方法

CloudWatch Agentのバージョン取得を自動化したいのでいろいろ試してみた

そのほか(兼おわりに)

EVENT【4/20（木）リモート】クラスメソッドの24卒向け会社説明会〜最新技術で爆速成長！新卒から活躍する秘訣〜

EVENT【4/19（水）リモート】第二新卒・若手エンジニア向け会社説明会～クラスメソッドで踏み出すクラウドエンジニアのキャリア～