| Table of Contents | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...
This operation returns the values of a given historical lookup field field in a lookup that keeps history upon successful key correlationmatching, and optionally upon time correlation.
| 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 theHistorical Lookup (hluhlut) operation from the dropdown (more info here). You need to specify four arguments (one of them optional):
Argument | Description | Data type | ||
|---|---|---|---|---|
Lookup name mandatory | Choose the lookup you want to use to enrich your table. | string | ||
Lookup field mandatory | Choose the lookup field 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 value with the highest timestamp in the lookup that is before the timestamp in your table. | timestamp | ||
| Info |
Once you specify the adequate arguments and click the Create field button, the new field is added to your table.
When the values of the lookup key field match the values of the Key argument, the new field displays the corresponding value from the Lookup field argument. If there is no match, it displays null.
When the time argument is used, not 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 its corresponding value 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.
The data type of the values in the new column will be the same as the original field brought from the lookup.
| Expand | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
...
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 Lookup (hluhlut) operation:
hluhlut("Lookup_name", "Lookup_field", Key_field)hluhlut("Lookup_name", "Lookup_field", Key_field, Timestamp_field)
The complete syntax with both the create field operator and the operation syntax is:
select hluhlut("Lookup_name", "Lookup_field", Key_field) as new_fieldselect hluhlut("Lookup_name", "Lookup_field", Key_field, Timestamp_field) as new_field
| 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). Syntax considerations
|
...
Example
...
We want to enrich the siem.logtrust.web.activity table with 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 (hlu) operation.
...
If you have the following time range lookup, you can enrich your data table:
Lookup name: Historical_company_officesEnrichment
Lookup field: Office_type
Key: city
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, region
select hlu("Historical_company_offices", "Office_type", city) |
The values in the Office_type lookup field will be brought into our table when the values in the city field and those in the lookup key field match. When they do not match, null will be returned.
Complete example with screenshot of the result when development is in a more mature stage.
Example 2 (with time argument)
...
fields: method, username, city (key), eventdate (timestamp)
After performing any other operation we want to manipulate our data, such as filtering and grouping operations. Then, we will use this upload lookup that contains info about company offices and the Lookup (luhlut) operation.
These are the arguments needed when using the interface :
Lookup name: Company_offices
Lookup field: Office_type
Key: city
Time: eventdate
...
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, region select hluhlut("Historical_company_offices", "Office_type", city, eventdate) |
The table timestamp will be matched with the closest previous lookup timestamp and the values in the Office_type lookup field will be brought into our table when the values in the city field and those in the lookup key field match. When they do not match, null will be returned.
...
.