Problem with query "Total Failed - Unique IP"

[Bruno-Macedo]

Hello,

I'm using this repository as part of my bachelor thesis (I quoted it) and when using the .json file for ssh logs I get the following error message:

Inspect: Total Failed - Unique IP parse error at line 1, col 11: syntax error: unexpected ip, expecting IDENTIFIER or ) queries: "expr": "count by (ip) (count_over_time({$label_name=~\"$labelvalue\", job=~\"$job\", instance=~\"$instance\"} |=\"sshd[\" |~\": Invalid|: Connection closed by authenticating user|: Failed\" |~\". from .\" | pattern `<> from port` | error=\"\" [$__interval]))

 "expr": "count by (ip) (count_over_time({$label_name=~\"$label_value\", job=~\"$job\", instance=~\"$instance\"} |=\"sshd[\" |~\": Invalid|: Connection closed by authenticating user|: Failed\" !~\".* from .*\" | pattern `<_> user <_> <ip> port` | __error__=\"\" [$__interval]))",

Would be possible to help find what I am doing wrong here?

complete .json

{
  "datasource": {
    "type": "loki",
    "uid": "8tA2QbPVk"
  },
  "fieldConfig": {
    "defaults": {
      "mappings": [
        {
          "options": {
            "match": "null",
            "result": {
              "index": 0,
              "text": "0"
            }
          },
          "type": "special"
        }
      ],
      "thresholds": {
        "mode": "absolute",
        "steps": [
          {
            "color": "purple",
            "value": null
          },
          {
            "color": "red",
            "value": 1
          }
        ]
      },
      "unit": "short"
    },
    "overrides": []
  },
  "gridPos": {
    "h": 4,
    "w": 3,
    "x": 9,
    "y": 1
  },
  "id": 21,
  "options": {
    "reduceOptions": {
      "values": false,
      "calcs": [],
      "fields": ""
    },
    "orientation": "auto",
    "textMode": "auto",
    "colorMode": "background",
    "graphMode": "none",
    "justifyMode": "auto"
  },
  "pluginVersion": "9.4.7",
  "targets": [
    {
      "datasource": {
        "type": "loki",
        "uid": "8tA2QbPVk"
      },
      "editorMode": "code",
      "expr": "count by (ip) (count_over_time({$label_name=~\"$label_value\", job=~\"$job\", instance=~\"$instance\"} |=\"sshd[\" |~\": Invalid|: Connection closed by authenticating user|: Failed\" |~\".* from .*\" | pattern `<_> from <ip> port` | __error__=\"\" [$__interval]))",
      "hide": false,
      "legendFormat": "{{ ip }}",
      "queryType": "range",
      "refId": "A",
      "resolution": 1
    },
    {
      "datasource": {
        "type": "loki",
        "uid": "8tA2QbPVk"
      },
      "editorMode": "code",
      "expr": "count by (ip) (count_over_time({$label_name=~\"$label_value\", job=~\"$job\", instance=~\"$instance\"} |=\"sshd[\" |~\": Invalid|: Connection closed by authenticating user|: Failed\" !~\".* from .*\" | pattern `<_> user <_> <ip> port` | __error__=\"\" [$__interval]))",
      "hide": false,
      "legendFormat": "{{ ip }}",
      "queryType": "range",
      "refId": "B"
    }
  ],
  "title": "Total Failed - Unique IP",
  "transformations": [
    {
      "id": "labelsToFields",
      "options": {
        "mode": "columns",
        "valueLabel": "ip"
      }
    },
    {
      "id": "merge",
      "options": {}
    },
    {
      "id": "organize",
      "options": {
        "excludeByName": {
          "178.40.119.51": false,
          "194.154.240.221": false,
          "label": true
        },
        "indexByName": {},
        "renameByName": {
          "value": "IP"
        }
      }
    },
    {
      "id": "calculateField",
      "options": {
        "alias": "Unique IP",
        "mode": "reduceRow",
        "reduce": {
          "include": [],
          "reducer": "count"
        },
        "replaceFields": true
      }
    }
  ],
  "type": "stat"
}

Kind regards, Bruno

[voidquark]

Hi,

Thank you for reaching out. To help you with your issue, could you please provide me with some additional information?

Firstly, please make sure that you are using the latest (Grafana, Loki, Promtail ) version of the software. Additionally, I would like to clarify that this dashboard has been tested only with RHEL8/RHEL9. May I know which Linux distro you are using and the major and minor version?

Also, kindly provide us with the following details:

• Grafana version
• Grafana Loki version
• Grafana promtail version

Furthermore, it would be helpful if you could share the log lines from /var/log/secure that correspond to the relevant log entries.

[Bruno-Macedo]

Hello, I managed to find the issue. The problem was the variable "ip", it should be capitalized "IP".

[voidquark]

I'm sorry, but this issue is not related to the functionality and couldn't have caused the problem you reported. However, while investigating the dashboard, I found a bug and uploaded a new revision (v2) to fix it. The problem was that the unique IP count was not calculated correctly due to an incorrect transformation. The query itself was correct, but the transformation caused the miscalculation.

[Bruno-Macedo]

Sorry for insisting on it. As I said, with the previous file, by changing ip to IP I got an answer. I also tried this new and I got the same error as before. Here are the versions I am running in the container: Grafana: grafana/grafana:latest Loki: grafana/loki:2.3.0 Promtail: grafana/promtail:2.3.0

When I go to Inspect => Error: Status: 500. Message: parse error at line 1, col 11: syntax error: unexpected ip, expecting IDENTIFIER or )

At the UI i get the following error:

As I said, I just wanted to show u, u dont need to bother about it.

Kind regards, Bruno

[voidquark]

Hey, the issue you're experiencing is caused by a bug in Loki version 2.3.0 - you can find more information about it here: https://github.com/grafana/loki/issues/4665. To prevent this from happening, I recommend updating your Loki version to the latest one available. Once you've done that, if you use the latest dashboard revision, this panel should work as expected.