ConnectiLogs Panel | Query Generator

ConnectiLogs Panel | Query Generator

The query builder is a tool that allows you to explore your ConnectiLogs data in a customized way, without needing to use code or technical language. With it, you can create filters, combine conditions, visualize results with different types of charts or tables, and save your queries to analyze them whenever you need within a dashboard.

Its goal is for you to find exactly the information you are looking for within your logs, whether to review accesses, analyze threats, or investigate a specific behavior in your service.

How it works step by step 

1. Select the data source 
The first step to creating a query in ConnectiLogs is to define where you want to get the information from. To organize this choice, ConnectiLogs uses three levels: Category, Source, and Collection.

Category 
The category defines the area or type of service on which you want to query data. Each category groups a set of related collections.

Some examples of available categories are:
  1. Web: includes everything related to websites, such as accesses, errors, malware activity, or file changes (ftp, ssh).
  2. Email: groups email activity records, such as sends, receives, accesses, etc.
  3. Control panel: collects information about actions performed within the user panel, such as login, configuration changes, or service management.


Source 
Within each category, you must select the source of the data, i.e., which specific object you want to get information about. The source allows you to select a domain, a server, or a particular user, depending on the type of data you are analyzing.

Examples of source:
  1. Domain: filters information for a specific website.
  2. Server: analyzes records associated with a complete server.
Note: each category will have a different list of sources available, which adapts to the chosen type.



Collection 
Finally, you must select the collection, which represents the final source of the data within ConnectiLogs. The collection is where the specific records you are going to analyze are located, and each contains the information according to the selected category and source.

Some examples of collections:
  1. Domain malware: list of files detected as malicious on a specific domain.
  2. Session accesses: record of all connections made by a user in the control panel.
  3. User actions: events such as configuration changes, creation or deletion of resources, or permission modifications.



Once this selection is complete, you must choose the data visualization period. In the free version of ConnectiLogs, data is limited to 30 days during its beta phase and to 7 days with general availability of the application.

2. Explore the data (optional) 
Once you have selected the collection and entered a valid date range, ConnectiLogs allows you to explore the data before starting to build your query. This step is optional but highly recommended, especially if you are not familiar with the collection you chose or want to make sure you are searching for the right thing.

Exploring the data gives you a preview of the records and helps you understand what information is available, how it is organized, and what values you could use to filter your results.

What can you do when exploring the data? 
  1. See what fields the collection contains: Each collection has different fields with specific information.
  2. Apply filters: If needed, you can apply filters to further segment the information and get more specifically to the data you need.


  1. Export: Do you like what you see and think it could be useful? You can export the data very quickly in CSV format to work on your own device offline. Keep in mind that if the collection contains a lot of data, downloading the collection data is limited to 100,000 lines for performance reasons.


Practical tips: 
  1. Use it if it's your first time with a collection: it will help you understand the data structure.
  2. Don't worry about the number of records: only a representative sample is shown, enough to guide you.
  3. Combine it with filters: exploration will only show data related to those applied filters.
PRO TIP:
Hover your mouse over any of the collection's columns to see a brief description of what it is, to understand the data even better:


3. Query builder 
After selecting the data source and, if you wish, exploring the records to better understand the collection, it's time to build the query and visualize the results. ConnectiLogs allows you to do this flexibly, choosing how you want to see the information and applying aggregations and filters intuitively.

3.1. Choose the visualization type 
First of all, you must decide how you want to see the results of your query. ConnectiLogs offers several formats:
  1. Table: shows all records in detail, without aggregations.
  2. Aggregated table: groups data by a field and allows applying operators on another dimension.
  3. Pivot table: allows reorganizing and comparing data across multiple dimensions in table format.
  4. Bar chart: A visual representation of data using rectangular bars whose length is proportional to the values they represent.
  5. Line chart: A chart that connects data points with straight line segments to show trends and changes in a variable across values.
  6. Area chart: A type of chart similar to the line chart, but whose values change with respect to a time dimension.
  7. Pie chart: an alternative to the bar chart, it allows visualizing the quantities or magnitudes of various categories or numerical variables.

3.1.1 Dimensions and visualization types. 
Depending on the type of visualization you choose, ConnectiLogs will show a series of drag-and-drop boxes. These boxes are the dimensions that come by default based on the chart type, so you don't have to worry about knowing which fields to add. Depending on the chosen chart or table type, it allows you to add fields across up to three different dimensions to organize the data.


Tables 
Tables are the simplest and most direct way to visualize data in ConnectiLogs. They are designed to display information in a list format, where each row represents a record and each column a field of that record.

