topic Re: Comparing input lookup table to index in Splunk Search

Comparing input lookup table to index?

hantun — Tue, 28 Mar 2023 14:14:28 GMT

Hello -

I am looking to match an uploaded lookup table in csv format to the indexes we have. I am running into problems since the column I want to match in the index is not parsed. I have two questions:

1. Can we parse in splunk to extract the numbers and words we need? If so, what is the resource I need or how do I parse correctly?

2. I am looking to match a column in my lookup table to the parsed data in the index. We have different indexes and we need to look all of them up with the same lookup table csv. What I have so far is this, do we need eval command?

index=guardduty | [ |inputlookup CostCentersandAWSAccounts.csv | search AccountId=Title | fields Business ]

Re: Comparing input lookup table to index

bowesmana — Tue, 28 Mar 2023 04:32:18 GMT

Yes, you can parse/extract in Splunk

rex is your friend

https://docs.splunk.com/Documentation/Splunk/9.0.3/SearchReference/Rex

So, it looks like you are trying to look for any rows in your lookup where AccountId has the value "Title" and then pass the Business field from the lookup as a constraint to the index=guardduty. You don't need the first | (pipe) symbol before the subsearch BTW.

index=guardduty | [ |inputlookup CostCentersandAWSAccounts.csv | search AccountId=Title | fields Business ]

so, if the guardduty data does not have a field called Business, but has something, e.g. BusinessName, then you will have to

index=guardduty [ | inputlookup CostCentersandAWSAccounts.csv where AccountId=Title | fields Business | rename Business as BusinessName ]

Note the use of 'where AccountId=Title' which pre-filters rather than post-search.

If you mean that there is no Business field yet extracted that you want to match against the Business field in the lookup, then you have to do either a or b

a. Create a new field extraction or a calculated field that creates a Business field for all indexes you want to match and then you can search like above.

b. Do a rex extraction and then do a lookup against the lookup file, e.g.

index=guardduty | rex "(?<Business>....regex_to_extract_business...)" | eval AccountId="Title" | lookup CostCentersandAWSAccounts.csv AccountId Business

Re: Comparing input lookup table to index

hantun — Tue, 28 Mar 2023 19:38:53 GMT

I got up to this:

index=csmp

| rex field=Title "^CSMP\s-\s(?<BindleName>\w+)\s-\s([a-zA-Z0-9 ]*)$"

| eval BindleName=Title

| lookup CostCentersandAWSAccounts.csv Business BindleName

I am confused why it is not returning the BindleName which is what I am parsing and comparing to the Lookup table's column named BindleName. Once the comparison is made, I want the search to return the related row of the Business column from the Lookup table. I feel like I am missing something big here...

Re: Comparing input lookup table to index

bowesmana — Tue, 28 Mar 2023 22:29:41 GMT

OK, so it's all about your field names.

In your original search you said AccountId=Title, which is looking for a text value of Title in AccountId field, whereas here you are setting BindleName field to the same value as the Title field, which where the confusion comes in.

But in your example here you are saying you want to find both the Business field AND the BindleName field from the lookup.

Given your description you would probably want something like

index=csmp | rex field=Title "^CSMP\s-\s(?<BindleName>\w+)\s-\s([a-zA-Z0-9 ]*)$" | lookup CostCentersandAWSAccounts.csv BindleName OUTPUT Business

which is saying

a) Extract a field called BindleName from the Title field
b) Lookup the BindleName field against the same named column in the lookup and OUTPUT the Business field from the lookup

Note - when posting searches, use the code block </> to format the SPL for easy reading, as above

Hope this helps