Hi Team,

I am looking for Search Expression syntax that can be used to create "Background Search Class Templates" in ACCE. Syntax with examples would be very useful.

I am trying below syntax but it seems its incorrect.

SELECT [ClassDescription], [CmAuditSequence], [Creator], [DateCreated], [DateLastModified], [EventStatus], [Id], [InitiatingUser], [LastModifier], [Name], [Owner] FROM [Event] WHERE [DateCreated] <= 20210223T183000Z OPTIONS(TIMELIMIT 180)

Your SQL has a syntax problem and this is not related to Background Searches. If I feed our SQL to Object Store Search in ACCE I get:

FNRAC4004E

The query string is invalid.

User response: The 'This' property must be the first value specified in the SELECT clause of the query. If this query uses a JOIN, the 'This' must be fully qualified as TableName.This.

So play in acce Object Store search until a reasonable result is produced and feed that to the background searches...

br,

Gerold

I am able to retrieve all the audit events in a csv report for a specific period using below query Syntax. I used the below query in Background Search Class Template and used this template in Background Search Sweeps.

I used "AS" keyword syntax to explicitly indicate the search result property for an item in the SELECT clause. This explicit mapping is called aliasing.

SELECT ClassDescription AS EventClassType, CmAuditSequence AS AuditSequence, Creator AS CreatedBy, DateCreated AS CreatedDate, DateLastModified AS LastModifiedDate, EventStatus AS StatusOfEvent, Id AS EventID, InitiatingUser AS InitiatedUser, LastModifier AS LastModifiedBy, Name AS EventName, Owner AS EventOwner FROM [Event] WHERE [DateCreated] <= 20210223T183000Z

Now, only concern I have is , in a csv report EventClassType (ClassDescription) column is showing as event Class ID instead of Event Class Name (such as Update Event,Get Object Event etc). Any suggestion on how to get the Event Class name instead of GUID in search result csv report ?

All I can say is that on my 5.5.6 system

SELECT [This], [ClassDescription], [CmAuditSequence], [Creator], [DateCreated], [DateLastModified], [EventStatus], [Id], [InitiatingUser], [LastModifier], [Name], [Owner] FROM [Event] OPTIONS(TIMELIMIT 180,COUNT_LIMIT 1000)

returns

I am trying in 5.5.4 system , Not sure what is changed in 5.5.6 version. May be you do not have the privileges.