Data missing in table

pradeepchandran · December 11, 2017, 6:25am

When i try to display some logs and its count from elastic search to grafana table some datas are missing,eg actual data :- “Reverse lookup error (bad ISP or attack)” but its displayed only reverse

daniellee · December 12, 2017, 12:00pm

Can you show your elasticsearch query and the table settings with screenshots.

pradeepchandran · December 12, 2017, 1:47pm

Actual field was “Unknown problem somewhere in the system.” but its shows only “Unkown”
Note:- Its work properly on ELK with same grok
elk_dash

Elastic Search query :-

TAble query :-

daniellee · December 13, 2017, 12:31pm

What does your Options tab look like? Is it JSON Data?

My first thought is that this is something to do with your mapping. Is it analyzed or not analyzed? Do you have a signature.raw column that you can use instead?

See this for an explanation of analyzed/not analyzed:
http://thomasardal.com/terms-aggregations-on-analyzed-fields-in-elasticsearch/

pradeepchandran · December 14, 2017, 3:54am

hi,
Did you mean grafana option tab ?its not json data i am using both kibana and grafana,but this issue shows only in grafana .i am using same elastic search and logstash for both,if the problem was with my elastic search query its also make issue in kibana right ???
my logstash grok :-
filter {

grok {

match => ["message", "(?m)\*\* Alert %{DATA:timestamp_seconds}:%{SPACE}%{WORD}?%{SPACE}\- %{DATA:ossec_group}\n%{YEAR} %{SYSLOGTIMESTAMP:syslog_timestamp} \(%{DATA:reporting_host}\) %{IP:reporting_ip}\-\>%{DATA:reporting_source}\nRule: %{NONNEGINT:rule_number} \(level %{NONNEGINT:severity}\) \-\> '%{DATA:signature}'\n%{GREEDYDATA:remaining_message}"]

# Matches  2014 Mar 08 00:00:00 ossec-server01->/var/log/auth.log
match => ["message", "(?m)\*\* Alert %{DATA:timestamp_seconds}:%{SPACE}%{WORD}?%{SPACE}\- %{DATA:ossec_group}\n%{YEAR} %{SYSLOGTIMESTAMP:syslog_timestamp} %{DATA:reporting_host}\-\>%{DATA:reporting_source}\nRule: %{NONNEGINT:rule_number} \(level %{NONNEGINT:severity}\) \-\> '%{DATA:signature}'\n%{GREEDYDATA:remaining_message}"]

}

grok {
match => [“remaining_message”, “(?m)(Src IP: %{IP:src_ip}%{SPACE})?(Src Port: %{NONNEGINT:src_port}%{SPACE})?(Dst IP: %{IP:dst_ip}%{SPACE})?(Dst Port: %{NONNEGINT:dst_port}%{SPACE})?(User: %{USER:acct}%{SPACE})?%{GREEDYDATA:real_message}”]
}

geoip {
source => “src_ip”
}
}

Real log :-
** Alert 1513223582.153236: - syslog,errors,
2017 Dec 14 03:53:02 spoiler 10.20.4.145-[/spoiler]>/var/log/syslog
Rule: 1002 (level 2) -> 'Unknown problem somewhere in the system.'
Dec 13 22:53:01 micro-zoho CRON[1896]: (root) CMD (/bin/bash /root/scripts/check_error_pattern.sh)

daniellee · December 14, 2017, 9:18am

I’m not sure how Kibana handles it - maybe it adds a multi-index for you? It is quite difficult to read the text you posted there - I am not sure what you are trying to show me.

Can you show a screenshot of the options tab for your table panel in Grafana.
Can you check the index of your signature column in Elasticsearch

pradeepchandran · December 16, 2017, 3:52am

Hi Danielle,

Sorry for the delay. actually i am new in grafana .I have not much
knowledge about how grafana fetch the index from elastic search.Please look
my elastic search index details its in json format and grafana table was
attached as file.

