How to create a Vega bar graph with the unique count of devices?

[Chris6 (Chris)]

Hello,
I have some events that where created:

In file 1:
{"souceNetwork":"cc567", "sourceDevice":"z01", "destinationNetwork":"cc567", "destinationDevice":"z02", "path":"/user/file1"}
{"sourceNetwork":"cc567", "sourceDevice":"z01", "destinationNetwork":"cc567", "destinationDevice":"z06", "path":"/user/file1"}
{"sourceNetwork":"cc567", "sourceDevice":"z02", "destinationNetwork":"cc567", "destinationDevice":"z06", "path":"/user/file1"}
{"sourceNetwork":"cc567", "sourceDevice":"z01", "destinationNetwork":"cc567", "destinationDevice":"z04", "path":"/user/file1"}

In file2:
{"sourceNetwork":"cc567", "sourceDevice":"z01", "destinationNetwork":"cc567", "destinationDevice":"z02", "path":"/user/file2"}

And there can be more files. Also all the device are in kibana as strings.

Now what I am trying to do is create a bar graph using Vega where on the X axis it shows the path and the y axis is a bit more complicated.
I want it to have the unique count of sourceDevice and destinationDevice together. So for example:
-for file 1 the sourceDevices are z01, z02 and the destination devices are z02, z06, z06, z04. So the bar for path 1 should be at 4 high because z02 is in both list and z06 is in there twice so they are counted once.
-for file 2 which would be the next bar in the graph it would only be 2 because there are only 1 sourceDevice and 1 destinationDevice that are unique.

I also wanted the data to be split in the bar graph so that you can hover over it and see that z01, z02, z06, z04 are in the one bar and then in the next bar only z01, z02 are in it.

I was trying to do this and vega and it would not even get it to get the correct data from kibana.

I was referencing this: How to make histogram by grouped aggregation count?
I could get data counts to appear, but could not get it to sort by paths on the x axis or even add destination data to the source data. I can do this without vega with just sourcData, but I would like to do it with the sourceDevices and destinationDevices combined.

[aaron-nimocks]

Think I am tracking what you are looking for, at least the data portion.

Check out the example I made. This just does the first few parts I think you're stuck on. Still need to finish it off. Most likely there is still a better way to transform the data but this "works".

The transformations create a data table that looks like the below.

[Chris6 (Chris)]

Hi thanks.
I tried that and I typed it all out it all into kibana. I tried to graph the data in the bar chary and shows nothing. I don't know if I am getting the data from elastic correctly or if my bar graph is wrong. It should be a simple bar graph putting x and y into it so I think I am doing that correctly. Anyway I put what i did for the bar graph below. Thanks in advance.

"mark": "bar",
  "encoding": {
    "x": {"field": "x", "type": "ordinal"},
    "y": {"field": "y", "type": "quantitative"}
  }

[aaron-nimocks]

You can check if you are getting data by looking at the request and response if you are on a new enough Kibana version. I'd start there and verify that your data is being populated.

If you do have data then sharing the spec will help us troubleshoot. Copy/Paste that here. Use a Gist or something similar if it's too large.

[Chris6 (Chris)]

It looks like my version of Kibana is not new enough for that. The inspect button is greyed out.

[aaron-nimocks]

Then you would need to use the browser console to get the data.

Use JSON.stringify(VEGA_DEBUG.vega_spec) for Vega.
Use JSON.stringify(VEGA_DEBUG. vegalite_spec) for Vega-Lite.

[Chris6 (Chris)]

I got the debug to work and it looks like it is getting data, but it seems that the there is only data for key and doc_count, so it looks like I am getting the terms incorrectly. What i am doring is:

aggs: {
   users: {
        terms:[
            fields: "sourceDevices.keyword"
            size: 10000
       ]
       terms:[
            fields: "destinationDevices.keyword"
            size: 10000
       ]
       terms:[
            fields: "path.keyword"
            size: 10000
       ]
   }
}

I believe that is what you want to do to get the dat from elasticsearch. It also says it gets 5 from my cluster so it is getting all the data.

[aaron-nimocks]

I would get the query working in Dev Tools first then it's easy to move over to Vega.

Keep in mind in Vega you can also pass in Filters and Time Filters so those could change what you are seeing in Dev Tools vs Vega if you are using them.

[Chris6 (Chris)]

It looks like I got the data to work, but I am using vega-lite v2 , so the transform is not working. I was testing it on github editor and cannot get the data to format properly. I copied it here.

I was using Vega Editor because it runs version 2 and get nothing.

[aaron-nimocks]

I changed some of it over.

The transformations I did were for Vega and the Bar Chart you added was Vega-Lite. You would need to use Vega Bar Chart or see if Vega-Lite could do the same transformations (I don't think it can).

[Chris6 (Chris)]

Thanks. I got the chart to work in Kibana with manualy entered data. I will play around with the console until I can get the data from elastic search correctly to be able to add it to a dashboard. For other people reference this is what my code looks liek for manually entered data: Vega Editor

[Chris6 (Chris)]

@aaron-nimocks I have a quick question. I got data to come in from elasticsearch but it has a keys value assosiated with it.
{"key":{
"sourceNetwork": "cc567",
"sourceDevice": "z01",
"destinationNetwork": "cc567",
"destinationDevice": "z02",
"path": "/user/file2"
}
}
I think now the transforn is messed up in that I need to use key.sourceDevices. It seems not be sorting by path correctly anymore This is what it looks like: Vega Editor

[aaron-nimocks]

Does this work?

      "transform": [
        {
          "type": "fold",
          "fields": ["key.path", "key.sourceDevice", "key.destinationDevice"]
        },
        {"type": "filter", "expr": "datum.key != 'key.path'"},
        {"type": "aggregate", "groupby": ["key", "value"]},
        {"type": "aggregate", "groupby": ["key"]},
        {"type": "project", "fields": ["key", "count"], "as": ["x", "y"]}
      ]
    }

[Chris6 (Chris)]

It doesn't put path on the x-axis, instead it puts key.sourceDevice, key.destinationDevice.

[aaron-nimocks]

Easiest solution is probably just to transform using project and remove the key first and then left as it was.