Audit Logging - help?

[meyerder]

I am working on a pmm deploy script that includes promtail and having an issue parsing the mysql audit logs in json format… Hopefully someone can help me out here as I cant quite get it correctly… to build the yml file and append to the existing one I have a script that generates job entrys… can someone help me out here??

#!/bin/bash

# Prompt for user inputs
read -p "Enter hostname: " hostname
read -p "Enter applid: " applid
read -p "Enter environment: " environment
read -p "Enter log file path (__path__): " log_path

# Text file containing command classes (one per line)
command_classes_file="command_classes.txt"

# Output configuration file
output_file="promtail_config.yml"

# Clear or create the output file
> "$output_file"

# Loop through each command class in the text file
while IFS= read -r command_class; do
    # Skip empty lines
    if [[ -z "$command_class" ]]; then
        continue
    fi

    cat >> "$output_file" << EOL
- job_name: ${command_class}
  static_configs:
    - targets:
        - localhost
      labels:
        job: ${command_class}
        hostname: ${hostname}
        applid: ${applid}
        environment: ${environment}
        __path__: ${log_path}
  pipeline_stages:
    - json:
        expressions:
          audit_record: audit_record
    - json:
        expressions:
          command_class: audit_record.command_class
          name: audit_record.name
          db: audit_record.db
          ip: audit_record.ip
          user: audit_record.user
          status: audit_record.status
          sqltext: audit_record.sqltext
    - match:
        selector: '{command_class="${command_class}"}'
        stages:
          - labels:
              command_class: command_class
              name: name
              db: db
              ip: ip
              user: user
              status: status
              sqltext: sqltext
    - match:
        selector: '{command_class!="${command_class}"}'
        stages:
          - drop:
              source: command_class
EOL
done < "$command_classes_file"

echo "Promtail configuration has been generated in $output_file"

[Agustin_G]

Hi @meyerder,

Can you explain what issues in particular you are having?

If you need to process a JSON file, you can use JQ for it. There are several posts on it, a quick search revealed the following two, for instance:

https://www.linode.com/docs/guides/using-jq-to-process-json-on-the-command-line/

How To Transform JSON Data with jq | DigitalOcean

[meyerder]

I know I can process the file with jq quite easily, but my goal is to send MySQL audit logs, generated by the Percona auditing plugin, to a Loki instance using Promtail. I want to enhance the labels on these logs to build auditing dashboards inside PMM.

Currently, I’m logging most of the JSON audit commands to one file per system. Given the sheer volume of logs (the Loki server is keeping up), I’ve already broken down the logs by application (Application 1, 2, 3, 4) and by location (AWS, on-prem, others). However, I want to refine the labels further, categorizing them by command classes like select, insert, alter_table, grant, and others. This way, I can retain logs from 20 systems for a week or more, especially for critical commands.

With the current volume (~80k entries per second across all systems), I need to split the command classes into separate jobs in Promtail. By doing so, each job will only process entries relevant to its assigned command class, rather than all entries in the db-audit job. Think of it like a WHERE clause in a database query.

My goal is to dynamically build and supplement the promtail.yml file based on the my.cnf file, using the command classes being audited. This involves variable replacements and creating jobs in promtail.yml for each command class. Each job should only include entries corresponding to its specific command class.

Everything is set up except for one part: I can’t quite figure out the pipeline stages and match aspect in Promtail. Right now, it sends everything to each job, rather than filtering based on the command class.

Promtail is installed.
It reads the audit.log (JSON format) generated by MySQL Percona audit logs.
The promtail.yml file contains jobs for each command class.
Each job should record only the entries relevant to its assigned command class.
I need to build a new job in promtail.yml for each command class and ensure that job only includes entries for that command class.
I want to do this dynamically based off of the input of the command classes text file (which is build by parsing the enabled commands being logged on that specific system)

End game
This allows a much quicker search aspect in loki due to volume (The to be built PMM Dashboard)
This Allows me the ability to more easily manage retention logs (keep selects for say a week but alter table commands for 90 days) on the loki server

**as at times I have a hard time expressing items I did get this cleaned up via AI… Hope it is clear

[meyerder]

Better Example

I know I can do this
{command_class=“delete”,environment=“PROD”,job=‘db-audit’}
But this forces it to look at the db-audit which contains millions of rows of command_class records

I want to be able to do this where each job only contains its own items so it has to look at a much smaller set of records when querying
{job=“select”,environment=“PROD”} – Contains ONLY the select command class records
{job=“delete”,environment=“PROD”} – Contains ONLY the delete command class records
{job=“alter_table”,environment=“PROD”} – Contains ONLY the alter_Table command class records

[Agustin_G]

Hi @meyerder,

Thanks, it’s clear now. I’m by no means a Promtail expert, but how about you use multiple pipelines for a single job, instead of using multiple jobs with only one pipeline? (I’m not counting the != match as a pipeline in this context, since you use it to discard data; this is why I say you are using only one per job.)

In your case, your script is generating something like:

- job_name: select
  static_configs:
...
  pipeline_stages:
...
    - match:
        selector: '{command_class="select"}'
...
    - match:
        selector: '{command_class!="select"}'
...
- job_name: insert
  static_configs:
...
  pipeline_stages:
...
    - match:
        selector: '{command_class="insert"}'
...
    - match:
        selector: '{command_class!="insert"}'
...
- job_name: delete
  static_configs:
...
  pipeline_stages:
...
    - match:
        selector: '{command_class="delete"}'
...
    - match:
        selector: '{command_class!="delete"}'
...

So you have many jobs, each with a pipeline of its own. However, I think what you want is something like the following (one job with multiple matches):

- job_name: db-audit
  static_configs:
...
  pipeline_stages:
...
    - match:
        selector: '{command_class="select"}'
...
    - match:
        selector: '{command_class="insert"}'
...
    - match:
        selector: '{command_class="delete"}'
...
    - match:
        selector: '{command_class!="something_else"}'
...

This is the relevant documentation link for you to check:

Pipelines | Grafana Loki documentation

Let me know how it goes, or if I misunderstood what you want to achieve.