In some visualizations, the sorting appears to not respect the selected values. for example, when using metricbeat i have a scripted field for "hour of day", "day of week" and "sundayOfWeek". Depending on how i filter my data, if i use the "hour of day" as the x axis, even if i set the data to be sorted alphatbetically i sometimes get wierd sort results like 7,8,9,10,1,2,3,14,15,16, etc
This tends to happen most when i'm splitting series/charts (e.g. chart on servername, series on sundayOfWeek).
I believe the issue is because the sort is done as each result set comes back, if the first result set does not include a particular time, it is excluded from the sort, if a later server includes it then it is placed after the first group.
in the graph above, you can see the hour of day ends with 9,10 but that should have been the 2nd/3rd column, and the day of week is 04/25, 04/18, 05/02, 04//11, 04/04 there is no rhyme or reason to this sorting.
here are my sort definitions
Request:
{
"aggs": {
"2": {
"terms": {
"script": {
"source": "doc['@timestamp'].value.hour",
"lang": "painless"
},
"order": {
"_key": "asc"
},
"value_type": "float",
"size": 24
},
"aggs": {
"3": {
"terms": {
"script": {
"source": "doc['@timestamp'].value.plusDays(-1*(doc['@timestamp'].value.dayOfWeek)).truncatedTo(ChronoUnit.DAYS)",
"lang": "painless"
},
"order": {
"_key": "desc"
},
"value_type": "date",
"size": 5
},
"aggs": {
"4": {
"terms": {
"field": "agent.hostname",
"order": {
"_key": "asc"
},
"size": 8
}
}
}
}
}
}
},
"size": 0,
"stored_fields": [
"*"
],
"script_fields": {
"dayOfWeek": {
"script": {
"source": "[\"\", \"1Mon \", \"2Tue \", \"3Wed \", \"4Thu \", \"5Fri \", \"6Sat \", \"7Sun \"][doc['@timestamp'].value.dayOfWeek]",
"lang": "painless"
}
},
"hourOfWeek": {
"script": {
"source": "[\"\", \"1Mon \", \"2Tue \", \"3Wed \", \"4Thu \", \"5Fri \", \"6Sat \", \"7Sun \"][doc['@timestamp'].value.dayOfWeek] + String.format( \"%02d\", new def[]{ doc['@timestamp'].value.hour } )",
"lang": "painless"
}
},
"hourOfDay": {
"script": {
"source": "doc['@timestamp'].value.hour",
"lang": "painless"
}
},
"sundayOfWeek": {
"script": {
"source": "doc['@timestamp'].value.plusDays(-1*(doc['@timestamp'].value.dayOfWeek)).truncatedTo(ChronoUnit.DAYS)",
"lang": "painless"
}
}
},
"docvalue_fields": [
{
"field": "@timestamp",
"format": "date_time"
},
{
"field": "azure.app_insights.end_date",
"format": "date_time"
},
{
"field": "azure.app_insights.start_date",
"format": "date_time"
},
{
"field": "azure.billing.usage_date",
"format": "date_time"
},
{
"field": "azure.billing.usage_end",
"format": "date_time"
},
{
"field": "azure.billing.usage_start",
"format": "date_time"
},
{
"field": "ceph.monitor_health.last_updated",
"format": "date_time"
},
{
"field": "docker.container.created",
"format": "date_time"
},
{
"field": "docker.healthcheck.event.end_date",
"format": "date_time"
},
{
"field": "docker.healthcheck.event.start_date",
"format": "date_time"
},
{
"field": "docker.image.created",
"format": "date_time"
},
{
"field": "event.created",
"format": "date_time"
},
{
"field": "event.end",
"format": "date_time"
},
{
"field": "event.ingested",
"format": "date_time"
},
{
"field": "event.start",
"format": "date_time"
},
{
"field": "file.accessed",
"format": "date_time"
},
{
"field": "file.created",
"format": "date_time"
},
{
"field": "file.ctime",
"format": "date_time"
},
{
"field": "file.mtime",
"format": "date_time"
},
{
"field": "kubernetes.container.start_time",
"format": "date_time"
},
{
"field": "kubernetes.event.metadata.timestamp.created",
"format": "date_time"
},
{
"field": "kubernetes.event.timestamp.first_occurrence",
"format": "date_time"
},
{
"field": "kubernetes.event.timestamp.last_occurrence",
"format": "date_time"
},
{
"field": "kubernetes.node.start_time",
"format": "date_time"
},
{
"field": "kubernetes.pod.start_time",
"format": "date_time"
},
{
"field": "kubernetes.service.created",
"format": "date_time"
},
{
"field": "kubernetes.storageclass.created",
"format": "date_time"
},
{
"field": "kubernetes.system.start_time",
"format": "date_time"
},
{
"field": "mongodb.replstatus.server_date",
"format": "date_time"
},
{
"field": "mongodb.status.background_flushing.last_finished",
"format": "date_time"
},
{
"field": "mongodb.status.local_time",
"format": "date_time"
},
{
"field": "mssql.transaction_log.stats.backup_time",
"format": "date_time"
},
{
"field": "mysql.performance.events_statements.last.seen",
"format": "date_time"
},
{
"field": "nats.server.time",
"format": "date_time"
},
{
"field": "package.installed",
"format": "date_time"
},
{
"field": "php_fpm.pool.start_time",
"format": "date_time"
},
{
"field": "php_fpm.process.start_time",
"format": "date_time"
},
{
"field": "postgresql.activity.backend_start",
"format": "date_time"
},
{
"field": "postgresql.activity.query_start",
"format": "date_time"
},
{
"field": "postgresql.activity.state_change",
"format": "date_time"
},
{
"field": "postgresql.activity.transaction_start",
"format": "date_time"
},
{
"field": "postgresql.bgwriter.stats_reset",
"format": "date_time"
},
{
"field": "postgresql.database.stats_reset",
"format": "date_time"
},
{
"field": "process.parent.start",
"format": "date_time"
},
{
"field": "process.start",
"format": "date_time"
},
{
"field": "system.process.cpu.start_time",
"format": "date_time"
},
{
"field": "system.service.state_since",
"format": "date_time"
},
{
"field": "tls.client.not_after",
"format": "date_time"
},
{
"field": "tls.client.not_before",
"format": "date_time"
},
{
"field": "tls.server.not_after",
"format": "date_time"
},
{
"field": "tls.server.not_before",
"format": "date_time"
},
{
"field": "zookeeper.server.version_date",
"format": "date_time"
}
],
"_source": {
"excludes": []
},
"query": {
"bool": {
"must": [],
"filter": [
{
"bool": {
"filter": [
{
"bool": {
"should": [
{
"range": {
"system.cpu.total.norm.pct": {
"gt": 0.8
}
}
}
],
"minimum_should_match": 1
}
},
{
"bool": {
"should": [
{
"query_string": {
"fields": [
"agent.hostname"
],
"query": "SERVER*"
}
}
],
"minimum_should_match": 1
}
}
]
}
},
{
"range": {
"@timestamp": {
"gte": "2021-04-05T00:00:00.000Z",
"lte": "2021-05-09T23:59:59.999Z",
"format": "strict_date_optional_time"
}
}
}
],
"should": [],
"must_not": []
}
}
}