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 might need to convert strings to all uppercase or all lowercase when doing the following:

Prerequisites 

The data needed in this procedure depends on the types of events you are investigating. The data descriptors can help you decide what data is appropriate for your goal. In addition, you need:

Example

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.

To optimize the search shown below, you should specify an index and a time range.

  1. Run the following search:
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.

Result

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.

  • Was this article helpful?