How to remove columns from a bar chart which have no data

Elastic Stack Kibana
1

Continuing the discussion from Absence of some X.AXIS values in Bar chart or line chart:

I have the same basic question. I am also new fairly new to Kibana. I am using version 4.4.2.

My configuration is :
y-axis sum of technique_count
x-axis Terms aggregation on ne_type.raw order by y-axis metric
split chart on columns, Terms aggregation on ne_ip.raw order by y-axis metric
split bars Terms aggregation on technique.raw order by y-axis metric

This gives me basically what I want, which is IP address by NE_TYPE by discovery technique. However, the way I have it configured, Kibana is doing a cartesian product of ne_ip and ne_type, so If I have 1000 ne_ip addresses and 20 ne_types, i am getting 20 columns per IP address. In the actual data, one IP address will not be related to more than 2 ne_types, so in such an instance I get 20 columns for one IP address, but only 1 or 2 of those columns will actually have counts greater than zero and the other 18 will have zero counts. This means most of the columns that will fit on a viewable graph will be meaningless so I would like to remove those zero count columns from the visualization.

2

Hi Ken,

Are you mostly trying to get the data table? Or the chart?

I'm trying to test a similar scenario with my test data but I don't think it's similar enough. Can you post a screenshot?

Thanks,
Lee

3

Pasted image1920×1175 140 KB

Hopefully that is readable.

Here is a sample data set if that helps (the IPs have been changed to protect the innocent). The real data set is 41,000+ lines.

ne_ip,src_type,name,ne_type,alt_name,capability_code,technique,technique_count,source,Disposition
1.99.254.99,IP,MME_1.99.254.99,MME,,,mme-s1ap-1,159084916,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,MME_1.99.254.99,MME,,,mme-s1ap-2,494383,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,MME_1.99.254.99,MME,,,mme-s1ap-3,160923567,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,MME_1.99.254.99,MME,,,mme-s1ap-4,1203144,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,MME_1.99.254.99,MME,,,mme-s1ap-5,17027,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,MME_1.99.254.99,MME,,,mme-s1ap-6,15897,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,MME_1.99.254.99,MME,,,mme-s1ap-7,3336794,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,MME_1.99.254.99,MME,,,mme-s1ap-8,68,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,eNodeB_1.99.254.99,eNodeB,,,enodeb-s1ap-1,68,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,eNodeB_1.99.254.99,eNodeB,,,enodeb-s1ap-2,494441,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,eNodeB_1.99.254.99,eNodeB,,,enodeb-s1ap-3,9069570,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,eNodeB_1.99.254.99,eNodeB,,,enodeb-s1ap-4,1209516,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,eNodeB_1.99.254.99,eNodeB,,,enodeb-s1ap-5,17003,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,eNodeB_1.99.254.99,eNodeB,,,enodeb-s1ap-6,15871,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,eNodeB_1.99.254.99,eNodeB,,,enodeb-s1ap-7,3331547,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
1.99.254.99,IP,eNodeB_1.99.254.99,eNodeB,,,enodeb-s1ap-8,74,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
8.99.168.191,IP,GGSN_8.99.168.191,GGSN,,,ggsn-gtp-1,2,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
8.99.168.191,IP,GGSN_8.99.168.191,GGSN,,,ggsn-gtp-2,2,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
6.19.17.88,IP,MSC_6.19.17.88,MSC,,,msc-gsm_map-1,2109,MX-MEX-M1-CEM-TSA_PS-2-15Min-02242016_pcaps,
6.19.17.88,IP,MSC_6.19.17.88,MSC,,,msc-gsm_map-2,2600,MX-MEX-M1-CEM-TSA_PS-2-15Min-02242016_pcaps,
19.19.22.90,IP,GGSN_19.19.22.90,GGSN,,,ggsn-gtp-1,215,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
19.19.22.90,IP,GGSN_19.19.22.90,GGSN,,,ggsn-gtp-2,215,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
19.19.22.90,IP,SGW_19.19.22.90,SGW,,,sgw-gtpv2-1,1,lte.0223.gtpc_pcaps,
19.19.22.90,IP,SGW_19.19.22.90,SGW,,,sgw-gtpv2-1,2,MX-MEX-M1-CEM-TAA_PS-1_12102015_10GB_pcaps,
19.203.33.14,IP,MME_19.203.33.14,MME,,,mme-gtpv2-1,45158,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
19.203.33.14,IP,SGSN_19.203.33.14,SGSN,,,sgsn-gtp-1,60687,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,
19.203.33.14,IP,SGSN_19.203.33.14,SGSN,,,sgsn-gtp-2,60688,MX-TOL-M1-CEM-TSA_PS-2.20m_pcaps,

The lines above for the 1.99.254.99 IP is all the data in the file for the first IP shown in the screen shot. It has only MME and eNodeB ne_type records associated with it, but in the screen shot you can see columns for all 6 different ne_types that are found in the data set. I haven't tried the above data set myself yet, but I am assuming the issue is not a data size issue. I included some records for each of the 6 ne_types found in the real data set. Hopefully it will reproduce the issue.

In response to your question, I am mostly trying to get the bar chart to not show the ip/ne combinations that have no data associated with them.

4

Also, in case it matters, the IP addresses are stored in elasticsearch as strings, not IP addresses. All the values are strings except for technique_count, which is an integer.

5

Here is the screen shot using the test data set I provided.

Pasted image1920×1175 131 KB

For the first IP shown, there is only data for the first 2 ne_types, so I would like to remove the additional 4 ne_type columns so that I can display more of the relevant data. I would want to do the same for any ne_type columns that have no corresponding records in the data set.

6

Hi Ken,
I'm still trying to help you on this. I have your sample data loaded but haven't found a solution yet and I'm at a conference a few days this week.

Regards,
Lee

7

Hi Lee,
Have you had a chance to research this any further?

Thanks,
Ken

8

Hi Ken,
I spent some more time on it but didn't find a solution. I'll ask around and see if I can find some help.

Regards,
Lee

9

Hi Ken,
Someone else suggested going to the Advanced link in your aggregation and add {"min_doc_count" : 1}. Can you give that a try?

Thanks,
Lee

10

I added {"min_doc_count" : 1} to the JSON input section for the x-axis bucket, but it made no difference. I then added it to Split Chart and tried again, and then to Split Bars, but I see no change in my visualization at any point.

Thanks,
Ken