topic Re: How to append the field by matching two different data source in Splunk Search

How to append the field by matching two different data source

ninadbhaskarwar — Tue, 29 Sep 2020 14:37:07 GMT

I have one Source =”ABC.csv” and a lookup “a_alert”.

ABS.csv contains fields such as ID, Description (200 free character field)

a_alert contain one field a_type e.g. Monitoring, Access, Deployment

I need a query which can match Description from ABC.csv to a_type from a_alert and create the append a field in the ABC.csv
Final output should be

ID Description a_type
1 User having Access Issue Access
2 …. Monitoring ……. Monitoring
3 … product having successful deployment Deployment
4 Access Issue with .. type of Users Access

Re: How to append the field by matching two different data source

somesoni2 — Tue, 29 Sep 2020 14:32:47 GMT

Assuming the word in the lookup a_type (e.g. Access, Monitoring etc) contained in the Description field in your data from source=ABC.csv, you would need to setup a lookup table with wildcard.

Everything on Search Head, will need to restart Splunk after config change.
Step1: Update your lookup table a_alert to include wildcard characters.

a_type   ---this is the header
*Access*
*Monitoring*
*Deployment*
...

Step2: (assuming lookup table a_alert.csv is already uploaded as lookup) Create a lookup transform with wild card mat
transforms.conf on any app/local directory in $Splunk_home/etc/apps

[alert_lookup]
 filename = a_alert.csv
 match_type = WILDCARD(a_type)

Step 2: add lookup command to your search

your base search giving field ID Description
| lookup alert_lookup a_alert as Description OUTPUT a_type

Reference:
https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html

Re: How to append the field by matching two different data source

woodcock — Wed, 21 Jun 2017 00:09:04 GMT

You have a source of ABC.csv that looks like this:

ID (integer), Description (200 free character field)

You have a lookup called a_alert that contains one field like this:

a_type
Monitoring
Access
Deployment

You need a query which can match Description from ABC.csv to a_type from a_alert and create/append a_field so that the final output is like this:

ID    a_type        Description
1     Access        User having Access Issue
2     Monitoring    …. Monitoring …….
3     Deployment    … product having successful deployment
4     Access        Access Issue with .. type of Users

You need to change your a_alert lookup to a wildcard (and possibly case_insensitive, if you'd like that, too); there is a special setting in transforms.conf to enable this. Then add bounding asterisks by doing this ONLY ONCE:

| inputlookup a_alert | eval a_type = "*" . a_type . "*" | outputlookup a_alert

Now you have this:

a_type
*Monitoring*
*Access*
*Deployment*

Finally, you do your search like this:

| inputcsv ABC.csv
| lookup a_alert a_type AS Description OUTPUT a_type
| rex field=a_type mode=sed "s/^\*// s/\*$//"
| table ID a_type Description

Re: How to append the field by matching two different data source

ninadbhaskarwar — Tue, 29 Sep 2020 14:37:51 GMT

Both of above worked for me, only change I did for solution provided by @somesoni2 i.e.

your base search giving field ID Description
|lookup alert_lookup a_type as Description OUTPUT a_type

instead of a_alert I have used a_type which is given by @woodcock .Also I have used regex from @woodcock

Thanks to @somesoni2 and @woodcock for providing quick solution

Re: How to append the field by matching two different data source

ninadbhaskarwar — Wed, 21 Jun 2017 20:41:23 GMT

Can you please help me to make this case insensitive

Re: How to append the field by matching two different data source

somesoni2 — Wed, 21 Jun 2017 20:48:02 GMT

Add this to your lookup definition in transforms.conf (under [alert_lookup])

case_sensitive_match = false

Re: How to append the field by matching two different data source

ninadbhaskarwar — Thu, 22 Jun 2017 04:21:13 GMT

Thanks it worked