Standardize Values And Combine Into Single Field

Standardize values using the upper() and lower() functions and combine into single field with concat()

Query

logscale

lower(#severity, as=severity)
 
| upper(#category, as=category)
 
| concat([severity, category], as=test)
 
| top(test)

Introduction

The lower() function is used to format a string in lower case, and the upper() function is used to format a string in upper case.

The lower()/upper() functions return a duplicate of an original string with all characters in lower case/upper case.

Standardizing the format of fields is useful for consistent analysis. In this example, upper() and lower() functions are used with concat() to concatenate the fields #category and severity, where one field's result is all lower case letters and the other field's results are all upper case letters.

If no as parameter is set, the fields outputted to is by default named _upper and _lower, respectively.

In this query, the as parameter is used for the lower() and upper() functions to label their results. These output fields (category and severity) are then used with the concat() function, returning the concatenated string into a field named test. Finally, it uses the top() function, to show which combinations of severity and category are most common in the data.

Step-by-Step

Starting with the source repository events.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0>Augment Data] 1>Augment Data] 2>Augment Data] 3{{Aggregate}} result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> result style 0 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
lower(#severity, as=severity)
```
Converts the values in the #severity field to lower case and returns the results in a field named severity.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0>Augment Data] 1>Augment Data] 2>Augment Data] 3{{Aggregate}} result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| upper(#category, as=category)
```
Converts the values in the #category field to upper case and returns the results in a field named category.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0>Augment Data] 1>Augment Data] 2>Augment Data] 3{{Aggregate}} result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| concat([severity, category], as=test)
```
Concatenates (combines) the values in field category and field severity, and returns the concatenated string in a new field named test.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0>Augment Data] 1>Augment Data] 2>Augment Data] 3{{Aggregate}} result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> result style 3 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| top(test)
```
Finds the most common values of the field test — the top of an ordered list of results - along with their count. The result of the count of their occurrences is displayed in a field named _count.
Event Result set.

Summary and Results

The query is used to standardize the format of the values in the fields #category and severity and concatenate the values into a single field, showing which combinations of severity and category are most common in the data.

The specific labeling of category and severity is particularly useful when you have more than one field that use the same query function.

By converting fields to consistent cases, it helps standardize data for easier analysis and comparison. The concatenation allows you to combine multiple fields into a single field, which can be useful for creating unique identifiers or grouping related information. It provides a quick overview of the distribution of events across different severity-category combinations.

Sample output from the incoming example data (showing the first 10 rows only):

test	_count
infoALERT	90005
infoFILTERALERT	36640
errorALERT	17256
warningGRAPHQL	14240
warningALERT	13617
warningSCHEDULEDSEARCH	11483
infoSCHEDULEDSEARCH	5917
warningFILTERALERT	1646
errorFILTERALERT	1487
infoACTION	3