How to use lookup fields to compare with current event logs and filter them

[MJA411]

Hello Splunk members!

I currently have a search that produces "Users" connecting to certain "hosts" whereas the status of connection is "created". The output is displayed as | timechart span=d dc(users) by host

Here is a sample of how my search currently look like:

index=search "remote access" (host="1.1.1.1" OR host="2.2.2.2" OR host="3.3.3.3")
| eval host = case(host="1.1.1.1", "server1", host="2.2.2.2","server2", host="3.3.3.3", "server3")

| rex field=_raw "\shas\sbeen\s\(?P<status>(created))\."

| rex field=msg "\(SPI=\s(?P<session_id>\w+)\)"

| timechart span=d dc(users) by host

Now my aim is to have a lookup file (User-site.csv) the lookup has 2 fields.

1. UserID

2. Site

I would like to filter out users based on site. For example, I want my timechart to only produce results for site "ABC" where users connected to.

In my lookup UserID is matched to their dedicated Site, however the current search has Users from all Site which is something I need to filter out.

Note: The UserID on the lookup is not 100% a match to (users) field on the initial search so I think I need to have something like "LIKE" command to compare similar characteristics from my lookup UserID field with users and then filter out the events based on site code (i.e. ABC)

I would appreciate your assistance with this, I have previously made this work but this time the lookup fields are not 100% match so I can't figure a way to use a LIKE command here.

Thanks

[gcusello]

Hi @MJA411,

at first, in the search you shared you don't need the regexes extractions because you don't need them in the timechart command: Status and section_id aren't present in the timechart command.

Then you can use the lookup command to filter out the results before timechart.

If you don't have exact results, you have to put in the lookup (in transforms.conf) the option 

match_type = WILDCARD

 try something like this:

index=search "remote access" (host="1.1.1.1" OR host="2.2.2.2" OR host="3.3.3.3")
| eval host = case(host="1.1.1.1", "server1", host="2.2.2.2","server2", host="3.3.3.3", "server3")
| lookup your_lookup UserID AS users OUTPUT Site
| search Site="ABC"
| timechart span=d dc(users) AS dc_users BY host

Ciao.

Giuseppe

[MJA411]

Hi @gcusello 

Thank you so much for replying to my question.  Lookup is not really my strong suit. Could you please explain how I can apply Match_type = WILDCARD on my existing lookup csv?

Thanks!

[gcusello]

Hi @MJA411,

in transforms.conf, you have to add this option to the lookup configuration.

You can find more infos at https://docs.splunk.com/Documentation/Splunk/8.0.5/Admin/Transformsconf#Lookup_tables

Ciao.

Giuseppe

[MJA411]

Hi @gcusello 

Thank you.

I was just thinking, wouldn't be easier and quick if I can manipulate the current fieldname by removing certain characters to match my lookup?

users field = AB-1234567

UserID field (on lookup) = 1234567

Can we use a REX command to remove "AB-" from all users values and then use the lookup values instead?

Please let me know if this is possible and what should I try to make it work.

Thanks a lot!

[gcusello · Accepted solution]

Hi @MJA411,

yes, it's surely easier extrct a part of your field to exactly match the lookup value with something like this:

| rex field=users "\w+-(?<my_users>.*)"

Ciao.

Giuseppe 

[MJA411]

Hi @gcusello 

This is superb! Worked like a charm 🙂 thank you so much!

[gcusello]

Hi @MJA411,

Good and happy splunking!

Ciao.

Giuseppe

P.S.: Karma Points are appreciated 😉