Lookup (hlurjson)

[ 1 Description ] [ 2 How does it work in the search window? ] [ 3 How does it work in LINQ? ] [ 4 Example ]

Description

This operation returns all the values of a row in a time range lookup as a single JSON field upon successful key matching.

Existing lookups required

To perform these operations, it is necessary to have existing lookups ready for use (visit this article to get help uploading lookups and this article to get help creating query lookups).

How does it work in the search window?

Select Create field in the search window toolbar, then select the Lookups category, and choose the Lookup (hlurjson) operation from the dropdown (more info here). You need to specify three arguments:

Argument	Description	Data type

Argument	Description	Data type
Lookup name ^mandatory	Choose the lookup you want to use to enrich your table.	string
Key ^mandatory	Choose the table field you want to use to find matches with the lookup key field.	same as lookup key field
Time ^mandatory	Choose the table timestamp you want to use to correlate with the lookup timestamp. It identifies the row with the latest timestamp in the lookup before the timestamp in your table.	timestamp

Once you specify the adequate arguments and click the Create field button, the new field is added to your table.

Not only keys are correlated to return values but also the timestamps of both lookup and table. The timestamp in your table will be matched with the closest previous lookup timestamp to retrieve the values in the whole row when both keys match. Your new table field will display values according to the time slot they belong to, which corresponds to the intervals between the lookup timestamps.

The data type of the values in the new column will be JSON. You can then split the resulting JSON objects into their different key/value pairs and transform them into different data types using the operations in the JSON group.

Lookup					Table

Lookup					Table
Timestamp	Key	Field1	Field2	Field3	Timestamp	Key	New field
					04:57:00	non-key value	null
					05:29:00	key value	null
05:35:00	key value	value1	value2	value3
					07:18:00	key value	(field1:value1, field2:value2, field3:value3)
					07:28:00	key value	(field1:value1, field2:value2, field3:value3)
					07:29:00	non-key value	null
07:35:00	key value	value4	value5	value6
					07:44:00	key value	(field1:value4, field2:value5, field3:value6)
					08:55:00	key value	(field1:value4, field2:value5, field3:value6)
					09:05:00	non-key value	null
					09:25:00	key value	(field1:value4, field2:value5, field3:value6)
09:35:00	key value	value7	value8	value9
					09:43:00	key value	(field1:value 7, field2:value8, field3:value9)
					10:33:00	key value	(field1:value 7, field2:value8, field3:value9)

How does it work in LINQ?

Use the create field operator select... as new_field and add the operation syntax to create the new column. This is the syntax for the Historical lookup:retrieve json (hlurjson) operation:

hlurjson("Lookup_name", Key_field, Timestamp_field)

The complete syntax with both the create field operator and the operation syntax is:

select hlurjson("Lookup_name", Key_field, Timestamp_field) as new_field

Syntax considerations

"lookup_name" → This must be the name of lookup that contains the data you want to use to enrich your data.
key_field → This must be the table field that will be used to find matches with the lookup key. The name can be different than the lookup key field as long as the data type coincide and the values it contains are potential matches (username-user). The absence of matches will return null and a different data type will return an error when running the query .
timestamp_field → This must be the table timestamp that will be used to correlate with the lookup timestamp. A data type other than timestamp will return an error when running the query.

Example

After performing the operations you need to manipulate your data, such as filtering and grouping operations, you can use the Lookup (hlurjson) operation to enrich your data with the following time range lookup:

Lookup name: Enrichment
Lookup fields: method, username, city (key), eventdate (timestamp)

These are the arguments needed when using the interface :

Lookup name: Enrichment
Key: city
Time: eventdate

This is the syntax needed when using LINQ free-text query:

from siem.logtrust.web.activity
where isnotnull(city)
where not isempty(city)
group every 1h by city, method
select hlurjson("Enrichment", city, eventdate)

The table timestamp will be matched with the closest previous lookup timestamp, and the values in the entire lookup row will be brought into your table when the values in the city fields match. When they do not match, null will be returned.