# Get average number of spans (queries) in a transaction grouped by path

**URL:** <https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349>\
**Category:** APM\
**Created:** [April 3, 2020, 7:15am UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349 "2020-04-03T07:15:51Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Joren\_Inghelbrecht](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joren_inghelbrecht/32/45922_2.png) [@Joren\_Inghelbrecht](https://discuss.elastic.co/u/Joren_Inghelbrecht)\
**Post date:** [April 3, 2020, 7:15am UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/1 "2020-04-03T07:15:51Z")

</div>

Elastic deployment version: v7.6.1

I'm trying to create a visualization which gives me the average number of queries per request. So basically what I want to see is:

- GET /users/ =\> 5 queries
- POST /users/ =\> 3 queries
- GET /users/:id/books =\> 555 queries (N+1 query problem)

Our Java backend application is using the Java apm agent and everything works as expected. An example transaction:

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/2/5260af90a182e330a67e6681d431e0a13f3ff960.png)

**Option 1:**

I found in the docs that there is a `transaction.span_count.started` property which gives me what I need (no all spans are queries, but that's not really an issue). I can also see the property when I check out the doc of the request:

![image](https://us1.discourse-cdn.com/elastic/original/3X/9/d/9d5be1afdaef0889a9d4cac825f75c0781b21781.png)

However, this seems to be an **unmapped field** , so I cannot use it in a visualization. I searched for "No cached mapping for this field" in this forum/Goolge/Github, but none of the solutions worked. This is what I tried:

- Refresh index in Kibana management
- Remove index (it is automatically added again when going to the apm tab)

The field is mentioned in the docs:

- [https://www.elastic.co/guide/en/apm/server/7.6/transaction-indices.html](https://www.elastic.co/guide/en/apm/server/7.6/transaction-indices.html)

But it's missing here:

- [https://www.elastic.co/guide/en/apm/server/7.6/exported-fields-apm-transaction.html](https://www.elastic.co/guide/en/apm/server/7.6/exported-fields-apm-transaction.html)
- [https://github.com/elastic/apm-server/blob/07f35ab548b5338086db2a061a727826359c7166/model/transaction/\_meta/fields.yml](https://github.com/elastic/apm-server/blob/07f35ab548b5338086db2a061a727826359c7166/model/transaction/_meta/fields.yml)

Since the apm index is automatically created, I'm not sure if I should change it manually. I noticed that the field is not in the mapping:

```auto
"span_count": {
    "properties": {
        "dropped": {
            "type": "long"
        }
    }
},

```

And that the transaction mapping is not dynamic:

```auto
"transaction": {
    "dynamic": "false",
        "properties": {
....

```

So, should I change this mapping manually or is there something missing in apm-server? And if I change it manually, will it be overwritten by apm-server?

**Option 2:**

Don't use the `transaction.span_count.started` field.

In this case, I would need to do something like this:

1. count # of spans of with `span.action: query`, group them by `transaction.id`
2. annotate every transaction with `url.path` (the property is not available on query spans, so I would need to get it somehow from the request span of that transaction)
3. take the average of the # of spans and group them by `url.path`

I'm quite new to Kibana, so I'm not certain how I would achieve this. I was able to create a visualization to get the slowest requests based on the average transaction duration, but that's a single group by.

---

<div class="post-metadata">

**Author:** ![felixbarny](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felixbarny/32/27341_2.png) [@felixbarny](https://discuss.elastic.co/u/felixbarny)\
**Post date:** [April 3, 2020, 10:50am UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/2 "2020-04-03T10:50:20Z")

</div>

You could try using the metrics used for the "Time spent by span type" graph (`span.self_time`):

[https://www.elastic.co/guide/en/apm/agent/java/current/metrics.html#metrics-application](https://www.elastic.co/guide/en/apm/agent/java/current/metrics.html#metrics-application)

That also lets you filter by the types of spans you are interested in. For example `type: "db" and subtype: "mysql"`.

If that doesn't work out, you can customize the index mapping with APM Server settings: [https://www.elastic.co/guide/en/apm/server/current/configuration-template.html](https://www.elastic.co/guide/en/apm/server/current/configuration-template.html)

---

<div class="post-metadata">

**Author:** ![Joren\_Inghelbrecht](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joren_inghelbrecht/32/45922_2.png) [@Joren\_Inghelbrecht](https://discuss.elastic.co/u/Joren_Inghelbrecht)\
**Post date:** [April 3, 2020, 11:50am UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/3 "2020-04-03T11:50:26Z")

</div>

Thank you for the suggestions!

Seems the `append_fields` setting is not available on elastic cloud: [https://www.elastic.co/guide/en/cloud/current/ec-manage-apm-settings.html](https://www.elastic.co/guide/en/cloud/current/ec-manage-apm-settings.html)

Any idea why the field is not in fields.yml by default? [https://github.com/elastic/apm-server/blob/master/model/transaction/\_meta/fields.yml](https://github.com/elastic/apm-server/blob/master/model/transaction/_meta/fields.yml)

Maybe I'm also overthinking it and I don't need to average over transactions of the same path, but it would be useful to directly see the path.

---

<div class="post-metadata">

**Author:** ![felixbarny](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felixbarny/32/27341_2.png) [@felixbarny](https://discuss.elastic.co/u/felixbarny)\
**Post date:** [April 3, 2020, 12:23pm UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/4 "2020-04-03T12:23:38Z")

</div>

> [@Joren\_Inghelbrecht](#):
>
> Seems the `append_fields` setting is not available on elastic cloud: [Edit APM user settings | Elasticsearch Service Documentation | Elastic](https://www.elastic.co/guide/en/cloud/current/ec-manage-apm-settings.html)

I'd suggest to contact support then. Maybe they can set it for you.

Not all fields are indexed by default. That is to save space for fields you don't normally use for aggregations. However, your use case seems like it could benefit from that field being indexed.

Did you try out the `span.self_time` metric-based approach?

---

<div class="post-metadata">

**Author:** ![Joren\_Inghelbrecht](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joren_inghelbrecht/32/45922_2.png) [@Joren\_Inghelbrecht](https://discuss.elastic.co/u/Joren_Inghelbrecht)\
**Post date:** [April 3, 2020, 2:22pm UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/5 "2020-04-03T14:22:14Z")

</div>

Yes, yes, perfect!

I took me a while to figure it out, but it is exactly what I need.

For anyone else who is interested, create a new visualization with:

- Metric: average `span.self_time.count`
- Bucket: split rows on `transaction.name` (group by), which is in this case our java method

Filter on `span.type: db`.

So I won't need the extra mapping. Thanks a lot for pointing me in the right direction @felixbarny!

---

<div class="post-metadata">

**Author:** ![felixbarny](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felixbarny/32/27341_2.png) [@felixbarny](https://discuss.elastic.co/u/felixbarny)\
**Post date:** [April 3, 2020, 4:46pm UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/6 "2020-04-03T16:46:25Z")

</div>

Sounds like something that could be very useful for lots of people.

@gil do you think including such a visualization in the curated UI would make sense?

@Joren_Inghelbrecht would you mind adding this visualization, including a screenshot, to the [https://github.com/elastic/apm-contrib](https://github.com/elastic/apm-contrib) repo?

---

<div class="post-metadata">

**Author:** ![Joren\_Inghelbrecht](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joren_inghelbrecht/32/45922_2.png) [@Joren\_Inghelbrecht](https://discuss.elastic.co/u/Joren_Inghelbrecht)\
**Post date:** [April 4, 2020, 9:04am UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/7 "2020-04-04T09:04:24Z")

</div>

@felixbarny I'd love to contribute. You want me to add an export of the visualization and some screenshots in an issue? I can open a PR too, but I'm not sure where the visualization should be placed.

---

<div class="post-metadata">

**Author:** ![felixbarny](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felixbarny/32/27341_2.png) [@felixbarny](https://discuss.elastic.co/u/felixbarny)\
**Post date:** [April 4, 2020, 10:55am UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/8 "2020-04-04T10:55:43Z")

</div>

A PR would be great. Seems this would be the best place to add it: [https://github.com/elastic/apm-contrib/tree/master/kibana](https://github.com/elastic/apm-contrib/tree/master/kibana)

We can fine tune the file locations in the PR.

A 7.x version is enough and a screenshot of the visualization would be great 👍🏻

---

<div class="post-metadata">

**Author:** ![multazim](https://avatars.discourse-cdn.com/v4/letter/m/5daacb/32.png) [@multazim](https://discuss.elastic.co/u/multazim)\
**Post date:** [April 5, 2020, 8:28am UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/9 "2020-04-05T08:28:24Z")

</div>

@felixbarny @gil I think it would be great if "Filters" section can let us filter by span type. This way we could have separate charts for DB, external http calls etc and a list of calls of that particular span type. This would help us separately drill down each component of the service.

I do see a recent ticket similar to this: [https://github.com/elastic/kibana/issues/40304](https://github.com/elastic/kibana/issues/40304)

---

<div class="post-metadata">

**Author:** ![gil](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gil/32/41911_2.png) [@gil](https://discuss.elastic.co/u/gil)\
**Post date:** [April 9, 2020, 12:27pm UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/10 "2020-04-09T12:27:40Z")

</div>

Just back from vacation -

> do you think including such a visualization in the curated UI would make sense?

I'm happy to see it was straightforward to build the visualization in kibana, that's one of the benefits of storing APM data in the Elastic stack. It might make sense to incorporate this into the curated UI but would be happy for it to land in apm-contrib asap.

> "Filters" section can let us filter by span type

Yes! Thanks for the feedback here and in the linked issue.

---

<div class="post-metadata">

**Author:** ![multazim](https://avatars.discourse-cdn.com/v4/letter/m/5daacb/32.png) [@multazim](https://discuss.elastic.co/u/multazim)\
**Post date:** [April 10, 2020, 4:36pm UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/11 "2020-04-10T16:36:51Z")

</div>

can you please put your visualization in apm-contrib.

---

<div class="post-metadata">

**Author:** ![Joren\_Inghelbrecht](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joren_inghelbrecht/32/45922_2.png) [@Joren\_Inghelbrecht](https://discuss.elastic.co/u/Joren_Inghelbrecht)\
**Post date:** [April 10, 2020, 8:35pm UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/12 "2020-04-10T20:35:25Z")

</div>

I had a very busy week, so will try to get back to this tomorrow.

---

<div class="post-metadata">

**Author:** ![multazim](https://avatars.discourse-cdn.com/v4/letter/m/5daacb/32.png) [@multazim](https://discuss.elastic.co/u/multazim)\
**Post date:** [April 12, 2020, 4:10pm UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/13 "2020-04-12T16:10:31Z")

</div>

Please do let us know here once you have done it.

---

<div class="post-metadata">

**Author:** ![Joren\_Inghelbrecht](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joren_inghelbrecht/32/45922_2.png) [@Joren\_Inghelbrecht](https://discuss.elastic.co/u/Joren_Inghelbrecht)\
**Post date:** [April 13, 2020, 8:52am UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/14 "2020-04-13T08:52:09Z")

</div>

@felixbarny @gil @multazim PR can be found here: [https://github.com/elastic/apm-contrib/pull/39](https://github.com/elastic/apm-contrib/pull/39)

---

<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:** [May 4, 2020, 4:52am UTC](https://discuss.elastic.co/t/get-average-number-of-spans-queries-in-a-transaction-grouped-by-path/226349/15 "2020-05-04T04:52:13Z")

</div>

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