Retrieving and Processing Event History

It's often necessary to find, aggregate, and filter any number of system or device events. This may be useful for:

• Generating reports
• Creating and filling a common table
• Processing events using a script
• Manually browsing event data

The common denominator of all above tasks is that we should extract selected historical events from the Iotellect Server database and convert them to a data table. This data table is then used to fill in a report or common table, feed the script with input data, or just presented to the operator.

There are three different ways to select the events and convert them to a data table:

• Using Iotellect expression language
• Using an Iotellect query
• Using a custom event writer to store events in a separate table and the database device driver to fetch them using native SQL

Here is a brief comparison of these methods:

Method selection hints:

• If you need to process a large number of events (100,000+), use native SQL
• If the number of events is not so large, but some aggregation is required, use Iotellect query language
• In all other cases use expression language

To select some events using the expression language, use the Get Event History function of the Events context. Specify context name/mask, event name, filtering expression and start date in the input parameters.

For example, let's make a report showing Login events for user john. This report's Source Data Expression will call Get Event History function having another expression (used for filtering events) specified as a string in the function parameter list:

{events:get("users", "login", "{username} == 'john'")}

Note that the last parameter (Start Date) is omitted and default NULL value will be used for it.

In this case, we fetch raw event data using the same Get Event History described above, but without specifying a filtering expression. We then run an Iotellect Query on this table, for filtering and aggregating the data using standard SQL operations.

For example, here's a query that will return all Login events of user john (see above):

SELECT

 *

FROM

 events:get("users", "login") as logins

WHERE

 logins.get$username = 'john'

To call use this query as a report Source Data Expression, use the following expression:

{:executeQuery("SELECT * FROM events:get('users', 'login') as logins WHERE logins.get$username = 'john'")}

This method is the most difficult in terms of configuration, so it should be used only if the other methods are not suitable. To fetch events using native SQL, do the following:

• Set up a custom event writer that will store a certain event in a dedicated database table with event-specific fields. The table will be created automatically by the event writer.
• Examine the structure of the table using your database management utility to find out what fields are available.
• Create a new Device and choose SQL Database as a device driver
• Copy settings of the Database device (Database Driver, URL, username, password, etc.) from the Iotellect Server global database configuration.

After that, you may execute any SQL query on the custom events table using the Execute Query operation provided by Database device driver.

Example: to select events using native SQL use the following report Source Data Expression:

{users.admin.devices.linkserver_database:executeQuery("SELECT * FROM logins WHERE username = 'john'")}

Was this page helpful?

Tell us more

Send

Thank you!