Splunk Search

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

MJA411
Explorer

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

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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 

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

MJA411
Explorer

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!

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

MJA411
Explorer

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!

0 Karma

gcusello
SplunkTrust
SplunkTrust

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 

0 Karma

MJA411
Explorer

Hi @gcusello 

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @MJA411,

Good and happy splunking!

Ciao.

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...