tuning the query

[nithys]

Hi Team,I am using a query which has same index and source but fetch two results based on the search and combine to a single table..now i want to display the result along with the timestamp it appears in ascending order
index=index1 source=source1 CASE("latest") AND "id" AND "dynamoDB data retrieved for ids" AND "material"| eval PST=_time-28800 | eval PST_TIME3=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=dataNotFoundIdsCount path=dataNotFoundIdsCount | stats values(*) as * by _raw | table dataNotFoundIdsCount, PST_TIME3 | sort- PST_TIME3| appendcols [search index=index1 source=source1 CASE("latest") AND "id" AND "sns published count" AND "material"| eval PST=_time-28800 | eval PST_TIME4=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath snsPublishedCount output=snsPublishedCount |spath output=republishType path=republishType| spath output=version path=republishInput.version| spath output=publish path=republishInput.publish| spath output=nspConsumerList path=republishInput.nspConsumerList{} | spath output=objectType path=republishInput.objectType | stats values(*) as * by _raw | table snsPublishedCount,republishType,version,publish, nspConsumerList,objectType,PST_TIME4 | sort- PST_TIME4 ] |table PST_TIME4 objectType version republishType publish nspConsumerList snsPublishedCount dataNotFoundIdsCount

[gcusello]

Hi @nithys ,

you cannot put the case funtion directly in the search, but only in the eval command, in addition there are some errors and the case condition isn't clear, please better describe them.

1)

what's the case condition?

CASE("latest") AND "id" AND "dynamoDB data retrieved for ids" AND "material"

2)

The errors are:

• case: there a parenthesis after the first value, there's only one condition and there isn't the value to assign if the condition is true,
• | sort- PST_TIME3, should be sort - PST_TIME3: a space after sort command is missed,
• you don't need an intermediate table command,
• what do you want to do with appendcols?

in other words, please better describe it.

Ciao.

Giuseppe

[nithys]

Hi @gcusello 
1.With below query  i am trying to fetch three fields from three different event logs which match all 3
condition
CASE is used get the extact uppercase/lowercase match of "latest,material" from first log event
"id,material" from second log event
"dynamoDB data retrieved for ids,dataNotFoundIdsCount,material" from third log event
 from third event log

CASE("latest") AND "id" AND "dynamoDB data retrieved for ids" AND "material"

Based on above condition

PST_TIME4	objectType	version	republishType	publish	nspConsumerList	snsPublishedCount	dataNotFoundIdsCount
2023-20-11 02:55:12	material	latest	id	NSP	ALL	3	1
2023-16-11 09:18:14	material	latest	id	NSP	ALL	3	1
2023-12-12 05:03:37	material	latest	id	ALL	ALL	1	2

2.CASE("latest") AND "id" AND "sns published count" AND "material"
Appendcols is used to fetch sns published count,publish,version,republishInput along with other filter condition latest,id,material

[nithys]

PST_TIME4 objectType version republishType publish nspConsumerList snsPublishedCount dataNotFoundIdsCount

2023-17-11 01:50:35	material	latest	id	NSP	ALL	3	1
2023-20-11 02:55:12	material	latest	id	NSP	ALL	3	1
2023-16-11 09:18:14	material	latest	id	NSP	ALL	3	1
2023-12-12 05:03:37	material	latest	id	ALL	ALL	1	2