Versions Compared

Old Version 11

changes.mady.by.user Borja Moro Moreno

Saved on

compared with

New Version 12

changes.mady.by.user Borja Moro Moreno

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel2
outlinefalse
stylenone
typeflat
printabletrue

Description

This operation returns all the values of a historical lookup row into a JSON field upon successful key correlation.

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 with the lookup key field.

same as lookup key field

Time

Choose the table timestamp you want to use to correlate with the lookup timestamp. It identifies the row with the highest 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).

...

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.

Expand
titleResult: visual representation

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:

...

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 special considerations

  • Status
    title"lookup_name"
    → This must be the name of lookup that contains the data you want to use to enrich your data. The name of an inexistent lookup will return an error when running the query.

  • Status
    colourBlue
    titlekey_field
    → This must be the table field that will be used to correlate 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 .

  • Status
    colourGreen
    titletimestamp_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

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.

...