Displaying time series in Canvas

[gabor (Gábor)]

Hi,

I have a data set based on some server logs containing sent- and received amount of data and client IPs. The dataset is a collection of these documents:
{ .. server_ip: IP1, client_ip: IP2, read: 2232323234, write: 232322, ...}

Hopefully it's ok to ask 2 questions:

1. I'd like to create a Canvas text element displaying the number of distinct client_ips. I tried something like "SELECT DISTINCT client_ip from log" but apparently no "DISTINCT" support yet. (I'm running Kibana 7.12)

2. both the read and written fields' value is monotonically increasing, ie.: the total amount of data read/written up to that point. Is it possible to create another text element that counts up the used bandwith, ie.: for each client_IP it finds the lowest and highest value of the read/write fields and divides by the time period.

Appreciate any insights.

Thanks,
Gábor

[corey.robertson]

Hi @gabor

For question 1)
You should be able to do the unique count via the expression, instead of essql.

Something like this

filters
| essql query="SELECT  client_ip from log"
| ply by="client_ip" expression={head 1}
| rowCount 

For #2, I'm not sure I completely understand. I think I understand you want the highread - lowread for the total, but where does the time period come from?

[gabor (Gábor)]

Hi @corey.robertson,

Thans for the suggestion on q1; I do get this problem:

Whoops! Expression failed

Expression failed with the message:

[essql] > Unexpected error from Elasticsearch: [verification_exception] Found 1 problem line 1:8: SELECT DISTINCT is not yet supported

As I mentioned, I'm running Kibana 7.12

With regards to Q2, I'm happy to elaborate: so, I have these docs in my index, each representing a measurement. They are taken in ca. every 30s interval. I'm looking for the amount of data exchanged between server_ip and client_ip, measured by MBytes/sec in the arbitrarily selected "time period". Suppose doc1 and doc2 are the nearest to the start and end of this "time period". Both of these will have a read/write value. I'd like to visualize the average: (doc2.read - doc1.read) / (t2 - t1)

Does that make sense?

Thanks.

[corey.robertson]

Apologies for that error. You need to take distinct out of the query. I've edited my original response to correct it.