# Using a CSV lookup table within a pipeline

**URL:** <https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707>\
**Category:** Graylog Central (peer support)\
**Tags:** pipeline-rules, debuggingpl\
**Created:** [January 5, 2018, 11:04am UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707 "2018-01-05T11:04:36Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 5, 2018, 11:04am UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/1 "2018-01-05T11:04:36Z")

</div>

Dear All,

I have a csv (UTF-8 encoded) file as shown below

```nohighlight
"Command","Rank","Category"
"tasklist","1","Attacker Investigation"
"ver","2","Attacker Investigation"
"ipconfig","3","Attacker Investigation"
"net time","4","Attacker Investigation"
"systeminfo","5","Attacker Investigation"

```

I have set the data adapter key to be the Command field and the lookup to be the Rank,Category.

Now I have logs with a CommandLine parameter which shows the commands that have been run on my Windows system which are coming into Graylog via a GELF input.

I want to use a pipeline function similar too below to query the lookup table and add one / two fields in to the log if there is a match or not

```nohighlight
hack_command_run = true / false for match no match
hack_command_lookup = lookup results

```

My function code is as follows.

```nohighlight
// function to check Commandline field in logs against a lookup table in a stream

rule "check_for_bad_command"
when
   // To save CPU cycles, only run on this field in stream
   has_field("CommandLine")
   then
	set_fields(hack_command_run,"false");
	let hack_command_lookup = lookup_value(to_string("Command", $message.CommandLine), " CommandLine");
	set_fields(hack_command_lookup);
	set_fields(hack_command_run,"true");
end

```

My expected add fields i want to achieve are

No match (tasklist)

```auto
hack_command_run=false

```

Match

```auto
hack_command_run=true
hack_command_lookup=1,Attack Investigation

```

Any helps appreciated

Jake

---

<div class="post-metadata">

**Author:** ![jochen](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/jochen/32/8_2.png) [@jochen](https://community.graylog.org/u/jochen)\
**Post date:** [January 5, 2018, 11:41am UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/2 "2018-01-05T11:41:49Z")

</div>

> [@Magneton](#):
>
> ```auto
> set_fields(hack_command_run,"false");
> 
> ```

You’re missing double quotes around the field name.  
[http://docs.graylog.org/en/2.4/pages/pipelines/functions.html#set-field](http://docs.graylog.org/en/2.4/pages/pipelines/functions.html#set-field)

> [@Magneton](#):
>
> ```auto
> to_string("Command", $message.CommandLine)
> 
> ```

What do you expect that function call to return?  
[http://docs.graylog.org/en/2.4/pages/pipelines/functions.html#to-string](http://docs.graylog.org/en/2.4/pages/pipelines/functions.html#to-string)

> [@Magneton](#):
>
> ```auto
> lookup_value(to_string("Command", $message.CommandLine), " CommandLine");
> 
> ```

The order of function parameters is relevant and I’m pretty sure yours is wrong.  
[http://docs.graylog.org/en/2.4/pages/pipelines/functions.html#lookup-value](http://docs.graylog.org/en/2.4/pages/pipelines/functions.html#lookup-value)

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 5, 2018, 11:51am UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/3 "2018-01-05T11:51:04Z")

</div>

Hi jochen,

I was debugging function and discovered a few errors. My code is now .

// function to check sysmon Commandline against a lookup table in a stream

rule "check\_for\_bad\_command"  
when  
// To save CPU cycles, only run on this field in stream  
has\_field(“CommandLine”)  
then  
set\_field(“hack\_command\_run”,“false”);  
let hack\_command\_lookup = lookup(“Naughty\_Commands”,“Command”, $message.CommandLine);  
set\_field(“hack\_cmd\_detected”, hack\_command\_lookup);  
set\_field(“hack\_command\_run”,“true”);  
end

Ok mistakes in previous code

1. set\_fields should have been set\_field with quotes
2. let hack\_command\_lookup = lookup\_value should have been let hack\_command\_lookup = lookup function which returns a multiple value according the summary in the rules editor.

I am expecting the lookup function to return two values, for example if the CommandLine field in the log had “tasklist” in it, the lookup should use the key value field Command in the CSV to return 1,Attack Investigation into the variable hack\_command\_lookup?

Question  
Do I need to do the string conversion as the log looks to have a string in it?

Cheers

Jake

---

<div class="post-metadata">

**Author:** ![jochen](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/jochen/32/8_2.png) [@jochen](https://community.graylog.org/u/jochen)\
**Post date:** [January 5, 2018, 12:20pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/4 "2018-01-05T12:20:16Z")

</div>

> [@Magneton](#):
>
> Do I need to do the string conversion as the log looks to have a string in it?

No, not necessarily but it’s a safe-guard against unexpected types in the fields you’re referring to.

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 10, 2018, 4:16pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/5 "2018-01-10T16:16:45Z")

</div>

Hi Jochen,

I nearly have everything working except for one thing, they lookup key is returned not the value. I will explain

Logs are being sent in and have a conversation on field name so that sysmon\_cmd\_event holds the command run by a user for example ‘tasklist’

I have a pipeline rule that is the following.

// function to check sysmon\_cmd\_event against a lookup table in a stream

rule “check\_for\_uncommon\_command”  
when  
// To save CPU cycles, only run on this field in stream

has\_field(“sysmon\_cmd\_event”)

then

// set fields to default values  
set\_field(“UncommonCommand”,“false”);  
set\_field(“CommandCategory”, “None”);

// Perform Lookups and set fields

```
let hack_command_lookup = lookup("uncommon-commands","Command", $message.sysmon_cmd_event);
set_field("CommandCategory", hack_command_lookup);
set_field("UncommonCommand","true");

```

end

If I check the lookup table using the test lookup it appears fine

![lookup-1](https://us1.discourse-cdn.com/flex016/uploads/graylog/original/2X/c/cab07d03cce589d590cc8056c2d3568f9fa91fc1.JPG)

We get the correct value and I get an alert to slack which works.

However in the logs the CommandCategory has the lookup value., see below

 ![lookup-2](https://us1.discourse-cdn.com/flex016/uploads/graylog/original/2X/4/46224fbd77e4434367cb238b8325f2f5b16f9b42.JPG)

The UncommonCommand" fielld is set correctly. I was expecting the “CommandCategory” field to be “Attacker Investigation” .

Have I made a mistake in the pipeline code? Why is the key value returned and not the ‘looked up’ value?

Cheers Jake

---

<div class="post-metadata">

**Author:** ![jochen](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/jochen/32/8_2.png) [@jochen](https://community.graylog.org/u/jochen)\
**Post date:** [January 10, 2018, 4:39pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/6 "2018-01-10T16:39:47Z")

</div>

You should take another look at the parameters of the [`lookup()`](http://docs.graylog.org/en/2.4/pages/pipelines/functions.html#lookup) function and their correct order.

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 10, 2018, 5:03pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/7 "2018-01-10T17:03:26Z")

</div>

Jochen,

See mistake should be

let hack\_command\_lookup = lookup(“uncommon-commands”,“Command”,“None”, $message.sysmon\_cmd\_event);

This would lookup based on the key "Command " described in csv with a default message of “None” if no lookup is found.

So this would still return the command parameter so it must be changed to

let hack\_command\_lookup = lookup(“uncommon-commands”,“Category”,“None”, $message.sysmon\_cmd\_event);

Cheers Jake

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 10, 2018, 5:15pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/8 "2018-01-10T17:15:57Z")

</div>

Hi Jochen,

Got it figured out

// Perform Lookups and set fields

```
let hack_command_lookup = lookup("uncommon-commands",$message.sysmon_cmd_event,"None");
set_field("CommandCategory", hack_command_lookup);

```

Silly me!!!

![lookup-correct](https://us1.discourse-cdn.com/flex016/uploads/graylog/original/2X/7/7f99d104d4d3436d5830665b9affbdec9e8bd311.JPG)

Jake

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 10, 2018, 5:21pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/9 "2018-01-10T17:21:23Z")

</div>

Hi Jochen,

Could I use the split function or similar to just leave “Attacker Investigation” rather than the key value pair?

Jake

---

<div class="post-metadata">

**Author:** ![jochen](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/jochen/32/8_2.png) [@jochen](https://community.graylog.org/u/jochen)\
**Post date:** [January 10, 2018, 7:00pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/10 "2018-01-10T19:00:26Z")

</div>

You could use `lookup_value()` instead of `lookup()`.

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 12, 2018, 4:20pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/11 "2018-01-12T16:20:53Z")

</div>

Hi Jochen,

When I use the lookup\_value form in the following way, it get errors.

// Perform Lookups and set fields

```
let hack_command_lookup = lookup_value("uncommon-commands",$message.sysmon_cmd_event,"None");
set_field("CommandCategory", hack_command_lookup);

```

I get indexer and parser values.  
{“type”:“mapper\_parsing\_exception”,“reason”:“object mapping for [CommandCategory] tried to parse field [CommandCategory] as object, but found a concrete value”}

Looking at the difference in the structure for the two functions we get

 ![lookup-diff](https://us1.discourse-cdn.com/flex016/uploads/graylog/original/2X/0/02aea70476243fdf573547fa87eaa2834110fb89.JPG)

This indicates to me that they return, different object types , hence the error described as above.

Can you explain to me why, it is not working?

Jake

---

<div class="post-metadata">

**Author:** ![jochen](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/jochen/32/8_2.png) [@jochen](https://community.graylog.org/u/jochen)\
**Post date:** [January 12, 2018, 4:33pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/12 "2018-01-12T16:33:25Z")

</div>

> [@Magneton](#):
>
> I get indexer and parser values.

Try rotating indices (System/Indices/Index Set/Maintenance) and, if that works, create a custom index template which properly sets the type for the “CommandCategory” field.

> [@Magneton](#):
>
> This indicates to me that they return, different object types , hence the error described as above.

Yes. `lookup()` returns a map (dictionary, object, whatever you’d like to call it) and `lookup_value()` returns a “flat” value.

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 12, 2018, 4:44pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/13 "2018-01-12T16:44:17Z")

</div>

Hi Jochen,

I rotated the indexes in (System/Indices/Index Set/Maintenance) using rotate active index option. Set the statement to use the lookup\_value in the function and get the same errors on the new rotated index

a minute ago graylog\_1 2b9ced70-f7b7-11e7-b971-06d71caf9804 {“type”:“mapper\_parsing\_exception”,“reason”:“object mapping for [CommandCategory] tried to parse field [CommandCategory] as object, but found a concrete value”}

Index is now graylog\_1.

Looks like that does not work unless I have done something incorrectly.

Jake

---

<div class="post-metadata">

**Author:** ![jochen](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/jochen/32/8_2.png) [@jochen](https://community.graylog.org/u/jochen)\
**Post date:** [January 12, 2018, 5:01pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/14 "2018-01-12T17:01:22Z")

</div>

> [@Magneton](#):
>
> Looks like that does not work unless I have done something incorrectly.

First change the pipeline rule (or suspend it), then rotate the index. Otherwise, the new index will have the same type for the “CommandCategory” field (because your rule fills it accordingly).

Alternatively, use another (unused) field name.

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 12, 2018, 5:03pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/15 "2018-01-12T17:03:38Z")

</div>

Hi Jochen,

I also took a look at the indexes via the API after rotation using [http://10.0.1.177:9000/api/system/indexer/overview](http://10.0.1.177:9000/api/system/indexer/overview) and got the following JSON back

{  
“deflector”: {  
“current\_target”: “graylog\_1”,  
“is\_up”: true  
},  
“indexer\_cluster”: {  
“health”: {  
“status”: “green”,  
“shards”: {  
“active”: 8,  
“initializing”: 0,  
“relocating”: 0,  
“unassigned”: 0  
}  
},  
“name”: “graylog”  
},  
“counts”: {  
“events”: 17019  
},  
“indices”: {  
“graylog\_1”: {  
“size”: {  
“events”: 233,  
“deleted”: 0,  
“bytes”: 1116764  
},  
“range”: {  
“index\_name”: “graylog\_1”,  
“begin”: “1970-01-01T00:00:00.000Z”,  
“end”: “1970-01-01T00:00:00.000Z”,  
“calculated\_at”: “2018-01-12T16:38:01.091Z”,  
“took\_ms”: 0  
},  
“is\_deflector”: true,  
“is\_closed”: false,  
“is\_reopened”: false  
},  
“graylog\_0”: {  
“size”: {  
“events”: 16786,  
“deleted”: 0,  
“bytes”: 26343100  
},  
“range”: {  
“index\_name”: “graylog\_0”,  
“begin”: “2017-11-30T22:49:07.000Z”,  
“end”: “2018-01-12T16:37:53.000Z”,  
“calculated\_at”: “2018-01-12T16:38:31.282Z”,  
“took\_ms”: 49  
},  
“is\_deflector”: false,  
“is\_closed”: false,  
“is\_reopened”: false  
}  
}  
}

The graylog\_1 index has a date time stamp of 1970 which is possibly a bug, do you want me to put it on Github?

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 12, 2018, 5:11pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/16 "2018-01-12T17:11:51Z")

</div>

Hi Jochen,

Is this relevant to the problem?

[http://docs.graylog.org/en/2.4/pages/lookuptables.html](http://docs.graylog.org/en/2.4/pages/lookuptables.html)

Lookupo Results Section of above

“Currently, the multi value can only be used in a pipeline rule when using the lookup() pipeline function.”

Jake

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 12, 2018, 5:24pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/17 "2018-01-12T17:24:01Z")

</div>

Hi Jochen,

Are we saying that the Command Category field has been mapped to the wrong field type in the default index?

Is that the reason that the changing the name may resolve it.

I will try changing the rule and then rotating the index and come back.

Jake

---

<div class="post-metadata">

**Author:** ![Magneton](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/magneton/32/1609_2.png) [@Magneton](https://community.graylog.org/u/Magneton)\
**Post date:** [January 12, 2018, 5:26pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/18 "2018-01-12T17:26:51Z")

</div>

Hi Jochen,

Problem solved removing pipepline rule from stage, changing functions, rotating and re-enabling.

 ![lookup-correct2](https://us1.discourse-cdn.com/flex016/uploads/graylog/original/2X/a/a6d958fdc0dc60bcd1a0779ec90acc16a857b49a.JPG)

Cheers

Jake

---

<div class="post-metadata">

**Author:** ![jochen](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/jochen/32/8_2.png) [@jochen](https://community.graylog.org/u/jochen)\
**Post date:** [January 12, 2018, 5:28pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/19 "2018-01-12T17:28:52Z")

</div>

> [@Magneton](#):
>
> The graylog\_1 index has a date time stamp of 1970 which is possibly a bug, do you want me to put it on Github?

That’s intentional since the index range (minimum and maximum timestamp) cannot be calculated for the currently write-active index. It’s basically a placeholder.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex016/uploads/graylog/original/3X/c/7/c7c09c6b5099570133d6502b83f50ba4430de5b6.png) [@system](https://community.graylog.org/u/system)\
**Post date:** [January 26, 2018, 5:29pm UTC](https://community.graylog.org/t/using-a-csv-lookup-table-within-a-pipeline/3707/20 "2018-01-26T17:29:11Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.