Skip to main content

Log Analysis with PowerQueries

John Tan
  • Updated

Introduction

A customer inquired about breaking down some complex log events in the Search UI by segment. It quickly became apparent that the customer's use case was more complicated than a search or parsing request, since:

  • One of the fields could potentially exceed 3500 characters
  • There were a number of string formatting requirements that the parser-based rewrites method is not well suited for
  • The resultant attribute would not be used for searching, graphs, or conditionals
  • The display settings of the Search UI (via the "Display" button) was helpful but didn't have an adequate level of detail

After discussing the customer's requirements in further detail, it became obvious that they needed a report. Enter DataSet PowerQueries.

The 3500+ Character Behemoth

As previously mentioned, one of the display fields could exceed 3500 characters in length. In other words, only the first 3500 characters of an attribute are displayed. The attribute is not modified, and characters past the 3500 mark are still processed. When working with attributes that could surpass this limit, be sure to extract any values of interest with the parser. More information on limits can be found here: https://support.dataset.com/hc/en-us/articles/360047870313-DataSets-limit-for-bytes-per-message-and-attributes-for-an-account

Since the customer was interested in the entire field (regardless of length), we had to devise a creative solution.

Simplified Solution

This example illustrates the high level of functionality you can expect from PowerQueries.

Given the following log events:

{"awsRegion":"us-west-1","eventID":"62b76501-b6fa-4547-93e2-2c011ee5878f","eventName":"AssumeRole","eventSource":"sts.amazonaws.com","eventTime":"2020-09-02T20:10:54Z","eventType":"AwsApiCall","eventVersion":"1.05","recipientAccountId":"526942720160","requestID":"7c144a60-94e6-4f3c-ab2f-72193a0fb3be","requestParameters":{"durationSeconds":1800,"roleArn":"arn:aws:iam::526942720160:role\/aws-service-role\/autoscaling.amazonaws.com\/AWSServiceRoleForAutoScaling","roleSessionName":"AutoScaling"},"resources":[{"ARN":"arn:aws:iam::526942720160:role\/aws-service-role\/autoscaling.amazonaws.com\/AWSServiceRoleForAutoScaling","accountId":"526942720160","type":"AWS::IAM::Role"}],"responseElements":{"assumedRoleUser":{"arn":"arn:aws:sts::526942720160:assumed-role\/AWSServiceRoleForAutoScaling\/AutoScaling","assumedRoleId":"AROAI7CCJHZPKCOC77O7U:AutoScaling"},"credentials":{"accessKeyId":"ASIAXVMB32CQMDGVJFX5","expiration":"Sep 2, 2020 8:40:54 PM","sessionToken":"IQoJb3JpZ2luX2VjEJz\/\/\/\/\/\/\/\/\/\/wEaCXVzLXdlc3QtMSJIMEYCIQDlONLFc4vIxjdPbiKYIeb0nl7rWfAg+rMv5\/\/sF0iPJAIhALpbj95i8Obaoi1G0p6C1KL9z8GkxQq5dLrU+F4Z07WiKuECCJX\/\/\/\/\/\/\/\/\/\/wEQARoMNTI2OTQyNzIwMTYwIgzGU\/2sOBY+t4ZDsWIqtQIeA+s2hxIPvUUImSEDfpzT4NBnHuVxujanoNIfNttizqO0RlYUcV3tZCVpYou2hXuiNSfNzBl5hfyoWl6VTO\/RTYjldzClfVHCyvmFdKJzAePweUFhCqHvOgBzKG5g3lvVr1uNMcLndyJM7BUkOuaUKr3Ra8+UvL1PWWwK\/877A1kAWxHSr7CQVFMJqDBDZ94cHeABqGJow4rVzniVPi6moaApV5wO3ka1+xgWsiLo0\/fKsrCsn7EoyQzm5sr1CT6kXa8y14B8Gsi5kqoeZxoGHE2TAfwanq5xxzTw+zckdr0fdm6x2iwkH\/F6\/NqpbGqL9d\/FX2VtB0VRwMk1+smUwv5TlDZ93J91e3gTnRc2ctr639yEY0A+7iyhVeksIc\/6SfTyD\/VCMzRA7ib3TQ+Yc3kYLncwzvi\/+gU6vgEmJscdDxecDvzDzeLaY5haAMTyLDkVwxRskrPQxAIszJgs47DNlm+Fk2k\/LDZjpm63XLxRzKjyykiXjpT\/6i47Fj+IgOY8S\/sY08Bn8OVL89aocq2+HnB9BWm1Y5s6cSfumUOnE0rkg03iLTU0CQFSxW\/M9L0NJJJLp5ygNAPgSe5aWj7VE\/ual5tsYK0T0Bh65LklxiYXPF8Pp33JgDRW07AQs0zEyM+w6OQRJ7j0H8E2lWmY5G0WcEBtRFz6"}},"sharedEventID":"0284c913-8889-4934-8ad2-3a757d70e816","sourceIPAddress":"autoscaling.amazonaws.com","userAgent":"autoscaling.amazonaws.com","userIdentity":{"invokedBy":"autoscaling.amazonaws.com","type":"AWSService"}}
{"awsRegion":"us-west-1","eventID":"132f17ee-8b8f-42b1-8cbb-15077d38377c","eventName":"AssumeRole","eventSource":"sts.amazonaws.com","eventTime":"2020-09-02T22:29:43Z","eventType":"AwsApiCall","eventVersion":"1.05","recipientAccountId":"526942720160","requestID":"6e1bcad9-fedf-43af-a36e-ba221d824431","requestParameters":{"durationSeconds":1800,"roleArn":"arn:aws:iam::526942720160:role\/aws-service-role\/autoscaling.amazonaws.com\/AWSServiceRoleForAutoScaling","roleSessionName":"AutoScaling"},"resources":[{"ARN":"arn:aws:iam::526942720160:role\/aws-service-role\/autoscaling.amazonaws.com\/AWSServiceRoleForAutoScaling","accountId":"526942720160","type":"AWS::IAM::Role"}],"responseElements":{"assumedRoleUser":{"arn":"arn:aws:sts::526942720160:assumed-role\/AWSServiceRoleForAutoScaling\/AutoScaling","assumedRoleId":"AROAI7CCJHZPKCOC77O7U:AutoScaling"},"credentials":{"accessKeyId":"ASIAXVMB32CQJ2BCEWIP","expiration":"Sep 2, 2020 10:59:43 PM","sessionToken":"IQoJb3JpZ2luX2VjEJ\/\/\/\/\/\/\/\/\/\/\/wEaCXVzLXdlc3QtMSJHMEUCIQCfwCtvrOq\/cfaPaPGzWFzqAv+GFG8vEVtXqsJf0OGBSAIgFiOzOPUAnDH1KbDCy+rSqkzAKnNmkbAEdYuY+qPylIQq4QIImP\/\/\/\/\/\/\/\/\/\/ARABGgw1MjY5NDI3MjAxNjAiDN5ldrBOPtNu1Z0TDiq1Ah9unJmEP\/sMtPIBqNC41Om6SQrnSLXaM98Si3+yIDBgLe\/vJAXPE8cldLQzzjJC4XJ95prnoJFL1\/la51howfl21Ovfv1qaNQ675rIXfJQFJaLfOWRPE8YY0tv94ZQEnT8LhRBZFtGqeKQ9dbInRu+P8FdKEN5iA+BKzjtZ9187VkZSNJSkzFFBVJfyCTVwUV+HgShNA83jMsGnbz9LnCv1wnvLo\/12QIuoyqaFfPLrP2o5iB3hI3gYemnZG1aWJnVZXilvmgKz6zkx2QVEzkpS1JrR6g7BRLrXrEnUo27kYCnc34OGoXxR7iFD6ebKPXvrSERRBIoui+a+zYxavWTlcIHSgCqZQOD8hFRwb1htBhhtGH3b5fygqbsPaiKVFGvWO3rsvh\/Jrx2g\/HDm1fEYztwU4TDXucD6BTq\/Ad7HOyhA9Ksy7iOypLFDPovJl8BFlLL8wYubsNNpQOu4M3znRTZr3vGRpgxXk7QdHACYL8\/REpmNS+AYxUG+GsiONVf3R2M7pMgSUYan2dQeR\/QRbuOc8Of3JJCH8VIUfIBUx8ysApLmbwt8kZzb3FzBLIOeW+drYUd2zfAe85xx42vzRgxZmlYYjTZRwUkQIPznjHSCV7HRogaKFCfzuEmDhSEzLL9d85DV6ENE4vLM96ipwHx0C0d9AGtATw5D"}},"sharedEventID":"ec3f9c95-bf09-4db3-b65b-fc3cb57a78d2","sourceIPAddress":"autoscaling.amazonaws.com","userAgent":"autoscaling.amazonaws.com","userIdentity":{"invokedBy":"autoscaling.amazonaws.com","type":"AWSService"}}
{"awsRegion":"us-west-1","eventID":"6c664e02-d564-4303-bd73-a48ec7f4ffba","eventName":"AssumeRole","eventSource":"sts.amazonaws.com","eventTime":"2020-09-02T22:54:10Z","eventType":"AwsApiCall","eventVersion":"1.05","recipientAccountId":"526942720160","requestID":"cbb9cb39-737f-49bb-a54e-7a88148b0cd1","requestParameters":{"durationSeconds":1800,"roleArn":"arn:aws:iam::526942720160:role\/aws-service-role\/autoscaling.amazonaws.com\/AWSServiceRoleForAutoScaling","roleSessionName":"AutoScaling"},"resources":[{"ARN":"arn:aws:iam::526942720160:role\/aws-service-role\/autoscaling.amazonaws.com\/AWSServiceRoleForAutoScaling","accountId":"526942720160","type":"AWS::IAM::Role"}],"responseElements":{"assumedRoleUser":{"arn":"arn:aws:sts::526942720160:assumed-role\/AWSServiceRoleForAutoScaling\/AutoScaling","assumedRoleId":"AROAI7CCJHZPKCOC77O7U:AutoScaling"},"credentials":{"accessKeyId":"ASIAXVMB32CQCH7NY3CW","expiration":"Sep 2, 2020 11:24:10 PM","sessionToken":"IQoJb3JpZ2luX2VjEJ\/\/\/\/\/\/\/\/\/\/\/wEaCXVzLXdlc3QtMSJHMEUCIBh\/\/S1hkBmnPtumXCw++15r10ediB5518Ona3V79L14AiEA1FpwChd28iMI3fZ53mG0J9g0PoKa\/o2Nj8E9rYPcJ5wq4QIImP\/\/\/\/\/\/\/\/\/\/ARABGgw1MjY5NDI3MjAxNjAiDKCB4J5CUPvNzPw1qyq1AqYf0h7OD+C0Fju6TAU96q7A2UBaKItUsyVkx\/3ey804dWHnSzFmS6e8Nx7zPKsK5NQe1wYtR9V+1jqDSl1R288gSiTzW6+ZNTg2cuVv\/nEx5\/huu7qSTgJX5uyhaILhS7z0TZTu1b4LqM8suwJXyQi7R0PK0YA2SdsES0auu2eeuDTkqgq8FUXvkzPrpo7cVHcMRsctQ3ugfKXxSLMFBSbVgQ9n4\/PWOMRjUvlvDlFXJJeIeVFgi2unlop3OEqfYOmM0u7l8HgQvGtX8EqsjaBOg7Co5yF9RwFjGbLE5NqThE5nLUO20GKIJcvTT5VQRsIjSLn+E+mcrwUUhxK4rM7K8E7rKH2pWDHL+M62TlO9gRpM5+K5xeTZSWZwsBtKQFbc3HAsvbJ7jNMauDz5Es4w53prqzCSxcD6BTq\/AddcxxQlFk8qRvvhi6vqiKO9PLBVhfowF+1rvlU8JnoUzlek6aEsVOxCvwJwW4l21aX9W9x8el9G2\/xnkoI8W8HYJIKyfPODOhLKM4HFt53g4SGEW54XX64NIV2fBaXabI7ttbRZbgmoDcLDjDUT6XMu0gSKWT9Ibnj2ix2B7GpmrqnpooDU3BbS7VECI2R66P97qSfSy+eEYHKWEgixGkXC+9KN\/K1ZrIuKM+gHx9lsM2TCrw2R+ClCNCp2MFhw"}},"sharedEventID":"859a18fc-996f-4fdb-87f3-72b736ca4d09","sourceIPAddress":"autoscaling.amazonaws.com","userAgent":"autoscaling.amazonaws.com","userIdentity":{"invokedBy":"autoscaling.amazonaws.com","type":"AWSService"}

We created this PowerQuery:

serverHost="CloudTrail" eventType="AwsApiCall"
| parse "$sDate$T$sTime$" from eventTime
| let origin=awsRegion + ' .::. ' + eventName + '..:..' + resources
| let searchURL='https://app.scalyr.com/events?filter=%22' + requestID + '%22%20%22' + sDate + 'T' + sTime + '%22&log=' + logfile + '&startTime=' + querystart() + '&endTime=' + queryend() |
| columns timestamp, requestID, origin, searchURL

Here's how it works

  1. Line 1: May look familiar because it's a standard DataSet search query. PowerQueries can be thought of as a layer that applies SQL functions to the results of a search query. Consequently, we recommend making the search query as specific as possible to ensure that only results you wish to process are included.
  2. Line 2: The parse statement allows you to extract values (which can then be incorporated into the PowerQuery) on the fly. In this example, we're extracting the date and time from the eventTime field (which was previously extracted by the parser on ingestion).parse is convenient for performing spot analysis, however, attributes like $sDate$ and $sTime$ can only be used within the PowerQuery where they originated. Parsers should be used whenever possible as they are more efficient and extracted attributes can be used in other searches, PowerQueries, graphs, and alerts.
  3. Line 3: Arrange attributes from the log event in a specific format. If the resources field is over 3500 characters, the search URL generated in step 4 will enable an in-depth evaluation of the original log line
  4. Line 4: Create a DataSet search URL that displays the log event in its entirety, for further review.
    1. URL escaped GET parameters and delimiters are used to ensure that the URL works as expected
    2. The sDate and sTime fields from line 2 are used in conjunction with the requestID to limit results to a the single originating log event
    3. The querystart() and queryend() functions return the start and end times specified for the PowerQuery. These values are used to specify the time frame of the search links that we generate in the searchURL column.

Results

mceclip0.png

Conclusion

PowerQueries are best suited for SQL aggregation operations and can be used for data manipulation routines (as demonstrated in this example). This query was modified from its original form to use sanitized log data.

Related articles

Comments

0 comments

Please sign in to leave a comment.