Stats with transpose of one field to summarized columns depending on field content

[bdein]

FYI: I come from the Splunk world with 10+ years of experience with all kinds of “interesting” query experience, so I hope and try to achieve some of the same tricks here in Grafana, and one comes here

• What Grafana version and what operating system are you using? Latest

• What are you trying to achieve?

• • Basically I want to make a stats table, where I collect to see the following information in different steps:
• 1. Just one row with the following information, and nothing else over picked time period:

• 1.1 Unique number of customers, unique number of hosts, unique number of monitored apps/systems/infrastructure, total events, total size of events, x columns representing different log levels (i.e. INFO, WARN, ERROR etc) in give time frame and per level total number of customers having this log level.

• 2. On next level, I need a drill down on above, so if I click on the count of i.e. “ERROR”, then I’ll get a list showing all customers, one customer per row, with the following information:

• 2.1. Customer ID, Name, Number of hosts, Number of systems, per x log level, number of systems with current log level (not total number of i.e. errors, but how many system shows error)

• 3. On next level in turn drill down to see an individual customers systems, one per row (as overall), and then further details below, that will work as drilldown depending on what is pointing at.

• How are you trying to achieve it?

• Simple query to stats count of log level by customer, host, system, and this part looks fine

• What happened?

• I get all log levels in one column.

• What did you expect to happen?

• I need the log level (and only the log level) to be transposed to columns, which in Splunk is very easy (if you know how) by:

| fields level customer host system
| eval lev_[level]=1
| stats sum(lev_*) AS * BY customer, host, system

How can I achieve the same result in Grafana?

• Can you copy/paste the configuration(s) that you are having problems with?
• As an example:
  
  

But should look like this:

Time	event_log	event_source	INFO	ERROR
2025-07-04 15:58:19.551	Application	Admin By Request Updater	2
2025-07-04 15:58:19.551	Application	Microsoft-Windows-Security-SPP	8
2025-07-04 15:58:19.551	Application	Outlook	2
2025-07-04 15:58:19.551	Security	Microsoft-Windows-Security-Auditing	5776
2025-07-04 15:58:19.551	System	Microsoft-Windows-Security-Kerberos		1
2025-07-04 15:58:19.551	System	Microsoft-Windows-UserModePowerService	365
2025-07-04 15:58:19.551	System	Service Control Manager	2

{
  "id": 5,
  "type": "table",
  "title": "Event Logs",
  "gridPos": {
    "x": 0,
    "y": 14,
    "h": 17,
    "w": 16
  },
  "fieldConfig": {
    "defaults": {
      "custom": {
        "align": "auto",
        "cellOptions": {
          "type": "auto"
        },
        "inspect": false
      },
      "mappings": [],
      "thresholds": {
        "mode": "absolute",
        "steps": [
          {
            "color": "green",
            "value": null
          },
          {
            "color": "red",
            "value": 80
          }
        ]
      },
      "color": {
        "mode": "thresholds"
      }
    },
    "overrides": []
  },
  "transformations": [
    {
      "id": "groupBy",
      "options": {
        "fields": {
          "Value #A": {
            "aggregations": [
              "sum"
            ],
            "operation": "aggregate"
          },
          "Value #Events BY level": {
            "aggregations": [
              "sum"
            ],
            "operation": "aggregate"
          },
          "Value #Size": {
            "aggregations": [
              "sum"
            ],
            "operation": "aggregate"
          },
          "Value #Total events BY level": {
            "aggregations": [
              "sum"
            ],
            "operation": "aggregate"
          },
          "Value #Total events BY level  2": {
            "aggregations": [
              "sum"
            ],
            "operation": "aggregate"
          },
          "customer": {
            "aggregations": [],
            "operation": "groupby"
          },
          "host": {
            "aggregations": [],
            "operation": "groupby"
          },
          "level": {
            "aggregations": [],
            "operation": "groupby"
          },
          "event_log": {
            "aggregations": [],
            "operation": "groupby"
          },
          "event_source": {
            "aggregations": [],
            "operation": "groupby"
          }
        }
      }
    }
  ],
  "pluginVersion": "12.1.0-90058",
  "targets": [
    {
      "datasource": {
        "type": "loki",
        "uid": "grafanacloud-logs"
      },
      "direction": "backward",
      "editorMode": "code",
      "expr": "sum(\r\n  count_over_time(\r\n    {customer=~\".+\",service_name=\"system/eventlog\"} \r\n      [$__auto]\r\n  )\r\n) by (level,event_log,event_source)",
      "hide": false,
      "queryType": "instant",
      "refId": "Total events BY level  2"
    },
    {
      "datasource": {
        "type": "loki",
        "uid": "grafanacloud-logs"
      },
      "direction": "backward",
      "editorMode": "code",
      "expr": "sum(\r\n    sum_over_time(\r\n        {customer=~\".+\"} \r\n        |= `` \r\n        | unwrap sizeB \r\n        [$__auto]\r\n    )\r\n) by (level,customer, host)",
      "hide": true,
      "queryType": "instant",
      "refId": "Size"
    }
  ],
  "datasource": {
    "type": "loki",
    "uid": "grafanacloud-logs"
  },
  "options": {
    "showHeader": true,
    "cellHeight": "sm",
    "footer": {
      "show": false,
      "reducer": [
        "sum"
      ],
      "countRows": false,
      "fields": ""
    }
  }
}

