Splunk Search

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

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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?)

0 Karma

Explorer

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.

0 Karma

Esteemed Legend

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.

0 Karma

Explorer

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?

0 Karma

SplunkTrust
SplunkTrust

Try this (adding the plus sign)

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

Explorer

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

0 Karma

Esteemed Legend

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

0 Karma

SplunkTrust
SplunkTrust

Before the where clause/command.

0 Karma

Champion

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

0 Karma

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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?

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma