Splunk Search

How to filter multiple regex condition and create column on basis of condition?

Explorer

I have a big table of many fields. From there I want to mark result. I have a lookup conditions, if those conditions get matched then only mark them this is the logic. Example -

Say I have this data in tabular format -

+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+
| IP           |  Location  |  Address           |  UserName  |  Email                   |  Pincode  |  Phn       |  Message             |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+
| 22.33.4.5    |  UK        |  22 Baker Street   |  SHolmes   |  holmes1@elementary.com  | 450001    | 8981399334 |  Dear watson         |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+
| 22.45.12.44  |  US        |  45 R Street       |  JimMo2    |  moes2@gmail.com         | 340003    | 7533342190 |  Hello there         |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+
| 12.3.54.5    | UK         | 34 El Road         | Jesr       | gh.row@splunk.com        | 443434    | 2332435543 | Destorying the world |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+
| 34.54.3.54   | RUS        | 23 RR Street       | SuperMol   | mol23@splunk.com         | 230002    | 4567575743 | Hey there            |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+
| 214.53.3.53  | YUG        | 45 Morisson Street | Mrxtz      | mol23@splunk.com         | 2300043   | 6767556743 | Marry her            |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+

Now I have this lookup -

+-----------+------------+------------+-----------+--------------+
| Address   |  UserName  |  Email     |  Message  |  Type        |
+-----------+------------+------------+-----------+--------------+
| *Street*  |  *         |  *gmail*   |  *        |  StreetGmail |
+-----------+------------+------------+-----------+--------------+
| *         |  *         |  *splunk*  |  *        |  Splunk      |
+-----------+------------+------------+-----------+--------------+
| *         |  *         |  *         | *Destroy* |  Banned      |
+-----------+------------+------------+-----------+--------------+
| *         |  *Super*   |  *splunk*  |  *        |  SplunkSuper |
+-----------+------------+------------+-----------+--------------+

So as per this lookup if a resultset matches with the condition then create a new column Type and map it to the table. So our original table becomes something like this -

+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+
| IP           |  Location  |  Address           |  UserName  |  Email                   |  Pincode  |  Phn       |  Message             | Type        |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+
| 22.33.4.5    |  UK        |  22 Baker Street   |  SHolmes   |  holmes1@elementary.com  | 450001    | 8981399334 |  Dear watson         | Other       |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+
| 22.45.12.44  |  US        |  45 R Street       |  JimMo2    |  moes2@gmail.com         | 340003    | 7533342190 |  Hello there         | StreetGmail |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+
| 12.3.54.5    | UK         | 34 El Road         | Jesr       | gh.row@splunk.com        | 443434    | 2332435543 | Destorying the world | Banned      |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+
| 34.54.3.54   | RUS        | 23 RR Street       | SuperMol   | mol23@splunk.com         | 230002    | 4567575743 | Hey there            | SplunkSuper |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+
| 214.53.3.53  | YUG        | 45 Morisson Street | Mrxtz      | mol23@splunk.com         | 2300043   | 6767556743 | Marry her            | Other       |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+

Which means if the condition doesn't match mark it as Other. I think the output is pretty self-explanatory. But if someone need explanation I can give it too.

I am not sure what to use, I was checking with inputlookup but I am not sure that's the correct thing to use.

0 Karma
1 Solution

First step will be to take the data in your lookup and make sure you've defined it as a wildcard lookup. You can do that by creating a Lookup in the Splunk Web UI, specifying the csv as the Lookup file and then putting this into the field Match type: WILDCARD(Address), WILDCARD(UserName), WILDCARD(Email), WILDCARD(Message). Let's say you called the lookup get_type, so that I can refer to it below.

Next, you'll use that lookup in your search. You'll do that like this:

your base search that gives you the first table above
| lookup get_type Address, UserName, Email, Message OUTPUT Type

Then you'll fill in the Type field for events that had no matches:

| fillnull value="Other" Type

View solution in original post

First step will be to take the data in your lookup and make sure you've defined it as a wildcard lookup. You can do that by creating a Lookup in the Splunk Web UI, specifying the csv as the Lookup file and then putting this into the field Match type: WILDCARD(Address), WILDCARD(UserName), WILDCARD(Email), WILDCARD(Message). Let's say you called the lookup get_type, so that I can refer to it below.

Next, you'll use that lookup in your search. You'll do that like this:

your base search that gives you the first table above
| lookup get_type Address, UserName, Email, Message OUTPUT Type

Then you'll fill in the Type field for events that had no matches:

| fillnull value="Other" Type

View solution in original post

Explorer

This works fine but the problem arises when there is a case where Address is blank. Then the OUTPUT logic doesn't help. It giving output as Other. Can you help me here?

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!