Bagaimana cara menggunakan pernyataan SQL untuk menanyakan event ActionTrail yang dikirimkan ke Simple Log Service? - ActionTrail

ActionTrail membantu Anda memantau operasi dalam akun Alibaba Cloud dan mencatat event yang dihasilkan dalam 90 hari terakhir. Untuk menganalisis event yang lebih lama dari 90 hari, Anda dapat membuat jejak di konsol ActionTrail dan mengirimkan event tersebut ke Simple Log Service Logstore yang ditentukan. Setelah itu, Anda dapat menggunakan pernyataan SQL untuk meminta dan menganalisis event yang telah dikirim. Topik ini menjelaskan cara menulis pernyataan SQL untuk meminta event di Simple Log Service.

Sintaksis pernyataan SQL

Pernyataan SQL berada dalam format <Pernyataan pencarian> | <Pernyataan analitik>.

ActionTrail memungkinkan Anda menggunakan pernyataan SQL untuk meminta event dalam berbagai skenario. Tabel berikut menjelaskan pernyataan pencarian dan pernyataan analitik yang dapat digunakan untuk meminta event dalam berbagai situasi:

Skenario	Contoh pernyataan pencarian	Contoh pernyataan analitik
Kueri event	Kueri event berdasarkan tipe baca/tulis: `* AND "event.eventCategory": Management AND "event.eventRW": Write` Kueri event berdasarkan nama pengguna: `* AND "event.eventCategory": Management AND "event.userIdentity.userName": "xxx"` Kueri event berdasarkan nama event: `* AND "event.eventCategory": Management AND "event.eventName": "DescribeScalingGroups"` Kueri event berdasarkan tipe sumber daya: `* AND "event.eventCategory": Management AND "event.resourceType": "ACS::ECS::Instance"` Kueri event berdasarkan nama sumber daya: `* AND "event.eventCategory": Management AND "event.resourceName": "i-xxx"` Kueri event berdasarkan nama layanan Alibaba Cloud: `* AND "event.eventCategory": Management AND "event.serviceName": "Ecs"` Kueri event berdasarkan ID AccessKey: `* AND "event.eventCategory": Management "event.userIdentity.accessKeyId": "STS.xxxx"`	select "event.acsRegion" as acsRegion, "event.apiVersion" as apiVersion, "event.eventId" as eventId, "event.eventName" as eventName, "event.eventRW" as eventRW, "event.eventSource" as eventSource, from_unixtime(__time__) as eventTime, "event.eventType" as eventType, "event.eventVersion" as eventVersion, "event.errorCode" as errorCode, "event.errorMessage" as errorMessage, "event.requestId" as requestId, "event.requestParameterJson" as requestParameterJson, "event.resourceName" as resourceName, "event.resourceType" as resourceType, "event.serviceName" as serviceName, "event.sourceIpAddress" as sourceIpAddress, "event.userAgent" as userAgent, "event.userIdentity.accessKeyId" as accessKeyId, "event.userIdentity.accountId" as accontId, "event.userIdentity.principalId" as principalId, "event.userIdentity.type" as type, "event.userIdentity.userName" as userName
Kueri ringkasan event	Kueri ringkasan event berdasarkan tipe baca/tulis: `* AND "event.eventCategory": Management AND "event.eventRW": Write` Kueri ringkasan event berdasarkan nama event: `* AND "event.eventCategory": Management AND "event.eventName": "DescribeScalingGroups"` Kueri ringkasan event berdasarkan nama layanan Alibaba Cloud: `* AND "event.eventCategory": Management AND "event.serviceName": "Ecs"` Kueri ringkasan event berdasarkan ID AccessKey: `* AND "event.eventCategory": Management "event.userIdentity.accessKeyId": "STS.xxxx"`	SELECT"event.serviceName"AS servieName,"event.eventName"AS eventName,"event.eventRw"AS eventRw,"event.sourceIpAddress"AS sourceIpAddress,"event.resourceName"AS resourceName,"event.resourceType"AS resourceType,"event.userIdentity.userName"AS userName,"event.userIdentity.type"AS userType,"event.userIdentity.accessKeyId"AS accessKeyId,"event.acsRegion"AS eventRegion,COUNT("event.eventId")AS n, date_trunc('hour', __time__) AS time GROUP BY time, servieName, eventName, eventRw, sourceIpAddress, resourceType, resourceName, accessKeyId, userType, userName, eventRegion ORDER BY time DESC LIMIT 20
Kueri event wawasan	Kueri event wawasan berdasarkan alamat IP tidak biasa: `* AND "event.eventCategory": Insight AND event.insightDetails.insightType: IpInsight AND "event.insightDetails.sourceIpAddress": "10.12.XX.XX"` Kueri event wawasan berdasarkan tipe event: `* AND "event.eventCategory": Insight AND event.insightDetails.insightType: IpInsight` Kueri event wawasan berdasarkan ID event: `* AND "event.eventCategory": Insight AND event.insightDetails.insightType: IpInsight AND "event.eventId": 6CE5DBDE-5D18-4BF9-BD6A-E0D2E1BA****`	`select from_unixtime(__time__) as eventTime, "event.acsRegion" as eventRegion, "event.insightDetails.sourceIpAddress" as sourceIpAddress, "event.insightDetails.insightContext.statistics.insightCount" as count`

