OnDemand User Group

Support Forums => Other => Topic started by: Joe Wolken on June 29, 2022, 11:47:13 AM

Title: SQL for Last Load, Last Query and Last Retrieval of each Application Group
Post by: Joe Wolken on June 29, 2022, 11:47:13 AM
Within the Administration Client Application Group list there is now the ability to enable 'Document Activity Tracking (going forward) for the 'Last Load', 'Last Query' and 'Last Retrieve'.

Does anyone have and willing to share the SQL statements (I image one SQL each for Load, Query and Retrieve) to run that would result in a list of all of the Application Groups with their most recent dates for each.  I assume the data would come from the System Log.

I am looking for historical data.  For example, seeing if the most recent retrieval for some reports was 1 year ago, I would not have that if I just now turned on the Document Activity Tracking, since it only populates day forward.

Thanks,
Joe
Title: Re: SQL for Last Load, Last Query and Last Retrieval of each Application Group
Post by: rjrussel on June 30, 2022, 06:45:12 AM
Joe,

Once you enable this feature for an application group, the information begins to be tracked. You can see the data in the OD Administrator in the list of Application groups. You can't however retroactively get this info for an application group that doesn't have this enabled.

FYI - It is not advised to leave this turned on permanently as it does create overhead on the server.

-Rob
Title: Re: SQL for Last Load, Last Query and Last Retrieval of each Application Group
Post by: Darrell Bryant on June 30, 2022, 11:52:28 AM
Application group query information is captured in the system log only if it has been turned in the Application Group, the default is that query activity is not logged. The message numbers are 65 (before the query is run) and 226 (after the query is run, contains the number of hits returned).
Document retrieval information is logged by default, but can be turned off. The message number is 66.
All loading activity is logged. The message number is 87. This can also be determined from the System Load facility.

If this is a small customer with only one System Log table (SL2) then you can use SQL to analyze the activity. If they have multiple System Log tables then I think is easier to perform the analysis by searching the System Log using the client.

Using the client you could pick a date range, specify an In search for Msg Num with value 65 66 87, and a Like search for Message with value %<name of AG>%.

I don't know enough SQL to only return one row per AG per activity (query, retrieve, load), which is what you want.

As for leaving the Document Activity Tracking turned on in the AG permanently, I would say it depends on the existing workload on the system. Having the current information might be valuable to some customers, justifying the overhead.