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

[timyong80]

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.

The expected end result should be:

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

[FrankVl]

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]

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$"

[timyong80]

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.

[FrankVl]

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'" ]

[timyong80]

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

[FrankVl]

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?

[FrankVl]

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 🙂

[FrankVl]

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

[harsmarvania57]

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.

[FrankVl]

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.