Searching Audit Data (Queries, Direct Persistence)
When you want to search audit data, use the QueryAuditHistoryWithQueryCriteria service. The Audit Subsystem supports filtering, sorting, and pagination capabilities for this service through the query parameter. It takes advantage of the ability of a database query to filter and sort the data. It also takes input from special widgets that were created to enhance filtering and provide pagination for the results.
 |
You can also use the original QueryAuditHistory service. However, that service will be deprecated a future release of ThingWorx Platform. If you prefer to use it, refer to the topic, Searching Audit Data (Queries, Data Table) for details.
|
This topic is organized as follows:
1. Query Basics
2. Searching with Context Constrained
Query Basics
The QueryAuditHistoryWithQueryCriteria service has the following format::
QueryAuditHistoryWithQueryCriteria(maxItems [INTEGER], startDate [DATETIME], endDate [DATETIME],
auditCategory[STRING],query[QUERY], locale[STRING])
The following table describes the parameters for this service:
|
Parameter
|
Description
|
Default Value
|
||
|---|---|---|---|---|
|
maxItems
|
The maximum number of results to return from the query. (INTEGER)
|
500 items
|
||
|
locale
|
The abbreviation of the language name in which to return the results. (STRING). For example, fr for French or zh_CN for Chinese (China). For a list of locales supported by ThingWorx, refer to Supported Locales below.
|
The locale of the logged-in user submitting the query
|
||
|
startDate
|
A date and time. The query begins looking up audit messages, starting from the date and time you specify here. (DATETIME)
|
Unspecified
|
||
|
endDate
|
A date and time. The query stops looking up audit messages when the date/time of the messages reaches the date and time you specify here. (DATETIME)
|
Unspecified
|
||
|
query
|
A query string (in JSON format). An example of a JSON query that you can use follows this table.
This parameter is used in several ThingWorx services. For complete details, refer to Query Parameter for Query Services.
|
N/A
|
By default, a query returns 500 rows. To change the number of rows returned by the query, you can set the maxItems parameter to the desired number of rows. There is an upper limit to the number of rows that you can specify for the maxItems parameter. By default that limit is 5000 rows. A system administrator can change this limit when configuring the Audit Subsystem. System administrators should refer to the topic, Configuration of the Audit Subsystem.
However, if you expect a significantly large number of results, use a QUERY.
Here is an example of a JSON-formatted query:
Example 1. JSON-formatted Query
The following JSON-formatted query shows how you might construct the filters, sorting, and pagination to be used with the QueryAuditHistoryWithQueryCriteria service (Direct Persistence implementation):
{
"filters": {
"type": "EQ",
"fieldName": "user",
"value": "Administrator",
"isCaseSensitive": false
},
"sorts": [
{
"fieldName": "timestamp",
"isAscending": true,
"isCaseSensitive": true
}
],
"pagination":{
"pageSize":50,
"pageNumber":2
}
}
Searching from the Context of a Thing
The QueryAuditHistory service for a Thing works in a similar manner to the QueryAuditHistory and QueryAuditHistoryWithQueryCriteria services on the audit subsystem, but with additional restrictions. The general behavior and all parameters of this service are the same as the services of the audit subsystem. The following are the restrictions of this service:
1. Checks if the user invoking the service is a member of the Auditors group. If not, the results are filtered by that user's permissions.
2. When the QueryAuditHistory service is invoked from a particular Thing, the search uses the entity identifiers instead of names.
The following table describes the parameters for the QueryAuditHistory service on a Thing:
|
Parameter Name
|
Description
|
|---|---|
|
maxItems
|
The maximum number of items to return. The base type of this parameter is NUMBER.
|
|
startDate
|
The earliest audit date to query. The base type of this parameter is DATETIME.
|
|
endDate
|
The latest audit date to query. The base type of this parameter is DATETIME.
|
|
query
|
The query definition. The base type of this parameter is QUERY. Format the query as JSON object..
|
|
locale
|
The localizationTable locale that is used to localize the results. The base type of this parameter is STRING.
|
To support Thing context-constrained queries for the Audit Subsystem, a user group was added, called Auditors. This group allows non-administrative users to see complete results from the QueryAuditHistory service when calling it from Things to which they have visibility.
For example, an Administrator create a Thing called ExampleThing and gave access to this Thing to User_A, who is a member of the Auditors group. The Administrator also gave access to this Thing to User_B, who is not in any special user groups. If each user runs the QueryAuditHistory service from ExampleThing, the expected results are as follows:
|
User
|
Group Membership
|
Expected Result
|
|---|---|---|
|
Administrator
|
Administrators
|
Returned InfoTable contains all audit entries related to ExampleThing.
|
|
User_A
|
Auditors
|
Returned InfoTable contains all audit entries related to ExampleThing.
|
|
User_B
|
No special gropus
|
Returned InfoTable contains audit entries associated withUser_B only.
|
Querying Legacy Audit Data While Direct Persistence Is Enabled
While Direct Persistence is enabled in Audit Subsystem Configuration, all Services that process or handle data are switching to Direct Persistence model - only data generated while it is enabled will be accessible. To enable users to access their data saved in Legacy format (DataTable entries in AuditDataTable entity) alternative way if accessing those records must be explained/provided.
 |
