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:
- 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 theeventTime
field (which was previously extracted by the parser on ingestion) - Line 3:
filter
is being used as a condition to limit results - 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 - Line 5: Create a DataSet search URL that displays the log event in its entirety, for further review.
- URL escaped GET parameters and delimiters are used to ensure that the URL works as expected
- The
sDate
andsTime
fields from line 2 are used in conjunction with therequestID
to limit results to a the single originating log event - The
querystart()
andqueryend()
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 thesearchURL
column.
Results:
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).
Comments
0 comments
Please sign in to leave a comment.