Help with a Custom Kibana Visualization :: Combine data in a table, then multiply two columns?

#1

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!

#2

Sorry, does anyone have any thoughts on this? Any advice - even "Looks impossible!" will be appreciated. Thanks

#3

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. :slight_smile:

#4

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. :slight_smile:

Thank you!

#5

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:

doc['sample_rate'].value * doc['total_length'].value

Could you give that a try and let me know if it works?

#6

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:

   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!

#7

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
#8

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