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
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.
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$"
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.
Yes, start with getting the Report_Table and then use the
join command, to combine it with what you had.
| 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'" ]
@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.
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 🙂
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.