It is recommended to export legacy audit data to cold storage during a maintenance window before switching to Direct Persistence. Where this is not always possible, the rest of this section offers helpful workarounds.
|
You can use the AuditDataTable entity to query Audit 1.0 ("legacy") information. The AuditDataTable entity is part of a subset of DataTable entities that have some additional restrictions added, such as disallowing entries update, and can use most of the same services as other DataTable entities.
One such service is QueryDataTableEntries. This service allows you to use Data Tags, source and JSON Query to filter retrieved results.
|
|
Data Tags for Audit entries are always empty.
|
To run this service, specify the same JSON Query parameters as when running QueryAuditHistory or QueryAuditHistoryWithQueryCriteria. The following JSON Query example uses User and Timestamp filters and sorts entries from newest to oldest.
|
|
Pagination is not supported with the Data Table query services.
|
Example 2. JSON Query
{
"filters": {
"type": "AND",
"filters": [
{
"type": "EQ",
"fieldName": "user",
"value": "Administrator",
"isCaseSensitive": true
}, {
"type": "BETWEEN",
"fieldName": "timestamp",
"from": 1577836800000,
"to": 1609459199000
}
]
},
"sorts": [
{
"fieldName": "timestamp",
"isAscending": false
}
]
}
|
|
This service returns raw (untranslated) auditing records, along with audit entry information. For information about translation, refer to the next section.
|
Writing a Custom QueryAuditHistory Service
Due to the nature of data returned by the QueryDataTableEntries service, it may not always suit the needs of the average user. To get the same information as in the QueryAuditHistory and QueryAuditHistoryWithQueryCriteria services of the Audit Subsystem, developers can write a wrapper service. This service needs to perform the following basic tasks:
• Query for data from the AuditDataTable
• Apply translation of the Localization Tokens (message and category fields)
• Apply variable substitution for message argument replacements (message field)
• Remove DataTable entry information
An example of such a service follows.
Example 3. Custom QueryAuditHistory Service
This service is written in simple JavaScript and is usable on any appropriate ThingWorx entity.
// Query auditing data with provided parameters
var result = Things["AuditDataTable"].QueryDataTableEntries({
maxItems: maxItems /* NUMBER */,
values: undefined /* INFOTABLE */,
query: query /* QUERY */,
source: source /* STRING */,
tags: undefined /* TAGS */
});
// Applying translation
var categoryCache = {};
var messageCache = {};
var size = result.getRowCount();
for (var i = 0; i < size; i++) {
var row = result.getRow(i);
row.message = applyReplacements(getTranslatedToken(row.message, locale, messageCache), row.messageArgs);
row.auditCategory = getTranslatedToken(row.auditCategory, locale, categoryCache);
}
// Removing non AuditHistory columns
result.RemoveField("id");
result.RemoveField("key");
result.RemoveField("tags");
result.RemoveField("location");
result.RemoveField("messageArgs");
// Helper translation and caching function
function getTranslatedToken(token, language, cache) {
if (cache[token] == null || cache[token] == undefined) {
var params = {
language: language /* STRING */,
token: token /* STRING */
};
var translation = Resources["RuntimeLocalizationFunctions"].GetEffectiveTokenForLanguage(params);
cache[token] = translation;
return translation;
} else {
return cache[token];
}
}
// Helper variable replacement function
function applyReplacements(translation, replacementsInfoTable) {
var filledInTranslation = translation;
var replacements = replacementsInfoTable.getRow(0);
for (var key in replacements) {
if (replacements[key] != null && replacements[key] != undefined) {
filledInTranslation = filledInTranslation.replace(new RegExp("__" + key + "__", "g"), replacements[key]);
}
}
return filledInTranslation;
}
Supported Locales
The following figure shows the locales supported by ThingWorx:
