Finding value from search and appending to lookup

[k31453]

Hi I have following LARGE lookup with over 1000 entries

|host | type |
|host1 |            |
|host2 |            |
|host3 |            |

I have SPL query which returns in json format:

{
  type: big
  tags: {
     address: host1
        }
}

 
I need to append my lookup table if host=tags.address then append type=big. 

Result of my lookup:
|host | type |
|host1 |   big   |
|host2 |            |
|host3 |            |

Note: SPL query is very big and i need to lookback data at least 1 year back. I only care about filling my lookup table.  Some of the hosts latest entry maybe 2 months ago and some 8 months ago.

[gcusello]

Hi @k31453,

if you have to append new records to an existing lookup, you have to create a search with the new records and then add the lookup's records, something like this:

my_search NOT [ | inputlookup my_lookup.csv | fields host ]
| dedup host type
| table host type
| outputlookup my_lookup.csv append=true

if instead you want to add new hosts and update lookup values with the values from the search, you have to run something like this:

your_search
| append [ | inputlookup my_lookup.csv | rename host AS lookup_host | fields host lookup_type ]
| eval type=coalesce(type,lookup_type)
| dedup host type
| table host type
| outputlookup my_lookup.csv

 Ciao.

Giuseppe

[k31453]

@gcusello My intention is simple. Get the type value from search and append it to existing lookup. 

the 2nd one bit doesn't make sense to me. We are renaming the host. But using old host as fields. 

The logic i am trying to work :

1. get the host value from lookup i.e. host1
2. If host == tags.address, output type big in lookup table.

Thanks for advice. 

[gcusello]

Hi @k31453,

Append means add, but if the hostname is already in the lookup or not it's relevant in the report creation because  the two situations must be managed in a different way:

• if you're sure that the hosts to append are always new, you have to use the first search, because the only operation to do is to add the new hosts,
• if instead you could already have some hosts in the lookup and you want only to update the type value for these hosts, you have to use the second one, because this search add the new hosts and update only the type for the existing ones.

Ciao.

Giuseppe

[k31453]

Hi @gcusello 

We know for sure we want to work with the host which was in lookup table and we do not care about any other hosts. 

Second one takes significant amount of load time though. And SPL query i am bit confused. 

| eval type=coalesce(type,lookup_type)

lookup type and search type both field name is exactly same so what are we doing here? 

[gcusello]

Hi @k31453,

if this is your need you need the second one.

I don't think that the second requires more load time because the lookup isn't so large (only 1000 records).

The coalesce is to manage an eventual difference between the type value in main search and in lookup, in this way you take the one from the main search.

Ciao.

Giuseppe

[k31453]

Hi @gcusello 

lookup is not large. But search query is significantly large. We are talking about millions of records a month. i have tried 2nd query it is taking significant time. Is this normal or am  i doing something wrong. 

And also how to address field such as below when constructing 2nd query

  tags: {
     address: host1

[gcusello]

Hi @k31453,

as I said a little lookup (1000 records) shouldn't be relevant if you have a main search of milions of events.

About the second question, if you have in the main search host with no type, the search takes the value from the lookup, if it's empty also the lookup it returns an empty value.

You have to decide if to add to the lookup or not.

Ciao.

Giuseppe

[k31453]

Hi @gcusello 

So main source does have type field.  Does second search then take value from type in main search and put it in corresponding lookup type?

[gcusello]

Hi @k31453,

yes exactly.

Ciao.

Giuseppe