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

[anirban_nag]

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   |  [email protected]  | 450001    | 8981399334 |  Dear watson         |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+
| 22.45.12.44  |  US        |  45 R Street       |  JimMo2    |  [email protected]         | 340003    | 7533342190 |  Hello there         |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+
| 12.3.54.5    | UK         | 34 El Road         | Jesr       | [email protected]        | 443434    | 2332435543 | Destorying the world |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+
| 34.54.3.54   | RUS        | 23 RR Street       | SuperMol   | [email protected]         | 230002    | 4567575743 | Hey there            |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+
| 214.53.3.53  | YUG        | 45 Morisson Street | Mrxtz      | [email protected]         | 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   |  [email protected]  | 450001    | 8981399334 |  Dear watson         | Other       |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+
| 22.45.12.44  |  US        |  45 R Street       |  JimMo2    |  [email protected]         | 340003    | 7533342190 |  Hello there         | StreetGmail |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+
| 12.3.54.5    | UK         | 34 El Road         | Jesr       | [email protected]        | 443434    | 2332435543 | Destorying the world | Banned      |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+
| 34.54.3.54   | RUS        | 23 RR Street       | SuperMol   | [email protected]         | 230002    | 4567575743 | Hey there            | SplunkSuper |
+--------------+------------+--------------------+------------+--------------------------+-----------+------------+----------------------+-------------+
| 214.53.3.53  | YUG        | 45 Morisson Street | Mrxtz      | [email protected]         | 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.

[elliotproebstel · Accepted 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

[anirban_nag]

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?