Splunk Search

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

Ragate
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

somesoni2
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

Ragate
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

woodcock
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

Ragate
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

somesoni2
SplunkTrust
SplunkTrust

Try this (adding the plus sign)

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

Ragate
Explorer

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Before the where clause/command.

0 Karma

Sukisen1981
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

Ragate
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

cpetterborg
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

Ragate
Explorer

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

0 Karma

cpetterborg
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...