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" | 
parse "$sDate$T$sTime$" from eventTime |
filter eventType=='AwsApiCall' |
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 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)
  2. Line 3: filter is being used as a condition to limit results
  3. Line 4: 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 5: 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

This query was modified from its original form to use sanitized log data. However, it enabled the customer to apply advanced processing routines to their log data while keeping the log event in its original form for further investigation and reducing associated noise (step 4(2) was done because a single request ID usually had multiple associated events in the same logfile).

Related articles

Comments

0 comments

Please sign in to leave a comment.