curl -XGET ‘http://privateip:9200/hide-*/_search?pretty’

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 30,
    "successful" : 30,
    "failed" : 0
  },
  "hits" : {
    "total" : 18402,
    "max_score" : 1.0,
    "hits" : [ {
      "_index" : "ossec-2017.12.10",
      "_type" : "ossec_filebeat",
      "_id" : "AWBA1ddz5tockKIFV_LT",
      "_score" : 1.0,
      "_source" : {
        "message" : "** Alert 1512910563.530938: - syslog,errors,\n2017 Dec
10 12:56:03 (e-agree_micro-zoho) 10.20.4.145->/var/log/syslog\nRule: 1002
(level 2) -> 'Unknown problem somewhere in the system.'\nDec 10 07:56:01
micro-zoho CRON[11412]: (root) CMD (/bin/bash
/root/scripts/check_error_pattern.sh)\n",
        "@version" : "1",
        "@timestamp" : "2017-12-10T14:30:31.546Z",
        "input_type" : "log",
        "source" : "/var/ossec/logs/alerts/alerts.log",
        "beat" : {
          "name" : "oss-01-prd-usw",
          "hostname" : "oss-01-prd-usw",
          "version" : "5.6.3"
        },
        "tags" : [ "ossec-server", "beats_input_codec_plain_applied" ],
        "offset" : 531222,
        "type" : "ossec_filebeat",
        "host" : "oss-01-prd-usw",
        "timestamp_seconds" : "1512910563.530938",
        "ossec_group" : "syslog,errors,",
        "syslog_timestamp" : "Dec 10 12:56:03",
        "reporting_host" : "e-agree_micro-zoho",
        "reporting_ip" : "10.20.4.145",
        "reporting_source" : "/var/log/syslog",
        "rule_number" : "1002",
        "severity" : "2",
        "**signature" : "Unknown problem somewhere in the system**.",
        "remaining_message" : "Dec 10 07:56:01 micro-zoho CRON[11412]:
(root) CMD (/bin/bash /root/scripts/check_error_pattern.sh)\n",
        "real_message" : "Dec 10 07:56:01 micro-zoho CRON[11412]: (root)
CMD (/bin/bash /root/scripts/check_error_pattern.sh)\n"
      }
    }, {
      "_index" : "ossec-2017.12.10",
      "_type" : "ossec_filebeat",
      "_id" : "AWBA1ddz5tockKIFV_LX",
      "_score" : 1.0,
      "_source" : {
        "message" : "** Alert 1512910683.532077: - syslog,errors,\n2017 Dec
10 12:58:03 (e-agree_micro-zoho) 10.20.4.145->/var/log/syslog\nRule: 1002
(level 2) -> 'Unknown problem somewhere in the system.'\nDec 10 07:58:01
micro-zoho CRON[11627]: (root) CMD (/bin/bash
/root/scripts/log_error_monitor.sh 2>&1)\n",
        "@version" : "1",
        "@timestamp" : "2017-12-10T14:30:31.546Z",
        "offset" : 532364,
        "beat" : {
          "name" : "oss-01-prd-usw",
          "hostname" : "oss-01-prd-usw",
          "version" : "5.6.3"
        },
        "source" : "/var/ossec/logs/alerts/alerts.log",
        "type" : "ossec_filebeat",
        "input_type" : "log",
        "tags" : [ "ossec-server", "beats_input_codec_plain_applied" ],
        "host" : "oss-01-prd-usw",
        "timestamp_seconds" : "1512910683.532077",
        "ossec_group" : "syslog,errors,",
        "syslog_timestamp" : "Dec 10 12:58:03",
        "reporting_host" : "e-agree_micro-zoho",
        "reporting_ip" : "10.20.4.145",
        "reporting_source" : "/var/log/syslog",
        "rule_number" : "1002",
        "severity" : "2",
        "signature" : "Unknown problem somewhere in the system.",
        "remaining_message" : "Dec 10 07:58:01 micro-zoho CRON[11627]:
(root) CMD (/bin/bash /root/scripts/log_error_monitor.sh 2>&1)\n",
        "real_message" : "Dec 10 07:58:01 micro-zoho CRON[11627]: (root)
CMD (/bin/bash /root/scripts/log_error_monitor.sh 2>&1)\n"
      }
    }, {
      "_index" : "ossec-2017.12.10",
      "_type" : "ossec_filebeat",
      "_id" : "AWBA1ddz5tockKIFV_LY",
      "_score" : 1.0,
      "_source" : {
        "message" : "** Alert 1512910741.532364: - syslog,errors,\n2017 Dec
10 12:59:01 (e-agree_micro-zoho) 10.20.4.145->/var/log/syslog\nRule: 1002
(level 2) -> 'Unknown problem somewhere in the system.'\nDec 10 07:59:01
micro-zoho CRON[11739]: (root) CMD (/bin/bash
/root/scripts/check_error_pattern.sh)\n",
        "@version" : "1",
        "@timestamp" : "2017-12-10T14:30:31.546Z",
        "source" : "/var/ossec/logs/alerts/alerts.log",
        "offset" : 532648,
        "type" : "ossec_filebeat",
        "input_type" : "log",
        "beat" : {
          "version" : "5.6.3",
          "name" : "oss-01-prd-usw",
          "hostname" : "oss-01-prd-usw"
        },
        "tags" : [ "ossec-server", "beats_input_codec_plain_applied" ],
        "host" : "oss-01-prd-usw",
        "timestamp_seconds" : "1512910741.532364",
        "ossec_group" : "syslog,errors,",
        "syslog_timestamp" : "Dec 10 12:59:01",
        "reporting_host" : "e-agree_micro-zoho",
        "reporting_ip" : "10.20.4.145",
        "reporting_source" : "/var/log/syslog",
        "rule_number" : "1002",
        "severity" : "2",
        "signature" : "Unknown problem somewhere in the system.",
        "remaining_message" : "Dec 10 07:59:01 micro-zoho CRON[11739]:
(root) CMD (/bin/bash /root/scripts/check_error_pattern.sh)\n",
        "real_message" : "Dec 10 07:59:01 micro-zoho CRON[11739]: (root)
CMD (/bin/bash /root/scripts/check_error_pattern.sh)\n"
      }
    }, {
      "_index" : "ossec-2017.12.10",
      "_type" : "ossec_filebeat",
      "_id" : "AWBA1ddz5tockKIFV_La",
      "_score" : 1.0,
      "_source" : {
        "message" : "** Alert 1512910801.532935: - syslog,errors,\n2017 Dec
10 13:00:01 (e-agree_micro-zoho) 10.20.4.145->/var/log/syslog\nRule: 1002
(level 2) -> 'Unknown problem somewhere in the system.'\nDec 10 08:00:01
micro-zoho CRON[11845]: (root) CMD (/bin/bash
/root/scripts/check_error_pattern.sh)\n",
        "@version" : "1",
        "@timestamp" : "2017-12-10T14:30:31.546Z",
        "type" : "ossec_filebeat",
        "beat" : {
          "name" : "oss-01-prd-usw",
          "hostname" : "oss-01-prd-usw",
          "version" : "5.6.3"
        },
        "offset" : 533219,
        "input_type" : "log",
        "tags" : [ "ossec-server", "beats_input_codec_plain_applied" ],
        "source" : "/var/ossec/logs/alerts/alerts.log",
        "host" : "oss-01-prd-usw",
        "timestamp_seconds" : "1512910801.532935",
        "ossec_group" : "syslog,errors,",
        "syslog_timestamp" : "Dec 10 13:00:01",
        "reporting_host" : "e-agree_micro-zoho",
        "reporting_ip" : "10.20.4.145",
        "reporting_source" : "/var/log/syslog",
        "rule_number" : "1002",
        "severity" : "2",
        "signature" : "Unknown problem somewhere in the system.",
        "remaining_message" : "Dec 10 08:00:01 micro-zoho CRON[11845]:
(root) CMD (/bin/bash /root/scripts/check_error_pattern.sh)\n",
        "real_message" : "Dec 10 08:00:01 micro-zoho CRON[11845]: (root)
CMD (/bin/bash /root/scripts/check_error_pattern.sh)\n"
      }
    }, {
      "_index" : "ossec-2017.12.10",
      "_type" : "ossec_filebeat",
      "_id" : "AWBA1ddz5tockKIFV_Le",
      "_score" : 1.0,
      "_source" : {
        "message" : "** Alert 1512910861.534129: - syslog,errors,\n2017 Dec
10 13:01:01 (e-agree_micro-zoho) 10.20.4.145->/var/log/syslog\nRule: 1002
(level 2) -> 'Unknown problem somewhere in the system.'\nDec 10 08:01:01
micro-zoho CRON[11956]: (root) CMD (/bin/bash
/root/scripts/log_error_monitor.sh 2>&1)\n",
        "@version" : "1",
        "@timestamp" : "2017-12-10T14:30:31.546Z",
        "beat" : {
          "hostname" : "oss-01-prd-usw",
          "version" : "5.6.3",
          "name" : "oss-01-prd-usw"
        },
        "tags" : [ "ossec-server", "beats_input_codec_plain_applied" ],
        "source" : "/var/ossec/logs/alerts/alerts.log",
        "offset" : 534416,
        "type" : "ossec_filebeat",
        "input_type" : "log",
        "host" : "oss-01-prd-usw",
        "timestamp_seconds" : "1512910861.534129",
        "ossec_group" : "syslog,errors,",
        "syslog_timestamp" : "Dec 10 13:01:01",
        "reporting_host" : "e-agree_micro-zoho",
        "reporting_ip" : "hide",
        "reporting_source" : "/var/log/syslog",
        "rule_number" : "1002",
        "severity" : "2",
        "signature" : "Unknown problem somewhere in the system.",
        "remaining_message" : "Dec 10 08:01:01 micro-zoho CRON[11956]:
(root) CMD (/bin/bash /root/scripts/log_error_monitor.sh 2>&1)\n",
        "real_message" : "Dec 10 08:01:01 micro-zoho CRON[11956]: (root)
CMD (/bin/bash /root/scripts/log_error_monitor.sh 2>&1)\n"
      }
    }, {
      "_index" : "ossec-2017.12.10",
      "_type" : "ossec_filebeat",
      "_id" : "AWBA1ddz5tockKIFV_Lf",
      "_score" : 1.0,
      "_source" : {
        "message" : "** Alert 1512910921.534416: - syslog,errors,\n2017 Dec
10 13:02:01 (e-agree_micro-zoho) 10.20.4.145->/var/log/syslog\nRule: 1002
(level 2) -> 'Unknown problem somewhere in the system.'\nDec 10 08:02:01
micro-zoho CRON[12060]: (root) CMD (/bin/bash
/root/scripts/log_error_monitor.sh 2>&1)\n",
        "@version" : "1",
        "@timestamp" : "2017-12-10T14:30:31.546Z",
        "input_type" : "log",
        "beat" : {
          "name" : "oss-01-prd-usw",
          "hostname" : "oss-01-prd-usw",
          "version" : "5.6.3"
        },
        "tags" : [ "ossec-server", "beats_input_codec_plain_applied" ],
        "source" : "/var/ossec/logs/alerts/alerts.log",
        "offset" : 534703,
        "type" : "ossec_filebeat",
        "host" : "oss-01-prd-usw",
        "timestamp_seconds" : "1512910921.534416",
        "ossec_group" : "syslog,errors,",
        "syslog_timestamp" : "Dec 10 13:02:01",
        "reporting_host" : "e-agree_micro-zoho",
        "reporting_ip" : "10.20.4.145",
        "reporting_source" : "/var/log/syslog",
        "rule_number" : "1002",
        "severity" : "2",
        "signature" : "Unknown problem somewhere in the system.",
        "remaining_message" : "Dec 10 08:02:01 micro-zoho CRON[12060]:
(root) CMD (/bin/bash /root/scripts/log_error_monitor.sh 2>&1)\n",
        "real_message" : "Dec 10 08:02:01 micro-zoho CRON[12060]: (root)
CMD (/bin/bash /root/scripts/log_error_monitor.sh 2>&1)\n"
      }
    }, {
      "_index" : "ossec-2017.12.10",
      "_type" : "ossec_filebeat",
      "_id" : "AWBA1ddz5tockKIFV_Lh",
      "_score" : 1.0,
      "_source" : {
        "message" : "** Alert 1512910983.534987: - syslog,errors,\n2017 Dec
10 13:03:03 (e-agree_micro-zoho) 10.20.4.145->/var/log/syslog\nRule: 1002
(level 2) -> 'Unknown problem somewhere in the system.'\nDec 10 08:03:01
micro-zoho CRON[12163]: (root) CMD (/bin/bash
/root/scripts/log_error_monitor.sh 2>&1)\n",
        "@version" : "1",
        "@timestamp" : "2017-12-10T14:30:31.546Z",
        "offset" : 535274,
        "type" : "ossec_filebeat",
        "input_type" : "log",
        "beat" : {
          "name" : "oss-01-prd-usw",
          "hostname" : "oss-01-prd-usw",
          "version" : "5.6.3"
        },
        "source" : "/var/ossec/logs/alerts/alerts.log",
        "tags" : [ "ossec-server", "beats_input_codec_plain_applied" ],
        "host" : "oss-01-prd-usw",
        "timestamp_seconds" : "1512910983.534987",
        "ossec_group" : "syslog,errors,",
        "syslog_timestamp" : "Dec 10 13:03:03",
        "reporting_host" : "e-agree_micro-zoho",
        "reporting_ip" : "10.20.4.145",
        "reporting_source" : "/var/log/syslog",
        "rule_number" : "1002",
        "severity" : "2",
        "signature" : "Unknown problem somewhere in the system.",
        "remaining_message" : "Dec 10 08:03:01 micro-zoho CRON[12163]:
(root) CMD (/bin/bash /root/scripts/log_error_monitor.sh 2>&1)\n",
        "real_message" : "Dec 10 08:03:01 micro-zoho CRON[12163]: (root)
CMD (/bin/bash /root/scripts/log_error_monitor.sh 2>&1)\n"
      }
    }, {
      "_index" : "ossec-2017.12.10",
      "_type" : "ossec_filebeat",
      "_id" : "AWBA1ddz5tockKIFV_Li",
      "_score" : 1.0,
      "_source" : {
        "message" : "** Alert 1512910983.535274: - syslog,errors,\n2017 Dec
10 13:03:03 (e-agree_micro-zoho) 10.20.4.145->/var/log/syslog\nRule: 1002
(level 2) -> 'Unknown problem somewhere in the system.'\nDec 10 08:03:01
micro-zoho CRON[12167]: (root) CMD (/bin/bash
/root/scripts/check_error_pattern.sh)\n",
        "@version" : "1",
        "@timestamp" : "2017-12-10T14:30:31.546Z",
        "input_type" : "log",
        "beat" : {
          "name" : "oss-01-prd-usw",
          "hostname" : "oss-01-prd-usw",
          "version" : "5.6.3"
        },
        "tags" : [ "ossec-server", "beats_input_codec_plain_applied" ],
        "source" : "/var/ossec/logs/alerts/alerts.log",
        "offset" : 535558,
        "type" : "ossec_filebeat",
        "host" : "oss-01-prd-usw",
        "timestamp_seconds" : "1512910983.535274",
        "ossec_group" : "syslog,errors,",
        "syslog_timestamp" : "Dec 10 13:03:03",
        "reporting_host" : "e-agree_micro-zoho",
        "reporting_ip" : "10.20.4.145",
        "reporting_source" : "/var/log/syslog",
        "rule_number" : "1002",
        "severity" : "2",
        "signature" : "Unknown problem somewhere in the system.",
        "remaining_message" : "Dec 10 08:03:01 micro-zoho CRON[12167]:
(root) CMD (/bin/bash /root/scripts/check_error_pattern.sh)\n",
        "real_message" : "Dec 10 08:03:01 micro-zoho CRON[12167]: (root)
CMD (/bin/bash /root/scripts/check_error_pattern.sh)\n"
      }
    }, {
      "_index" : "ossec-2017.12.10",
      "_type" : "ossec_filebeat",
      "_id" : "AWBA1ddz5tockKIFV_Lo",
      "_score" : 1.0,
      "_source" : {
        "message" : "** Alert 1512911161.537039: - syslog,errors,\n2017 Dec
10 13:06:01 (e-agree_micro-zoho) 10.20.4.145->/var/log/syslog\nRule: 1002
(level 2) -> 'Unknown problem somewhere in the system.'\nDec 10 08:06:01
micro-zoho CRON[12500]: (root) CMD (/bin/bash
/root/scripts/check_error_pattern.sh)\n",
        "@version" : "1",
        "@timestamp" : "2017-12-10T14:30:31.546Z",
        "beat" : {
          "name" : "oss-01-prd-usw",
          "hostname" : "oss-01-prd-usw",
          "version" : "5.6.3"
        },
        "offset" : 537323,
        "input_type" : "log",
        "tags" : [ "ossec-server", "beats_input_codec_plain_applied" ],
        "source" : "/var/ossec/logs/alerts/alerts.log",
        "type" : "ossec_filebeat",
        "host" : "oss-01-prd-usw",
        "timestamp_seconds" : "1512911161.537039",
        "ossec_group" : "syslog,errors,",
        "syslog_timestamp" : "Dec 10 13:06:01",
        "reporting_host" : "e-agree_micro-zoho",
        "reporting_ip" : "10.20.4.145",
        "reporting_source" : "/var/log/syslog",
        "rule_number" : "1002",
        "severity" : "2",
        "signature" : "Unknown problem somewhere in the system.",
        "remaining_message" : "Dec 10 08:06:01 micro-zoho CRON[12500]:
(root) CMD (/bin/bash /root/scripts/check_error_pattern.sh)\n",
        "real_message" : "Dec 10 08:06:01 micro-zoho CRON[12500]: (root)
CMD (/bin/bash /root/scripts/check_error_pattern.sh)\n"
      }
    }, {
      "_index" : "ossec-2017.12.10",
      "_type" : "ossec_filebeat",
      "_id" : "AWBA1ddz5tockKIFV_Lp",
      "_score" : 1.0,
      "_source" : {
        "message" : "** Alert 1512911161.537323: - syslog,errors,\n2017 Dec
10 13:06:01 (e-agree_micro-zoho) 10.20.4.145->/var/log/syslog\nRule: 1002
(level 2) -> 'Unknown problem somewhere in the system.'\nDec 10 08:06:01
micro-zoho CRON[12501]: (root) CMD (/bin/bash
/root/scripts/log_error_monitor.sh 2>&1)\n",
        "@version" : "1",
        "@timestamp" : "2017-12-10T14:30:31.546Z",
        "input_type" : "log",
        "source" : "/var/ossec/logs/alerts/alerts.log",
        "beat" : {
          "name" : "oss-01-prd-usw",
          "hostname" : "oss-01-prd-usw",
          "version" : "5.6.3"
        },
        "tags" : [ "ossec-server", "beats_input_codec_plain_applied" ],
        "offset" : 537610,
        "type" : "ossec_filebeat",
        "host" : "oss-01-prd-usw",
        "timestamp_seconds" : "1512911161.537323",
        "ossec_group" : "syslog,errors,",
        "syslog_timestamp" : "Dec 10 13:06:01",
        "reporting_host" : "e-agree_micro-zoho",
        "reporting_ip" : "10.20.4.145",
        "reporting_source" : "/var/log/syslog",
        "rule_number" : "1002",
        "severity" : "2",
        "signature" : "Unknown problem somewhere in the system.",
        "remaining_message" : "Dec 10 08:06:01 micro-zoho CRON[12501]:
(root) CMD (/bin/bash /root/scripts/log_error_monitor.sh 2>&1)\n",
        "real_message" : "Dec 10 08:06:01 micro-zoho CRON[12501]: (root)
CMD (/bin/bash /root/scripts/log_error_monitor.sh 2>&1)\n"
      }
    } ]
  }
}

daniellee · December 18, 2017, 8:55am

You can look at the mapping like this:

curl http://privateip:9200/hide-*/_mapping

Here is an snippet from a mapping response with an example of a field that has is not_analyzed:

          "@metric": {
            "type": "string",
            "index": "not_analyzed"
          },

The above example is from an old version of Elasticsearch. Hopefully you are using ES 5.0 and have the text and keyword defaults instead of string:

pradeepchandran · December 21, 2017, 5:37am

Hi daniellee,

Issue resolved. actually i am used old version of ES(v2) and now i am
change it to ES(v5) and mapped index as keyword field .its working properly.
Thank you for your support.

Topic		Replies	Views
I can't display my elasticsearch index in table panel Elasticsearch	4	4673	February 9, 2020
Problem Faced with Elasticsearch in Grafana Elasticsearch elasticsearch , aws , grafana-ui	2	465	June 16, 2022
Table panel does not show data Table Panel	5	7366	October 13, 2022
Grafana table cannot show completed data sometimes Elasticsearch	8	1592	October 18, 2022
Elastic Search log message extraction and display in table panel in Grafana Configuration	0	253	July 2, 2020

Data missing in table

Related topics