topic Re: CSV with Lookup in Getting Data In

CSV with Lookup

phanikumarcs — Wed, 10 Apr 2024 08:08:51 GMT

Hi @ITWhisperer
@gcusello @ITWhisperer please help

This is the other issue which is related to csv dataset and lookup dataset.

From this SPL: source="cmkcsv.csv" host="DESKTOP" index="cmk" sourcetype="cmkcsv"

Getting output below

Subscription	Resource	Key Vault	Secret	Expiration Date	Months
BoB-foo	Dicore-automat	Dicore-automat-keycore	Di core-tuubsp1sct	2022-07-28	-21
BoB-foo	Dicore-automat	Dicore-automat-keycore	Dicore-stor1scrt	2022-07-28	-21
BoB-foo	G01462-mgmt-foo	G86413-vaultcore	G86413-secret-foo

From this lookup: | inputlookup cmklookup.csv
Getting output below

Application	environment	appOwner
Caliber	Dicore - TCG	foo@gmail.com
Keygroup	G01462 - QA	goo@gmail.com
Keygroup	G01462 - SIT	boo@gmail.com

Combine the two queries into one, where the output will only display results where the 'environment' and 'Resource' fields match. For instance, if 'G01462' matches in both fields across both datasets, it should be included in the output. How i can do this, could anyone help here to write spl. I wrote some of the Spls but it's not working for me.

source="cmkcsv.csv" host="DESKTOP" index="cmk" sourcetype="cmkcsv"
|join type=inner [ | inputlookup cmklookup.csv environment]

source="cmkcsv.csv" host="DESKTOP" index="cmk" sourcetype="cmkcsv"
| lookup cmklookup.csv environment AS "Resource" OUTPUT "environment"

Re: CSV with Lookup

ITWhisperer — Wed, 10 Apr 2024 08:20:18 GMT

In your example, G01462 doesn't (completely) match any entry in either Resource or environment. Lookup requires an exact match (unless you define it as a wildcard lookup or CIDR). In the case of G01462-mgmt-foo, would you want the lookup to find either G01462 - QA or G01462 - SIT or both?

Re: CSV with Lookup

phanikumarcs — Wed, 10 Apr 2024 08:32:32 GMT

Indeed, the objective is to utilize a lookup operation to match 'G01462' and find either 'G01462 - QA' or 'G01462 - SIT', or both.

Alternatively, can I modify the lookup operation to precisely match the "newResource" field with the "Resource" field to retrieve the corresponding values of the "environment" field in the

Application	environment	appOwner	newResource
Caliber	Dicore - TCG	foo@gmail.com	Dicore-automat
Keygroup	G01462 - QA	goo@gmail.com	Dicore-automat
Keygroup	G01462 - SIT	boo@gmail.com	G01462-mgmt-foo

Re: CSV with Lookup

ITWhisperer — Wed, 10 Apr 2024 08:43:00 GMT

Do that then!