Contoh pernyataan SQL

Contoh 1: Kueri semua event manajemen dengan tipe tulis
* AND "event.eventCategory": Management AND "event.eventRW": Write | select "event.acsRegion" as acsRegion, "event.apiVersion" as apiVersion, "event.eventId" as eventId, "event.eventName" as eventName, "event.eventRW" as eventRW, "event.eventSource" as eventSource, from_unixtime(__time__) as eventTime, "event.eventType" as eventType, "event.eventVersion" as eventVersion, "event.errorCode" as errorCode, "event.errorMessage" as errorMessage, "event.requestId" as requestId, "event.requestParameterJson" as requestParameterJson, "event.resourceName" as resourceName, "event.resourceType" as resourceType, "event.serviceName" as serviceName, "event.sourceIpAddress" as sourceIpAddress, "event.userAgent" as userAgent, "event.userIdentity.accessKeyId" as accessKeyId, "event.userIdentity.accountId" as accontId, "event.userIdentity.principalId" as principalId, "event.userIdentity.type" as type, "event.userIdentity.userName" as userName
Contoh 2: Kueri ringkasan semua event manajemen dengan tipe tulis
Catatan
Jika Anda menentukan rentang waktu kueri yang panjang, disarankan untuk menggunakan klausa LIMIT N guna membatasi jumlah event yang dikembalikan menjadi N. Sebagai contoh, jika Anda menggunakan klausa LIMIT 20, sistem akan mengembalikan 20 event.
* AND "event.eventCategory": Management AND "event.eventRW": Write | SELECT "event.serviceName" AS servieName, "event.eventName" AS eventName, "event.eventRw" AS eventRw, "event.sourceIpAddress" AS sourceIpAddress, "event.resourceName" AS resourceName, "event.resourceType" AS resourceType, "event.userIdentity.userName" AS userName, "event.userIdentity.type" AS userType, "event.userIdentity.accessKeyId" AS accessKeyId, "event.acsRegion" AS eventRegion, COUNT("event.eventId") AS n, date_trunc('hour', __time__) AS time GROUP BY time, servieName, eventName, eventRw, sourceIpAddress, resourceType, resourceName, accessKeyId, userType, userName, eventRegion ORDER BY time DESC LIMIT 20
Contoh 3: Kueri semua event wawasan dengan tipe IPInsight
* AND "event.eventCategory": Insight AND event.insightDetails.insightType: IpInsight | select from_unixtime(__time__) as eventTime, "event.acsRegion" as eventRegion, "event.insightDetails.sourceIpAddress" as sourceIpAddress, "event.insightDetails.insightContext.statistics.insightCount" as count