topic Re: How do I merge two fields together and get rid of what does not match? in Splunk Search

How do I merge two fields together and get rid of what does not match?

Ragate — Thu, 28 Jun 2018 15:54:34 GMT

Hi. I have two sources that I am trying to merge and dedup similar data. They both have a license key, one was longer than the other but had the main 7 variables that I used substr to make them both the same. Currently the syntax looks like this:

source="LMCustomerRevLicense.csv" OR source="C:\\Users\\ragate\\Desktop\\splunk\\JsonDump.txt" Dykema | eval LicenseKeyID=substr('context.custom.dimensions{}.LicenseKey',4,7) | dedup "Account Name" | table "LicenseKeyID" "Account Name" "context.custom.dimensions{}.DocumentSessionId" | where "License Key Identifier"="LicenseKeyID"

I want to merge these two license keys together to then be able to have everything else in the csv file will be able to be sorted with this key and anything that does not match with the one I used substr for is removed from the data. This might seem a little confusing so let me know if I need to explain a little better. I am new to splunk if that hasn't been made obvious already.

Thanks in advance.

Re: How do I merge two fields together and get rid of what does not match?

Ragate — Thu, 28 Jun 2018 16:00:03 GMT

Like to clarify that Dykema is there only because I was using that company to try to see if it would work. Currently, this code does not produce anything for me. Just is the closest I can get.

Re: How do I merge two fields together and get rid of what does not match?

cpetterborg — Thu, 28 Jun 2018 20:49:29 GMT

I'm confused by some of the things in your search. What do you get from this search:

source="LMCustomerRevLicense.csv" OR source="C:\\Users\\ragate\\Desktop\\splunk\\JsonDump.txt"  
| dedup "Account Name" 
| eval LicenseKeyID=substr('context.custom.dimensions{}.LicenseKey',4,7) 
| table LicenseKeyID "Account Name" "context.custom.dimensions{}.DocumentSessionId"

Do your results have the correct values in the table?

Re: How do I merge two fields together and get rid of what does not match?

Ragate — Thu, 28 Jun 2018 20:55:33 GMT

No I do not get the correct values. The LicenseKeyID field is left blank.

Re: How do I merge two fields together and get rid of what does not match?

cpetterborg — Thu, 28 Jun 2018 20:57:54 GMT

So your eval is incorrect. What values are in context.custom.dimensions{}.LicenseKey?

Re: How do I merge two fields together and get rid of what does not match?

Sukisen1981 — Sat, 30 Jun 2018 12:29:25 GMT

Try This -

     source="LMCustomerRevLicense.csv" |  dedup "Account Name" | table  "License Key Identifier","Account Name", "context.custom.dimensions{}.DocumentSessionId" | join "License Key Identifier" [append search source="C:\\Users\\ragate\\Desktop\\splunk\\JsonDump.txt"|eval LicenseKeyID=substr('context.custom.dimensions{}.LicenseKey',4,7) | dedup "Account Name" | table  "License Key Identifier", "Account Name","context.custom.dimensions{}.DocumentSessionId"| rename LicenseKeyId as  "License Key Identifier"]

You are performing a straight dedup on "Account Name" ,is that present in both the sources? If not remove it from the table command where "Account Name" is not present from the above query

Re: How do I merge two fields together and get rid of what does not match?

woodcock — Sat, 30 Jun 2018 15:56:06 GMT

I believe that the heart of your problem is normalizing the license field. You should probably not be using substr; try this:

| rex field=license_field_name mode=sed "s/[^\-]\-// s/\-.*//"

This will work on field values that need to be modified and will be harmless on those that don't.

Re: How do I merge two fields together and get rid of what does not match?

Ragate — Mon, 02 Jul 2018 19:09:10 GMT

Currently have the License Field as: CC-301TJFQ-ST0-X-Q3332

I am not good with regex but the one you gave me gives me C301TJFQ

I need it to be 301TJFQ

Anyway you could help me with that?

Re: How do I merge two fields together and get rid of what does not match?

somesoni2 — Mon, 02 Jul 2018 19:52:43 GMT

You're comparing two fields "License Key Identifier" and "LicenseKeyID". Do both exist in all the events (in other words, do both exist in both CSV and TXT file data?)

Re: How do I merge two fields together and get rid of what does not match?

Ragate — Mon, 02 Jul 2018 19:56:54 GMT

Yes, In the LicenseKeyID I parsed out unnecessary digits to get the raw License Key Identifier. From there, both exist in the TXT and CSV.

Re: How do I merge two fields together and get rid of what does not match?

somesoni2 — Mon, 02 Jul 2018 19:59:53 GMT

Try this (adding the plus sign)

 | rex field=license_field_name mode=sed "s/[^\-]+\-// s/\-.*//"

Re: How do I merge two fields together and get rid of what does not match?

Ragate — Mon, 02 Jul 2018 20:05:57 GMT

Thank You, That worked. Where would be the correct places to add this into my original query?

Re: How do I merge two fields together and get rid of what does not match?

somesoni2 — Mon, 02 Jul 2018 20:10:47 GMT

Before the where clause/command.

Re: How do I merge two fields together and get rid of what does not match?

woodcock — Thu, 05 Jul 2018 16:32:13 GMT

Be sure to click Accept to close the question and let others find working answers, @Ragate.