Splunk Search

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

Engager

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

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.

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

Engager

Thank you @harsmarvania57 and @FrankVl. By following your advice, I managed to return the entries that matches Target from ReportTable against the Notes field in TicketTable. 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 ReportTable with an additional column which is the Ticket Number taken from the TicketTable?

Thanks again and cheers.

0 Karma

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

Engager

@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

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

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

Ultra Champion

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

0 Karma

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

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