Splunk Search

Can you help me with my wildcard search involving two columns?

timyong80
Explorer

In my Report Table, there were multiple lines of actions performed in the Active Directory. I want to take the value in a Target column and do a wild card search on the Notes column in Ticket Table for any records that match and return the Ticket Number.

For example, I want to return the Ticket Number for the Password Reset activity done on Bobby. In this particular case, I need to do a wildcard search on Ticket Table in the Notes column for any words that has "Bobby" in it.

alt text

The expected end result should be:

alt text

I am able to return the results if both the columns (Target) in Report Table and (Notes) in Ticket Table are an exact match using the syntax below. But how can I achieve the results if I want to match the columns based on wildcards? Thank you.

| inputlookup Report_Table.csv 
| lookup local=1 Ticket_Table.csv Notes AS Target OUTPUT Ticket_Number
0 Karma

FrankVl
Ultra Champion

You can use the map command for this. Something like this:

| inputlookup Report_Table.csv
| map search="|inputlookup Ticket_Table.csv | search Notes=*$user$* | rex field=Notes "(?<ticketuser>$user$)" | table ticketuser, 'Ticket Number'"

You can then join this back to the original lookup to get all the fields together.

harsmarvania57
SplunkTrust
SplunkTrust

Good approach but query which you provided didn't work in 7.1.2, however below one is working

| inputlookup Report_Table.csv
| eval Target="*" + Target + "*"
| map search="|inputlookup Ticket_table.csv | search Notes=$Target$"
0 Karma

timyong80
Explorer

Thank you @harsmarvania57 and @FrankVl. By following your advice, I managed to return the entries that matches Target from Report_Table against the Notes field in Ticket_Table. However, the table that it returned contains all the fields from Ticket_Table, just that it now contains two entries related to NA\dixon and Bobby.

I tried using | table Report_Table but it didn't work.

Do you know how can I make the end result that looks like Report_Table with an additional column which is the Ticket Number taken from the Ticket_Table?

Thanks again and cheers.

0 Karma

FrankVl
Ultra Champion

Yes, start with getting the Report_Table and then use the join command, to combine it with what you had.

So:

| inputlookup Report_Table.csv
| join type=left Target [
| inputlookup Report_Table.csv
| eval Target="*" + Target + "*"
| map search="|inputlookup Ticket_Table.csv | search Notes=$Target$ | rex field=Notes "(?<Target>$Target$)" | table Target, 'Ticket Number'" ]
0 Karma

timyong80
Explorer

@FrankVl Thank you for your efforts. Not sure why didn't worked still. It's returning me all the same contents from Report_Table.csv. I'm using ver 6.6.8 btw, not sure if it matters. Cheers.

0 Karma

FrankVl
Ultra Champion

But does the second part of the search (if you run it without the first inputlookup and join) return the list of users and their ticket number?

0 Karma

FrankVl
Ultra Champion

Ah, thanks. I tested it in 7.2, and also only tested it against indexed data, not against lookup, so it might indeed require a bit of tweaking to get it perfectly working for this particular use case. But I hope the concept at least should work 🙂

0 Karma

FrankVl
Ultra Champion

How do you plan to deal with situations where user names occur in multiple tickets?

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

Hi,

Please look at answer provided by me on this question https://answers.splunk.com/answers/596835/how-to-search-for-values-in-a-lookup-table-with-wi.html, it might help you.

0 Karma

FrankVl
Ultra Champion

That answer is for the other way around: event field contains a long string and the lookup table contains a substring with wildcards. @timyong80 wants to match usernames from an event field, to usernames embedded in note strings in a lookup field.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...