They are especially useful when you need to see the complete detail of the data, without aggregations or transformations.

How do they work?
When you select a simple table, ConnectiLogs displays the records as they exist in the collection. That is:
  1. Data is not grouped
  2. No automatic calculations are applied
  3. Each row corresponds to an actual line of the log itself (e.g., an access, an action, or a malware detection)
You decide which columns you want to view, selecting the fields that interest you.

What can you do with a table?
  1. View raw data: ideal for inspecting specific events
  2. Sort columns: you can organize the results by a field (e.g., date, IP, user)
  3. Apply filters: to reduce the amount of data and focus on what's important
  4. Manually explore patterns: detect suspicious or repetitive behaviors
Table limits
To ensure good performance and reasonable load times, tables have certain limitations:
  1. Number of visible records: the table shows a maximum of 100,000 rows (not all data if there are large volumes), for application performance reasons.
  2. No aggregations: does not allow applying operators like Count, Sum, or Average directly

These limitations do not affect the analysis, but rather how the data is presented on screen.

When to use a table?
The table is the best option when:
  1. You want to see the exact detail of the records
  2. You are investigating a specific case (e.g., a suspicious IP or a specific error)
  3. You need to validate information before applying aggregations
  4. You are exploring a collection for the first time
Recommendation
Always start with a table when you're not sure what you're looking for and it's a collection with so many columns that the data explorer might overwhelm you. Once you understand the data, you can move on to more advanced visualizations (like aggregated tables or charts) to draw faster conclusions.

Aggregated table 
The aggregated table is a more advanced step compared to the simple table. Instead of showing raw data, it allows summarizing and grouping information to get a clearer and more useful view.

It is one of the most used visualizations when you want to answer questions like: how many times does something happen?, which element has the most activity?, or how is the data distributed?

How does it work?
In an aggregated table, data is organized based on two main elements:
  1. Group by: the field by which data is grouped (e.g., IP, domain, path, user...)
  2. Value to aggregate: the calculation applied to the records (e.g., count, sum, average, etc.)
Instead of seeing each individual record, you will see one row for each unique value of "Group by".

Simple example
If you have thousands of accesses to your website:
  1. With a simple table, you would see each access one by one
  2. With an aggregated table → you can see something like:

IP
Number of accesses
192.168.1.1
150
80.23.45.10
98
10.0.0.5
76

Here you are grouping by IP and applying a Count.

In some cases, you can add more than one dimension to make more specific groupings (e.g., IP + path), although this increases the complexity of the result and loading time.

Aggregated tables always work with aggregation operators. If you need more information about aggregation values, you can go to the next section in this guide.

Aggregated table limits
  1. Number of visible records: the table shows a maximum of 10,000 rows (not all data if there are large volumes), for application performance reasons.
  2. As many values to group by as you need
  3. 1 single value to aggregate
When to use an aggregated table?
  1. You want to summarize information instead of viewing raw data
  2. You need to count, compare, or measure data
  3. You are looking to quickly identify what stands out within a dataset
  4. You are preparing information for analysis or dashboards
Recommendation
Use the aggregated table when you already understand the data (for example, after using a simple table) and want to draw quick conclusions. It is a tool to transform complex data into information.

Pivot table 
It is a more advanced visualization than the aggregated table. It allows analyzing data across multiple dimensions simultaneously, cross-referencing information to get a much more complete view.

It is especially useful when you want to answer questions like: how are two or more variables related?, what happens in different data combinations?, or how is something distributed across various levels?

How does it work?
In a pivot table, data is organized into three main elements:
  1. Rows: first dimension by which data is grouped (e.g., IP, domain, user...)
  2. Columns: second dimension that is crossed with the previous one (e.g., status code, event type, country...)
  3. Value to aggregate: the calculation applied to the records (Count, Sum, Average, etc.)
The result is a table where each cell represents a combination of rows and columns.

Simple example
If you have access records to your website:
  1. Rows: IP
  2. Columns: status code (200, 404, 500...)
  3. Value to aggregate: Count
The result would be something like:

IP
200
404
500
192.168.1.1
120
20
10
80.23.45.10
90
5
3
10.0.0.5
70
4
2

This allows you to see not only how many accesses each IP has, but also what type of responses each generates.

What can you do with a pivot table?
  1. Cross data from different dimensions
  2. Compare behaviors across categories
  3. Detect complex patterns (e.g., errors by country or attacks by type)
  4. Analyze more complete distributions

