I have a field from one query that gets appended with another field coming from second query.How do I filter those value

[nithys]

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

[ITWhisperer]

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.

[nithys]

Hi @ITWhisperer 
Added a log to all the event to that it can be picked up commonly,which resolved it

[nithys]

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}