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

2023.01.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

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

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

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

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

前提

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

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

img

CREATE DATABASE IF NOT EXISTS ssm_inventory

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

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

img

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

img

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

img

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

img

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

img

そのほか(兼 おわりに)

以上、いくつかよく使いそうなインベントリデータの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

参考