# Help with a Custom Kibana Visualization :: Combine data in a table, then multiply two columns?
**URL:** https://discuss.elastic.co/t/help-with-a-custom-kibana-visualization-combine-data-in-a-table-then-multiply-two-columns/202557
**Category:** Kibana
**Created:** [October 7, 2019, 3:30pm UTC](https://discuss.elastic.co/t/help-with-a-custom-kibana-visualization-combine-data-in-a-table-then-multiply-two-columns/202557 "2019-10-07T15:30:50Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@redapplesonly](https://discuss.elastic.co/u/redapplesonly)
#### Post date: [October 7, 2019, 3:30pm UTC](https://discuss.elastic.co/t/help-with-a-custom-kibana-visualization-combine-data-in-a-table-then-multiply-two-columns/202557/1 "2019-10-07T15:30:50Z")
Hello Kibana gurus,
Boy, I need your help. I have built a network sFlow collector, which collects a lot of network data. That data is successfully flowing into Elasticsearch & Kibana. Let’s say for a toy example, I can see the following data in Kibana:
```
Sender Receiver Protocol Sample Rate Total Length
10.10.10.10 20.20.20.20 TCP 64 1500
10.10.10.10 30.30.30.30 UDP 64 1500
10.10.10.10 20.20.20.20 TCP 64 1500
20.20.20.20 10.10.10.10 TCP 64 1500
10.10.10.10 20.20.20.20 TCP 64 1500
10.10.10.10 20.20.20.20 TCP 64 100
10.10.10.10 30.30.30.30 UDP 64 1500
```
I can combine that data by merging whenever Sender/Receiver/Protocol/Sample Rate stats are the same and summing “Total Length”:
```
Sender Receiver Protocol Sample Rate Total Length
10.10.10.10 20.20.20.20 TCP 64 4600
10.10.10.10 30.30.30.30 UDP 64 3000
20.20.20.20 10.10.10.10 TCP 64 1500
```
What’s more, because this is sampled data, I really need to multiply those last columns:
```
Sender Receiver Protocol Total Data Sent
10.10.10.10 20.20.20.20 TCP 294400
10.10.10.10 30.30.30.30 UDP 192000
20.20.20.20 10.10.10.10 TCP 96000
```
This is highly useful. What I need is a Kibana Visualization that produces the last chart.
I’ve been reading through the Kibana documentation and tutorials and just haven’t gotten very far at all. Obviously, I want a Data Table type. My buckets would be Sender / Receiver / Protocol / Sample Rate… I assume. But I’m uncertain how to specify the Metrics here: These would be… sums? Unique counts? I’m not sure.
Can anyone point me to a tutorial or offer some advice on how to build this kind of Visualization? Is this something that can be done in one Visualization, or would I have to potentially build visualizations on top of one another, as I might have to build a MySQL query?
Many thanks!
---
### Author:  [@redapplesonly](https://discuss.elastic.co/u/redapplesonly)
#### Post date: [October 14, 2019, 6:39pm UTC](https://discuss.elastic.co/t/help-with-a-custom-kibana-visualization-combine-data-in-a-table-then-multiply-two-columns/202557/2 "2019-10-14T18:39:41Z")
Sorry, does anyone have any thoughts on this? Any advice - even "Looks impossible!" will be appreciated. Thanks
---
### Author:  [@lukas](https://discuss.elastic.co/u/lukas)
#### Post date: [October 17, 2019, 8:02pm UTC](https://discuss.elastic.co/t/help-with-a-custom-kibana-visualization-combine-data-in-a-table-then-multiply-two-columns/202557/3 "2019-10-17T20:02:01Z")
What you really need here is a scripted field that multiplies sample rate \* total length, which gives you total data sent. Then, you can create a visualization that just sums up the value for that scripted field.
Your buckets would just be two separate terms aggregations for sender & receiver.
I'm happy to give more information about creating scripted fields if you need it. 🙂
---
### Author:  [@redapplesonly](https://discuss.elastic.co/u/redapplesonly)
#### Post date: [October 18, 2019, 5:24pm UTC](https://discuss.elastic.co/t/help-with-a-custom-kibana-visualization-combine-data-in-a-table-then-multiply-two-columns/202557/4 "2019-10-18T17:24:30Z")
Hi Lukas,
Thanks for looking into this. Yes, I'd love it if you can point me to documentation about scripted fields. I've tried sitting down and reading the Kibana manual, but its huge and its very easy to drill down into a topic which you really don't need. Any guidance you can provide to help me focus on specific topics that will help me will be wildly appreciated. 🙂
Thank you!
---
### Author:  [@lukas](https://discuss.elastic.co/u/lukas)
#### Post date: [October 18, 2019, 8:08pm UTC](https://discuss.elastic.co/t/help-with-a-custom-kibana-visualization-combine-data-in-a-table-then-multiply-two-columns/202557/5 "2019-10-18T20:08:36Z")
Sure, no worries.
If you go into the Management page, then click on Index Patterns, and select the index pattern you've set up, you'll see a "Scripted Fields" tab.
For the scripted field, you'll want to create a new scripted field with a script similar to this:
```auto
doc['sample_rate'].value * doc['total_length'].value
```
Could you give that a try and let me know if it works?
---
### Author:  [@redapplesonly](https://discuss.elastic.co/u/redapplesonly)
#### Post date: [October 21, 2019, 4:22pm UTC](https://discuss.elastic.co/t/help-with-a-custom-kibana-visualization-combine-data-in-a-table-then-multiply-two-columns/202557/6 "2019-10-21T16:22:07Z")
Thanks Lukas,
So I'm completely new to Scripted Fields. I'd never heard of the painless programming language until today. So this might be a bumpy discussion.
If I'm following you correctly, my first step would be to define a new Scripted Field, which I'll call "flowSum." On Kibana --\> Management --\> Index Patterns --\> Create Scripted Field, I'd set the following:
**Name:** flowSum
**Language:** painless
**Type:** number
**Format Default:** default
**Popularity:** 0
**Script:**
```auto
GET _flowSum
{
"query": {
"function_score": {
"script_score": {
"script": {
"lang": "painless",
"source": """
int sum = doc['sample_rate'].value * doc['total_length'].value
return sum;
"""
}
}
}
}
}
```
In the "Preview" pane, it looks like I've messed up the syntax of that first line. That aside, do you see any problems with my general approach?
Then, once this step is completed and I have a new, calculated field named "flowSum," then I could build a new Visualization, using Sender / Receiver / Protocol as my buckets?
Many thanks!
---
### Author:  [@redapplesonly](https://discuss.elastic.co/u/redapplesonly)
#### Post date: [October 29, 2019, 7:22pm UTC](https://discuss.elastic.co/t/help-with-a-custom-kibana-visualization-combine-data-in-a-table-then-multiply-two-columns/202557/7 "2019-10-29T19:22:00Z")
I got this working... Posting the complete solution here, in case anyone else is following in my footsteps. Thanks Lukas!
```
STEP ONE: Define an Scripted Field
Management --> Index Patterns --> (select your current index) --> "Scripted Fields" Tab
"Add scripted field"
Fill out the form:
Language: painless
Type: number
Format: (default)
Popularity: 0
Script:
doc['SamplingRate'].value * doc['TotalLen'].value
How to read the script:
"doc['SamplingRate'].value" == "Consult the data record/doc, find statistic "SamplingRate," get its value
Make your changes and click "CREATE FIELD"
STEP TWO: Assemble the Visualization
Visualizations --> "Create new visualization" --> Data Table --> (select your current index)
Create Four Buckets:
For IP Addresses:
"Add Buckets" --> Split Rows --> Aggregation: Terms --> Field: (your field here) --> Metric: Alphabetical --> (Everything else default)
For Numbers:
"Add Buckets" --> Split Rows --> Aggregation: Terms --> Field: (your field here) --> Metric: Descending --> (Everything else default)
Now Add Metrics:
Metric --> Sum --> Field: (Your created Scripted Field, above)
Metric --> Min --> Field: @timestamp
Metric --> Max --> Field: @timestamp
Click "Save," upper left
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [November 26, 2019, 7:22pm UTC](https://discuss.elastic.co/t/help-with-a-custom-kibana-visualization-combine-data-in-a-table-then-multiply-two-columns/202557/8 "2019-11-26T19:22:02Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.