Skip to main content
Splunk Lantern

String conversion to a common format

When correlating events with records in lookup tables—for example, lists of domains—differences in capitalization or other text formatting standards can cause problems in your results. You want to compare DNS records returned from a search with a list of whitelisted domain names in a lookup table, but the two sources use different capitalization standards.

Data required 

Stream DNS data

Procedure

Run the following search. You can optimize it by specifying an index and adjusting the time range.

sourcetype=stream:dns record_type=A
|rename query{} AS query
| search query=*
| eval upperDomainEvent=upper(query)
| lookup <name of file>.csv domain AS upperDomainEvent OUTPUTNEW domain
| search NOT(domain=*)
| stats count by upperDomainEvent

Search explanation

The table provides an explanation of what each part of this search achieves. You can adjust this query based on the specifics of your environment.

Splunk Search Explanation

sourcetype=stream:dns 

Search only Stream DNS data.

record_type=A

Search only DNS A records, which return IPv4 addresses.

|rename query{} AS query

Rename the field as shown for better readability.

| search query=*

Filter the results to only include logs with a value in the query field.

| eval upperDomainEvent=upper(query)

Create a new field called upperDomainEvent that displays the values in the query field in uppercase letters

| lookup <name of file>.csv domain AS upperDomainEvent OUTPUTNEW domain

Retrieve values from the csv lookup file, equating the domain and UpperDomainEvent fields. Output only the domain field results and put them into new records instead of overwriting existing values.

| search NOT(domain=*)

Filter the results to return only those that are not in the csv lookup file.

| stats count by upperDomainEvent  

Group results by upperDomainEvent and show how many times each value appears.

Next steps

This conversion to all uppercase letters allows your Splunk search to compare the domains returned by the stream:dns data to those in the lookup table so that you can quickly eliminate whitelisted domains from your search results.

You might also be interested in other processes associated with the Monitoring command line interface actions and Monitoring employee network traffic use cases.