• Did you receive any errors in the Grafana UI or in related logs? No If so, please tell us exactly what they were.

• Did you follow any online instructions? Haven’t been able to find any If so, what is the URL?

[dawiddebowski]

Hi, you could use Partition by values and then Join by fields transformations like that:

Before transformations:

After:

As for the rest of the questions:
2. I don’t think you can create a link on a column. You can create a link on specific data (e.g. on the number in the cell) by creating a data link (lower part of the visualization settings on the right). The destination dashboard would have to either be variabled (you can pick log level by a variable) or you’d have to create other panels for the same functionality and link them accrodingly from the original panel with overrides (which is far less practical imo).
3. Here I’d need some more details, as it’s too late in Saturday for me to understand that, sorry

(If you’re going to ask following questions, please tag me in your answer - something happened with the notifications and I don’t see the responses otherwise )

[bdein]

Hi @dawiddebowski,

Thanks - this as what I need.

On 3.: Yes it might look a bit complicated, but I frankly don’t think it’s that bad, when I first get my head wacked around how thing like this is done in Grafana
Basically what I want to achieve is a multi step (max 4) drilldown from top overall view till factual core details of any customers app, service, host, infrastructure details

In practice:

• Level 1 (top level)
  ** I’ll only see a general picture showing me the over all status by telling how many customers are good (=INFO), not so good (=WARN), bad(=ERROR), really bad(=FATAL)
  ** Beside above I want show how many are DOWN, and how many are in MAINT (Maintenance mode).

• On level 4 (max down).
  ** Here I’ve clicked on various WARN, ERROR and what not to drill down to the root cause (hopefully).
  ** Here I’ll be at a specific service, host, app and/or combi with graphs and detailed logs around the time of the incident

Hope above gives a little broader picture of what I want to achieve

[bdein]

Are there no available with any ideas here?

[dawiddebowski]

Sorry, I forgot to write back What I would do is to use data links with overrides so that each column would guide user to different place.
Column with event_log would lead to a dashboard for this event log. Maybe some overall status at top (some stats like number of logs in every log level, below some info how it behave over time, if this number of errors is expected or not with thresholds and colors) and maybe some documentation. Below you can either use similar table to the one you’ve already shown or use repeated rows to get into source details.
The column with event source would go to the most detailed drill down dashboard - this one focuses directly on the source, here you get detailed info about logs the source produced, I’d probably go with overall stats on top and collapsible rows with each level. I don’t know if event_source can have both Application and Security logs, if so, I maybe would go with another dashboard combining those two or scratch the two dashboards to make only this one.