Pivot table limits
  1. Number of visible records: the table shows a maximum of 10,000 combinations, for performance reasons
  2. Limited dimensions:
    1. 2 dimensions in rows
    2. 2 dimensions in columns
  3. 1 single value to aggregate
  4. Higher resource consumption: may take longer to load if there are many possible combinations (high cardinality), and may even fail to execute the query
When to use a pivot table?

It is the best option when:
  1. You need to analyze relationships between two variables
  2. You want to compare data across multiple axes (e.g., time vs event type)
  3. You are looking to go beyond simple counting or grouping
  4. You are building more complete analyses for dashboards

Recommendation
Use the pivot table when the aggregated table falls short. It is a very powerful tool for more advanced analyses, but it's best used when you already know what you want to compare, since improper use (too many combinations) can make results difficult to interpret.

IMPORTANT: HIGH CARDINALITY IN PIVOT TABLES
In aggregated tables, there is an important limit you must consider: the maximum cardinality is 2,500 values. Cardinality refers to the number of unique values generated by rows and columns.

If this limit is exceeded, the query will not execute, as it could affect application performance.

What does this mean in practice?
When you group data, ConnectiLogs needs to generate one row for each unique value. For example:
  1. If you group by IP: each distinct IP generates a row
  2. If you group by path: each distinct path generates a row
  3. If you group by IP + path: each unique combination of IP and path generates a row, i.e., number of different IPs x number of different paths.
Example:
  1. IP field → 100 unique values
  2. Path field → 50 unique values
If you group by IP + path, the theoretical maximum would be:
100 × 50 = 5,000 combinations

In this case, you would exceed the limit of 2,500, so the query would not execute.

Note: the theoretical maximum is not always reached (because not all combinations exist), but it is a good reference to estimate if you might have problems.

What happens if you exceed 2,500?
  1. The query does not execute
  2. ConnectiLogs will show a warning indicating there are too many combinations
  3. You must adjust the query before you can get results
How to avoid exceeding this limit?
Some practical recommendations:
  1. Reduce the date range → less data, fewer combinations
  2. Apply filters: limit the values (e.g., a single IP or domain)
  3. Avoid combining high-cardinality fields: such as IDs, IPs, or highly variable paths
  4. Start with a single dimension and add more only if necessary
Bar chart 
The bar chart is a visualization designed to compare values across different categories quickly and visually. It is one of the most intuitive formats within ConnectiLogs, as it converts numerical data into bars whose length represents the magnitude of the value.

It is especially useful when you want to answer questions like: which category has the most activity?, which element stands out the most?, or how do different groups compare to each other?

How does it work?
A bar chart is built from two main elements:
  1. Group by (X-axis): the category you want to analyze (e.g., IP, domain, path, user...)
  2. Value to aggregate (Y-axis): the result of the calculation applied to the data (e.g., Count, Sum, Average...)
The result could be represented like this:
  1. /home → 1200 accesses
  2. /login → 800 accesses
  3. /contact → 300 accesses
In the chart, each bar would have a height proportional to these values, allowing you to see at a glance which paths receive more traffic.

What can you do with a bar chart?
  1. Compare categories visually
  2. Quickly identify the highest or lowest values
  3. Detect anomalies or out-of-the-ordinary behaviors
  4. Summarize large volumes of data clearly

Bar chart limits
To ensure performance and readability:
  1. Group by: Allows up to two different fields. We recommend using only one, and two only in very specific cases for performance reasons.
  2. Value to aggregate: Allows only one field.
  3. Recommended cardinality: works best with medium or filtered datasets

Recommendation
Use the bar chart when you need direct visual comparison between categories. It is one of the most effective ways to transform aggregated data into easy-to-understand information in seconds.

Pro Tip:
To improve usability, there is a horizontal and vertical selector so you can zoom in on the chart and explore the data better.

You can go from this:


To this:


Timeline and area chart 
The timeline chart and the area chart are, essentially, the same type of visualization. Both are used to represent the evolution of data over time, allowing you to analyze trends, peaks, and changes in a metric continuously.

The only real difference between them is the visual representation.

How do they work?
Both charts are based on two main elements:
  1. Group by: implicitly includes the date and up to one more field if you wish,
  2. Value to aggregate: a calculated metric (Count, Sum, Average, etc.)
From this data, points in time are constructed that show how a metric evolves.

Difference between timeline and area chart

Although conceptually the same, the difference lies in the visualization:
  1. Timeline chart: Represents the data using a line that connects the points. It is visually lighter and focuses on the trend.
  2. Area chart: Same as the timeline, but fills the area under the line. This helps better visualize the volume or total magnitude of the values.
