I am appending results from below query,which will display difererent objectype
suppliedMaterial:
index="" source="" "suppliedMaterial" AND "reprocess event" |stats count | rename count as ReProcessAPICall
| appendcols "" "suppliedMaterial" AND "data not found for Ids"| eval PST=_time-28800 | eval PST_TIME3=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=dataNotFoundIds path=dataNotFoundIds{}| stats values(*) as * by _raw | table dataNotFoundIds{},dataNotFoundIdsCount, PST_TIME3 | sort- PST_TIME3 ]
| appendcols [search index="" source="*" "suppliedMaterial" AND "sqs sent count" | eval PST=_time-28800 | eval PST_TIME4=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath sqsSentCount output=sqsSentCount | stats values(*) as * by _raw | table sqsSentCount PST_TIME4 | sort- PST_TIME4 ]
| appendcols [search index="" source="" "suppliedMaterial" AND "request body" | eval PST=_time-28800 | eval PST_TIME4=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=version path=eventBody.version | spath output=objectType path=eventBody.objectType | stats values(*) as * by _raw | table version, objectType ] | table objectType version dataNotFoundIdsCount sqsSentCount ReProcessAPICall
For Material
index="" source="" material" AND "reprocess event" |stats count | rename count as ReProcessAPICall
| appendcols*" "material" AND "data not found for Ids"| eval PST=_time-28800 | eval PST_TIME3=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=dataNotFoundIds path=dataNotFoundIds{}| stats values(*) as * by _raw | table dataNotFoundIds{},dataNotFoundIdsCount, PST_TIME3 | sort- PST_TIME3 ]
| appendcols [search index="" source="*" "material" AND "sqs sent count" | eval PST=_time-28800 | eval PST_TIME4=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath sqsSentCount output=sqsSentCount | stats values(*) as * by _raw | table sqsSentCount PST_TIME4 | sort- PST_TIME4 ]
| appendcols [search index="" source="" "material" AND "request body" | eval PST=_time-28800 | eval PST_TIME4=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=version path=eventBody.version | spath output=objectType path=eventBody.objectType | stats values(*) as * by _raw | table version, objectType ] | table objectType version dataNotFoundIdsCount sqsSentCount ReProcessAPICall
My actual is :
objectType | version | dataNotFoundIdsCount | sqsSentCount | ReProcessApiCall |
suppliedMaterial | all | 4 | 15 | 12 |
suppliedMaterial | latest | 2 | 19 | |
suppliedMaterial | all | 3 | 11 | |
Material | latest | 6 | 10 | |
Material | latest | 5 | 4 | |
Material | all | 4 | 1 | |
Material | all | 2 | 3 |
My Expected is : Basically I needed to count the two fields (dataNotFoundIdsCount & ssqsSentCount based on what version whether 'all' or 'latest') from the previous queries .
I am thinking to use the version as dynamic values , and bring conditional check in those queries to add the field values for each version and name it as dataNotFoundIdsCount_all ,dataNotFoundIdsCount_latest.
Finally in the last query again check the version and show the sum Please advise if there's a easy way of doing this ..
objectType | version | dataNotFoundIdsCount | sqsSentCount | ReProcessApiCall |
suppliedMaterial | all | 4 | 15 | 12 |
suppliedMaterial | latest | 2 | 19 | |
Material | all | 3 | 11 | |
Material | latest | 6 | 10 |
You have a couple of complex and confusing searches - using appendcols does not guarantee that the data in the row relate to each other in a meaningful way. It is difficult to see how your expected result can be derived from your actual result.
Perhaps if you shared some anonymised sample events, it might be clearer what you are dealing with and what you are trying to achieve.
Hi @ITWhisperer
Added a log to all the event to that it can be picked up commonly,which resolved it
Hi @ITWhisperer
Based on the below the raw events....I need to filter based on the attribute "suppliedMaterial" and "version"- get the result of row and then add the columns of sqsSentCount and dataNotFoundIdsCount similar to below
objectType | version | dataNotFoundIdsCount | sqsSentCount |
suppliedMaterial | all | 1 | 8 |
suppliedMaterial | latest | 3 | 9 |
Material | all | 3 | 11 |
Material | latest | 6 | 10 |
supplied material
1st event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"event":{"resource":"/v1/reprocess/id","path":"/support/v1/reprocess/id","httpMethod":"POST","pathParameters":null,"queryStringParameters":null,"body":"{\n \"objectType\": \"suppliedMaterial\",\n \"objectIds\": [\n \"569683\",\n \"564373er\",\n \"569129\"\n ],\n \"version\": \"all\"\n}","requestContext":{"requestId":"","authorizer":{"principalId":"","integrationLatency":0},"domainName":""}},"msg":"reprocess event","time":"2023-11-15T05:47:59.318Z","v":0}
2nd event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"supportType":"reprocess","entity":"suppliedMaterial","dataNotFoundIds":["564373er"],"dataNotFoundIdsCount":1,"msg":"data not found for Ids","time":"2023-11-15T05:47:59.329Z","v":0}
3rd event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"supportType":"reprocess","entity":"suppliedMaterial","sqsSentCount":8,"msg":"sqs sent count","time":"2023-11-15T05:47:59.364Z","v":0}
4th event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"eventBody":{"objectType":"suppliedMaterial","objectIds":["569683","564373er","669179"],"version":"all"},"msg":"request body","time":"2023-11-15T05:47:59.318Z","v":0}
5 event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"event":{"resource":"/v1/reprocess/id","path":"/support/v1/reprocess/id","httpMethod":"POST","pathParameters":null,"queryStringParameters":null,"body":"{\n \"objectType\": \"suppliedMaterial\",\n \"objectIds\": [\n \"569683\",\n \"564373er\",\n \"669179\"\n ],\n \"version\": \"latest\"\n}","requestContext":{"requestId":"","authorizer":{"principalId":"","integrationLatency":0},"domainName":""}},"msg":"reprocess event","time":"2023-11-15T05:47:59.318Z","v":0}
6 event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"supportType":"reprocess","entity":"suppliedMaterial","dataNotFoundIds":["564373er"],"dataNotFoundIdsCount":3,"msg":"data not found for Ids","time":"2023-11-15T05:47:59.329Z","v":0}
7 event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"supportType":"reprocess","entity":"suppliedMaterial","sqsSentCount":9,"msg":"sqs sent count","time":"2023-11-15T05:47:59.364Z","v":0}
8 event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"eventBody":{"objectType":"suppliedMaterial","objectIds":["569683","564373er","569129"],"version":"latest"},"msg":"request body","time":"2023-11-15T05:47:59.318Z","v":0}
material
1st event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"event":{"resource":"/v1/reprocess/id","path":"/support/v1/reprocess/id","httpMethod":"POST","pathParameters":null,"queryStringParameters":null,"body":"{\n \"objectType\": \"material\",\n \"objectIds\": [\n \"569683\",\n \"564373er\",\n \"469196\"\n ],\n \"version\": \"all\"\n}","requestContext":{"requestId":"","authorizer":{"principalId":"","integrationLatency":0},"domainName":""}},"msg":"reprocess event","time":"2023-11-15T05:47:59.318Z","v":0}
2nd event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"supportType":"reprocess","entity":"material","dataNotFoundIds":["564373er"],"dataNotFoundIdsCount":3,"msg":"data not found for Ids","time":"2023-11-15T05:47:59.329Z","v":0}
3rd event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"supportType":"reprocess","entity":"material","sqsSentCount":11,"msg":"sqs sent count","time":"2023-11-15T05:47:59.364Z","v":0}
4th event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"eventBody":{"objectType":"material","objectIds":["569683","564373er","569129"],"version":"all"},"msg":"request body","time":"2023-11-15T05:47:59.318Z","v":0}
5 event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"event":{"resource":"/v1/reprocess/id","path":"/support/v1/reprocess/id","httpMethod":"POST","pathParameters":null,"queryStringParameters":null,"body":"{\n \"objectType\": \"suppliedMaterial\",\n \"objectIds\": [\n \"569683\",\n \"564373er\",\n \"569129\"\n ],\n \"version\": \"latest\"\n}","requestContext":{"requestId":"","authorizer":{"principalId":"","integrationLatency":0},"domainName":""}},"msg":"reprocess event","time":"2023-11-15T05:47:59.318Z","v":0}
6 event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"supportType":"reprocess","entity":"suppliedMaterial","dataNotFoundIds":["564373er"],"dataNotFoundIdsCount":6,"msg":"data not found for Ids","time":"2023-11-15T05:47:59.329Z","v":0}
7 event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"supportType":"reprocess","entity":"suppliedMaterial","sqsSentCount":10,"msg":"sqs sent count","time":"2023-11-15T05:47:59.364Z","v":0}
8event-
{"name":"","awsRequestId":"1","hostname":","pid":8,"level":30,"eventBody":{"objectType":"suppliedMaterial","objectIds":["569683","564373er","569129"],"version":"latest"},"msg":"request body","time":"2023-11-15T05:47:59.318Z","v":0}