Splunk Search

How to correlate similar field values together and adding totals?

jacobfrasca
New Member

I am trying to use a lookup we use to track usage of exceptions in one of our platforms so that we can remove unneeded exceptions as needed. In my search, I am bringing my log data in that would represent usage and taking the domain out of the email address to compare against the values in my lookup. the issue I am running into is that my log will show a domain of "test.example.com" but the exception that would be used is "*.example.com". I am looking for an elegant way to add the usage of "test.example.com" to the counter for "*.example.com". 

My lookup has headers of OBJECT, CATEGORY, USAGE. OBJECT being where the domain would go. Once I get these counters sorted I would write back to the lookup table with the new value for USAGE. 

 

Including the part of my search I am struggling with:

| eval domain=replace(emailAddress,".*?@","")
| stats count(domain) as USAGE by domain
| eval CATEGORY="domain"
| rename domain as OBJECT
| table OBJECT,CATEGORY,USAGE
| append [| inputlookup exceptions.csv]
| stats sum(USAGE) AS USAGE by OBJECT, CATEGORY

Any help is appreciated!  

 

Labels (5)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

If you set up your lookup with an extra field, called MATCH, which holds the wildcarded domain and OBJECT holds the shorter domain (that you want to group by),e.g.

OBJECTMATCHCATEGORYUSAGE
example.com*.example.comdomain1000

and set up the lookup definition for WILDCARD(MATCH), you can then do something like this

| eval domain=replace(emailAddress,".*?@","")
| stats count(domain) as USAGE by domain
| eval CATEGORY="domain"
| table domain,CATEGORY,USAGE
| lookup exceptions.csv MATCH as domain CATEGORY OUTPUT USAGE as usageFromLookup
| stats sum(USAGE) AS USAGE values(usageFromLookup) as usageFromLookup by OBJECT, CATEGORY
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...