How do I combine two data sources and get rid of repeated IDs?

[Ragate]

Currently I have two data sources with different names for the same IDs. One is called License Key Identifier and the other is called LicenseKeyID.
As of right now I have this:

source="c:\\users\\ragate\\desktop\\splunk\\jsondump.txt" | join type=left substr('context.custom.dimensions{}.LicenseKey',4,7) [search source="C:\\Users\\ragate\\Desktop\\splunk\\LMCustomerRevLicense.csv"] | eval LicenseKeyID=substr('context.custom.dimensions{}.LicenseKey',4,7) |

It only pulls from the bottom row of the excel sheet for the License Key Identifiers but gives me the correct LicenseKeyID. How do I get it to pull all the rows for the License Key Identifiers and then get rid of the ones that do not match up with the LicenseKeyID?

[woodcock]

Did you really ask this question 3 times?

[DalJeanis]

So, first, you have two files you are trying to compare.

index="foo"  source="c:\\users\\ragate\\desktop\\splunk\\jsondump.txt" 
| eval JSONKey = substr('context.custom.dimensions{}.LicenseKey',4,7) 
| fields ...list the fields you want to keep...
| head 100

Verify that the above is extracting correctly.

index="foo" source="C:\\Users\\ragate\\Desktop\\splunk\\LMCustomerRevLicense.csv
| eval LMCKey = substr('context.custom.dimensions{}.LicenseKey',4,7) 
| fields ...list the fields you want to keep...
| head 100

Verify that the above is extracting correctly.

---

If either of the above fails, then please give us a non-confidential version of the code that correctly extracts the file, and please give the full list of fields you care about in each file.

---

If both work, then something like this uses stats (rather than join) to combine them....

index="foo"  (source="c:\\users\\ragate\\desktop\\splunk\\jsondump.txt"  OR
    source="C:\\Users\\ragate\\Desktop\\splunk\\LMCustomerRevLicense.csv") 
| eval rectype=if(source="c:\\users\\ragate\\desktop\\splunk\\jsondump.txt", "JSONKey","LMCKey")
| eval matchKey = substr('context.custom.dimensions{}.LicenseKey',4,7) 
| fields ...list the fields you want to keep from EITHER file...
| stats values(*) by matchKey