# How to calculate & draw metrics of scripted metrics

**URL:** <https://discuss.elastic.co/t/how-to-calculate-draw-metrics-of-scripted-metrics/305281>\
**Category:** Kibana\
**Tags:** vega\
**Created:** [May 20, 2022, 10:56am UTC](https://discuss.elastic.co/t/how-to-calculate-draw-metrics-of-scripted-metrics/305281 "2022-05-20T10:56:34Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ultimate](https://avatars.discourse-cdn.com/v4/letter/u/ba8739/32.png) [@ultimate](https://discuss.elastic.co/u/ultimate)\
**Post date:** [May 20, 2022, 10:56am UTC](https://discuss.elastic.co/t/how-to-calculate-draw-metrics-of-scripted-metrics/305281/1 "2022-05-20T10:56:34Z")

</div>

## Problem description

we have log files from different devices parsed into our Elasticsearch database, line by line. The log files are built as a ring buffer, so they always have a fixed size of 1000 lines. They can be manually exported whenever needed. After import and parsing in Elasticsearch each document represents a single line of a log file with the following information:

```
DeviceID: 12345
FileType: ErrorLog
FileTimestamp: 2022-05-10 01:23:45
LogTimestamp: 2022-05-05 01:23:45
LogMessage: something very important here

```

Now I want to have a statistic on the timespan that usually is covered by that fixed amount of lines. Because, depending on the intensity of the usage of the device, a varying amount of log entries is generated and the files can cover from just a few days to several months... But since the log files are split into individual lines it is not that trivial (I suppose).

**My goal is to have a chart that shows me a "histogram" of the different log file timespans...**

## First Try: Visualize library \> Data table

I started by creating a `Data table` in the `Visualize library` where I was able to aggregate the data as follows:

**I added 3 Buckets** --\> so I have all lines bucketed by their original file:

1. Split rows DeviceID.keyword
2. Split rows FileType.keyword
3. Split rows FileTimestamp

... **and 2 Metrics** --\> to show the log file timespan (I couldn't find a way to create a `max-min` metric, so I started with individual metrics for max and min):

1. Metric Min LogTimeStamp
2. Metric Max LogTimeStamp

This results in the following query:

```
{
  "aggs": {
	"2": {
	  "terms": {
		"field": "DeviceID.keyword",
		"order": {
		  "_key": "desc"
		},
		"size": 100
	  },
	  "aggs": {
		"3": {
		  "terms": {
			"field": "FileType.keyword",
			"order": {
			  "_key": "desc"
			},
			"size": 5
		  },
		  "aggs": {
			"4": {
			  "terms": {
				"field": "FileTimestamp",
				"order": {
				  "_key": "desc"
				},
				"size": 100
			  },
			  "aggs": {
				"1": {
				  "min": {
					"field": "LogTimeStamp"
				  }
				},
				"5": {
				  "max": {
					"field": "LogTimeStamp"
				  }
				}
			  }
			}
		  }
		}
	  }
	}
  },
  "size": 0,
  ...
}

```

... and this output:

```
DeviceID FileType FileTimestamp Min LogTimestamp Max LogTimestamp 
---------------------------------------------------------------------------------------------
12345 ErrorLog 2022-05-10 01:23:45 2022-04-10 01:23:45 2022-05-10 01:23:45
...

```

**Looks good so far!** The expected result would be exactly 1 month for this example.

**But my research showed, that it is not possible to add the desired metrics here, so I needed to try something else...**

## Second Try: Vizualize library \> Custom visualization (Vega-Lite)

So I started some more research and found out, that `vega` might be a possibility. I already was able to transfer the bucket part from the first attempt there and I also added a `scripted metric` to automatically `calculate the timespan` (instead of min & max), so far, so good. The request body looks as follows:

```
body: {
    "aggs": {
		"DeviceID": {
			"terms": { "field": "DeviceID.keyword" }, 
			"aggs": {
				"FileType": {
					"terms": { "field": "FileType.keyword" } ,
					"aggs": {
						"FileTimestamp": {
							"terms": { "field": "FileTimestamp" } ,
							"aggs": {
								"timespan": {
									"scripted_metric": {
										"init_script": "state.values = [];",
										"map_script": "state.values.add(doc['FileTimestamp'].value);",
										"combine_script": "long min = Long.MAX_VALUE; long max = 0; for (t in state.values) { long tms = t.toInstant().toEpochMilli(); if(tms > max) max = tms; if(tms < min) min = tms; } return [max,min];",
										"reduce_script": "long min = Long.MAX_VALUE; long max = 0; for (a in states) { if(a[0] > max) max = a[0]; if(a[1] < min) min = a[1]; } return max-min;"
									}
								}
							}
						}
					}
				}
			}
		}
    },      
    "size": 0,
}

```

...with this response (unnecessary information removed to reduce complexity):

```
{
	"took": 12245,
	"timed_out": false,
	"_shards": { ... },
	"hits": { ...	},
	"aggregations": {
		"DeviceID": {
			"buckets": [
				{
					"key": "12345",
					"FileType": {
						"buckets": [
							{
								"key": "ErrorLog",
								"FileTimeStamp": {
									"buckets": [
										{
											"key": 1638447972000,
											"key_as_string": "2021-12-02T12:26:12.000Z",
											"doc_count": 1000,
											"timespan": {
												"value": 31339243240
											}
										},
										{
											"key": 1636023881000,
											"key_as_string": "2021-11-04T11:04:41.000Z",
											"doc_count": 1000,
											"timespan": {
												"value": 31339243240
											}
										}
									]
								}
							},
							{
								"key": "InfoLog",
								"FileTimeStamp": {
									"buckets": [
										{
											"key": 1635773438000,
											"key_as_string": "2021-11-01T13:30:38.000Z",
											"doc_count": 1000,
											"timespan": {
												"value": 2793365000
											}
										},
										{
											"key": 1636023881000,
											"key_as_string": "2021-11-04T11:04:41.000Z",
											"doc_count": 1000,
											"timespan": {
												"value": 2643772000
											}
										}
									]
								}
							}
						]
					  }
				},
				{
					"key": "12346",
					"FileType": {
						...
					}
				},
				...
			]
		}
	}
}

```

**Yeah, it seems to work! Now I have the timespan for each original log file.**

## Question

Now I am stuck with:

1. I want to **average the timespans for each original log file** (identified via the combination of DeviceID + FileType + FileTimeStamp) to prevent devices with multiple log files imported to have a higher weight, than devices with only 1 log file imported. I tried to `add another aggregation` for the avg, but I couldn't figure out where to put so that the result of the `scripted_metric` is used. My closest attempt was to put a `avg_bucket` after the FileTimeStamp bucket:

Request:

```
body: {
    "aggs": {
        "DeviceID": {
            "terms": { "field": "DeviceID.keyword" }, 
            "aggs": {
                "FileType": {
                    "terms": { "field": "FileType.keyword" } ,
                    "aggs": {
                        "FileTimestamp": {
                            "terms": { "field": "FileTimestamp" } ,
                            "aggs": {
                                "timespan": {
                                    "scripted_metric": {
                                        "init_script": "state.values = [];",
                                        "map_script": "state.values.add(doc['FileTimestamp'].value);",
                                        "combine_script": "long min = Long.MAX_VALUE; long max = 0; for (t in state.values) { long tms = t.toInstant().toEpochMilli(); if(tms > max) max = tms; if(tms < min) min = tms; } return [max,min];",
                                        "reduce_script": "long min = Long.MAX_VALUE; long max = 0; for (a in states) { if(a[0] > max) max = a[0]; if(a[1] < min) min = a[1]; } return max-min;"
                                    }
                                }
                            }
                        },
// new part - start
                        "avg_timespan": {
                            "avg_bucket": {
                                "buckets_path": "FileTimestamp>timespan"
                            }
                        }
// new part - end
                    }
                }
            }
        }
    },      
    "size": 0,
}

```

But I receive the following error:

> EsError: buckets\_path must reference either a number value or a single value numeric metric aggregation, got: [InternalScriptedMetric] at aggregation [timespan]

**So is it the right spot? (but not applicable to a scripted metric) Or am I on the wrong path?**

1. I need to **plot all this** , but I can't find my way through all the buckets, etc.  
I read about flattening (which would probably be a good idea, so (if done by the server) the result would not be that complex), but don't know where and how to put the flattening transformation.

I imagine the resulting chart like this:

- **x-axis = log file timespan** , where the timespan is "binned" according to a given step size (e.g. 1 day), so there are only bars for each bin (1 = 0-1days, 2 = 1-2days, 3 = 2-3days, etc.) and not for all the different timespans of log files
- **y-axis = count of devices**
- **type: lines or vertical bars, split by file type**

e.g. something like this:

[![log-file-timespan-histogram](https://us1.discourse-cdn.com/elastic/original/3X/0/d/0dc2e044b93d466d600e2761a4686cca7f2fba60.png)](https://i.stack.imgur.com/NRMfK.png)

**Any help is really appreciated! Thanks in advance!**

Note: I also posted this on Stackoverflow: [https://stackoverflow.com/questions/72317538](https://stackoverflow.com/questions/72317538)

---

<div class="post-metadata">

**Author:** ![ultimate](https://avatars.discourse-cdn.com/v4/letter/u/ba8739/32.png) [@ultimate](https://discuss.elastic.co/u/ultimate)\
**Post date:** [May 30, 2022, 10:07am UTC](https://discuss.elastic.co/t/how-to-calculate-draw-metrics-of-scripted-metrics/305281/2 "2022-05-30T10:07:26Z")

</div>

If you have the privileges to create a [transform](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html), then the elastic painless example [Getting duration by using bucket script](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-painless-examples.html#painless-bucket-script) can do exactly what you want. It creates a new index where all documents are grouped according to your needs.

**To create the transform** :

1. go to _Stack Management \> Transforms \> + Create a transform_
2. select **Edit JSON config** for the _Pivot configuration object_
3. paste & apply the JSON below
4. check whether the result is the expected in the _Transform preview_
5. fill out the rest of the transform details + save the transform

**JSON config**

```
{
  "group_by": {
	"DeviceID": {
	  "terms": {
		"field": "DeviceID.keyword"
	  }
	},
	"FileType": {
	  "terms": {
		"field": "FileType.keyword"
	  }
	},
	"FileTimestamp": {
	  "terms": {
		"field": "FileTimestamp"
	  }
	}
  },
  "aggregations": {
	"TimeStampStats": {
	  "stats": {
		"field": "@timestamp"
	  }
	},
	"TimeSpan": {
	  "bucket_script": {
		"buckets_path": {
		  "first": "TimeStampStats.min",
		  "last": "TimeStampStats.max"
		},
		"script": "params.last - params.first"
	  }
	}
  }
}

```

Now you can create a chart from the new index, for example with these settings:

- Vertical Bars
- Metrics:
  - Y-axis = "Count"

- Buckets:
  - X-axis = "TimeSpan"
  - Split series = "FileType"

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 27, 2022, 10:08am UTC](https://discuss.elastic.co/t/how-to-calculate-draw-metrics-of-scripted-metrics/305281/3 "2022-06-27T10:08:06Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.