In practice:
  1. Timeline → better for seeing trends with precision
  2. Area → better for perceiving volume and weight of data
Simple example
Imagine you are analyzing daily accesses to your website:
  1. Day 1 → 100 accesses
  2. Day 2 → 150 accesses
  3. Day 3 → 90 accesses
In both cases you would see the same temporal evolution, but:
  1. In timeline you would see a line going up and down
  2. In area you would see that same line with the area below filled in
What can you do with these charts?
  1. Analyze trends over time
  2. Detect peaks or drops in activity
  3. Compare metric evolution
  4. Identify temporal patterns (daily, weekly, etc.)

Limits of these charts
  1. By default we show 10 different lines, with the option to choose 10, 20, 50, or up to 100 different lines.
  2. Only allows adding a single value.
  3. They are designed for analysis, not for individual record detail.
  4. A single aggregated value per series.


When to use timeline or area chart?

They are ideal when:
  1. You want to see how a metric evolves over time
  2. You are analyzing historical behavior
  3. You need to detect trends or temporal anomalies
  4. You are building dashboards with periodic metrics

Recommendation
Use timeline when you are interested in trend precision and clear comparison between points. Use area chart when you want to give more importance to the volume or impact of data over time.

Pie chart 
The pie chart is a visualization designed to show the proportional distribution of a dataset. Its goal is to represent how a total is distributed among different categories, visually and very quickly to interpret.

It is especially useful when you want to answer questions like: what share does each category represent of the total?, how is a dataset distributed?, or which element has the greatest relative weight?

How does it work?
The pie chart is based on two main elements:
  1. Group by: the category you want to analyze (e.g., IP, domain, event type, user...)
  2. Value to aggregate: the metric that defines the size of each slice (e.g., Count, Sum, Average...)
Each category is represented as a "slice" of the circle, and the size of that slice is proportional to its value relative to the total.

Simple example
If you analyze accesses to your website by path:
  1. /home → 500 accesses
  2. /login → 300 accesses
  3. /contact → 200 accesses
The chart will show a circle divided into slices, where:
  1. /home occupies 50%
  2. /login 30%
  3. /contact 20%
This allows you to understand traffic distribution at a glance.

What can you do with a pie chart?
  1. See proportions within a total
  2. Quickly identify the dominant category
  3. Understand data distribution without needing tables
  4. Visualize metric composition simply

Pie chart limits
  1. Group by: allows up to 1 field.
  2. Value to aggregate: allows only one field.
  3. By default there is NO limit on slices, which is why it is extremely important to choose a field in group by with low cardinality (few distinct values). You can choose limits of 10, 20, 50, and 100 different slices.
  4. Limited number of categories: it is not recommended to use fields with many different categories, as the chart loses readability.
  5. Better with few elements: ideally between 2 and 8 categories
  6. High cardinality not recommended: fields with many unique values are not suitable
  7. A single aggregated value: only allows one primary metric

When to use a pie chart?
It is the best option when:
  1. You want to show clear proportions within a total
  2. You have few different categories
  3. You need a simple, direct visualization
  4. You are showing basic distributions in dashboards
The pie chart allows you to immediately see which type of event predominates.

Recommendation
Use the pie chart only when you have few categories and want to highlight proportions. If the number of values grows too large, it is preferable to use a bar chart to maintain clarity of analysis.

Pro TIP:
The "Group into Others" option is a functionality designed to improve chart readability when there are many categories with little individual weight.

Instead of showing all categories separately, ConnectiLogs groups the least relevant ones into a single category called "Others".

How does it work?
When you apply this option, the system:
  1. Sorts categories by their value (e.g., number of events or total sum)
  2. Keeps the main categories visible (those with the greatest weight)
  3. Groups the rest of the minor categories into a single block called "Others"
The value of "Others" is the total sum of all grouped categories.

Simple example
Imagine this traffic distribution:
  1. /home → 5000
  2. /login → 3000
  3. /contact → 1500
  4. /blog/post1 → 200
  5. /blog/post2 → 150
  6. /blog/post3 → 100
If you activate "Group into Others" (leaving, for example, only the first 3 categories visible), the result would be:
  1. /home → 5000
  2. /login → 3000
  3. /contact → 1500
  4. Others → 450
3.2. Aggregate and analyze the data 
Once you choose the type of chart or table you want to use, the application automatically loads the available dimensions. From there, you can add the fields you want in each dimension. At all times, the tool will indicate the permitted limits, so you know how many fields you can add and where.

All visualizations, except the simple table, allow working with aggregations. An aggregation serves to summarize information from a dimension and obtain data that is easier to analyze. In other words, it is a way to group records and apply a calculation to them.

