Table of Contents | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...
This operation returns all the values of a row in a historical time range lookup row into as a single JSON field upon successful key correlationmatching.
Info |
---|
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 Historical Lookup : retrieve json (hlurjson) operation from the dropdown (more info here). You need to specify three arguments:
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 correlate 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 highest latest timestamp in the lookup that is before the timestamp in your table. | timestamp | info|
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). |
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 correlate key values. When retrieve the values of the lookup key field match the values of the Key argument, the new field displays all the values of the corresponding lookup row. If there is no match, it displays nullin the whole row when both keys match. Your new table field will display ranges of recurring values according to the time slot they belong to, which corresponds to the intervals between the lookup timestamps.
...
select hlurjson("Lookup_name", Key_field, Timestamp_field) as new_field
Info | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Syntax special considerations
|
Example
We want to enrich the siem.logtrust.web.activity
table with all the information about the working model in each city. If we want to work more comfortably, we can isolate the data we’re interested in by using filter and grouping operations. Then, we will use this upload lookup that contains info about company offices and the Historical lookup: retrieve json (hlurjson) operation.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: Historical_company_offices Enrichment
Key: city
Time: eventdate
Complete example with screenshot of the arguments when development is in a more mature stage.
This is the syntax needed when using LINQ free-text query:
Code Block |
---|
from siem.logtrust.web.activity where isnotnull(city) where not isempty(city) where result = "OK" group every 1h by city, result, regionmethod select hlurjson("Historical_company_officesEnrichment", 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 our your table when the values in the city field and those in the lookup key field fields match. When they do not match, null will be returned.
...
.