Splunk Search

How to append the field by matching two different data source

ninadbhaskarwar
Path Finder

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
alt text

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

ninadbhaskarwar
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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

somesoni2
SplunkTrust
SplunkTrust

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

ninadbhaskarwar
Path Finder

Can you please help me to make this case insensitive

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

case_sensitive_match = false
0 Karma

ninadbhaskarwar
Path Finder

Thanks it worked

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 ...