For example, an aggregation can be used to count how many times a given record appears, considering the combinations of the other selected dimensions.

To perform these aggregations, operators that summarize the data are used. The available operators are:
  1. Count: counts the total number of records.
  2. Sum: adds the numerical values of the selected dimension.
  3. Average: calculates the average value of the dimension.
  4. Min: shows the minimum recorded value.
  5. Max: shows the maximum recorded value.
  6. Count distinct: counts the number of unique values within the selected dimension.
For example, if you want to analyze accesses per route on your website, you can use Count to know how many accesses each domain has had. If, instead, you are analyzing a numerical field like file size to determine the amount of outgoing traffic, you can use Sum to know the total volume per resource.

In the following practical example, the number of visits for each different IP found will be shown:


3.3. Filter the results (optional) 
Additionally, you can apply filters to narrow down the data and visualize only the information that really interests you. Filters allow you to include or exclude records based on certain conditions, making analysis easier and results more precise.

For example, you can filter to see only events from a specific domain, limit results to a specific date range, or exclude certain types of records you don't want to analyze.

To define these filters, ConnectiLogs provides different operators, which allow you to establish how each condition should be met:
  1. = (Equals): shows only records whose value exactly matches the indicated one.
  2. != (Not equals): excludes records that match the indicated value.
  3. like (Contains): shows records that contain the indicated value, even if it is part of a longer text.
  4. not like (Does not contain): excludes records that contain the indicated value.
  5. in (Included in a set): shows records whose value is within a defined list of values.
  6. not in (Not included in a set): excludes records whose value is within a defined list of values.
  7. > (Greater than): shows records whose value is greater than the indicated one.
  8. >= (Greater than or equal to): shows records whose value is greater than or equal to the indicated one.
  9. < (Less than): shows records whose value is less than the indicated one.
  10. <= (Less than or equal to): shows records whose value is less than or equal to the indicated one.
It is not necessary to delve into how each operator works in this section. ConnectiLogs has a specific guide on filters (ConnectiLogs Panel | Filter Configuration) that explains in detail how to use them, with practical examples and usage recommendations.


3.4. Run the query 
Once you have defined the visualization type, added the corresponding fields to the dimensions, and optionally applied the filters you need, you can now execute the query.

To do this, click the "Run query" button.

ConnectiLogs will process the information taking into account all selected options and display the results on screen using the chosen visualization format. From that moment on, you can analyze the obtained data and, if you wish, adjust dimensions, filters, or chart type to refine the results.

From here, the client has several additional options:
  1. Export the data to a file to work with it outside ConnectiLogs. Keep in mind that in some cases data may be limited during its beta phase and until we implement the full record exporter functionality.
  2. Add the query to a dashboard to always have it visible and update it automatically (ConnectiLogs Panel | Dashboards).


Tips:
  1. Choose the visualization first: this defines how you can organize dimensions and what type of operators you can use.
  2. Use aggregation correctly: Count and Count distinct are useful for counting records, while Sum, Average, Min, and Max are used to analyze numerical data.
  3. Optional filters: even if you don't apply filters, you can always explore the results and adjust them later.
  4. Save frequent queries: if you repeat similar analyses, save time by saving the query to run it again later on a dashboard or export it to have it on your device.


For more information, you can contact us.

    • Related Articles

    • ConnectiLogs Panel | Log Management

      What is ConnectiLogs? ConnectiLogs is the visualization, analysis, and exploration platform for activity and security data from cdmon products. Its goal is to centralize all the information generated by the different services in order to facilitate ...

    • ConnectiLogs Panel | Dashboards

      Dashboards in ConnectiLogs are customizable panels that allow you to easily visualize the most relevant information from your data. Their main advantage is that they let you access the information you need quickly and directly, without having to ...

    • ConnectiLogs Panel | Filter Configuration

      Filters allow you to reduce and control the data returned by a query, showing only the records that meet specific conditions. They are one of the most important tools in the query builder, as they help you move from large volumes of logs to concrete, ...

    • ConnectiLogs | MariaDB user management of cdmon hostings

      The ConnectiLogs tool is designed to provide clear and simple access to the logs of the hostings you have created in your control panel. From this section, you can view and analyze server logs through an intuitive panel, accessible to both beginners ...

    • ConnectiLogs | Malware Management of cdmon Hostings

      The ConnectiLogs tool is designed to provide clear and simple access to the logs of the hostings you have created in your control panel. From this section, you can view and analyze server logs through an intuitive panel, accessible to both beginners ...