Create Frequency Count With Formatted Links

Transform field values into clickable links with occurrence count using the top() function with format()

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1{{Aggregate}} 2>Augment Data] result{{Result Set}} repo --> 1 1 --> 2 2 --> result

logscale

top(repo)
| format("[Link](https://example.com/%s)", field=repo, as=link)

Introduction

The top() function can be used to count occurrences of field values and sort them by frequency, providing insights into the most common values in your data.

In this example, the top() is used to count occurrences of repository names in the field repo, followed by the format() function to create clickable links for each repository.

Example incoming data might look like this:

@timestamp	repo	action	user
2023-06-15T10:00:00Z	frontend-app	push	alice
2023-06-15T10:05:00Z	backend-api	clone	bob
2023-06-15T10:10:00Z	frontend-app	pull	charlie
2023-06-15T10:15:00Z	database-service	push	alice
2023-06-15T10:20:00Z	frontend-app	pull	bob
2023-06-15T10:25:00Z	backend-api	push	alice
2023-06-15T10:30:00Z	monitoring-tool	clone	charlie
2023-06-15T10:35:00Z	frontend-app	push	bob

Step-by-Step

Starting with the source repository events.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1{{Aggregate}} 2>Augment Data] result{{Result Set}} repo --> 1 1 --> 2 2 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
top(repo)
```
Groups events by the repo field and counts their occurrences. Creates a result set with two fields: the repository name (repo) and _count. Results are automatically sorted by count in descending order. If no limit is specified, the top() function returns all unique values.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1{{Aggregate}} 2>Augment Data] result{{Result Set}} repo --> 1 1 --> 2 2 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| format("[Link](https://example.com/%s)", field=repo, as=link)
```
Creates formatted markdown-style links based on repository values in repo and returns the results in a new field named link.
The field parameter specifies to use the repo field value in the formatting string (represented by %s).
Event Result set.

Summary and Results

The query is used to analyze the frequency of repository interactions and create clickable links for each repository.

This query is useful, for example, to create interactive reports showing which repositories are most actively used, or to build dashboards where users can quickly access frequently accessed repositories.

Sample output from the incoming example data:

repo	_count	link
frontend-app	4	[Link](https://example.com/ frontend-app)
backend-api	2	[Link](https://example.com/ backend-api)
monitoring-tool	1	[Link](https://example.com/ monitoring-tool)
database-service	1	[Link](https://example.com/ database-service)

Note that the results are automatically sorted by count in descending order, showing the most frequently accessed repositories first. The original field value is preserved in the repo field while the formatted link is available in the link field.

Examples Library