DQL makeTimeseries by multiple filters and dimensions

[zip-chanko]

Hello,

Is there a way to use the makeTimeseries function which splits the graph by multiple filters.

For example, I have logs with attribute httpstatus which shows 2xx, 4xx, 5xx, etc.

I would like to see the graph splits into three dimensions. (total, 200 and non-200).

[MichalOlszewski]

Hi @zip-chanko 

It all depends on the format of the data and what it looks like. Can you provide some screenshot of your data?

Generally, sure, you can add multiple variables to split by “by:{}”. In that way you can add more dimensions to your timeseries.

If you want to make multiple timeseries in one chart you always can use “append” statement after makeTimeseries. Like in your example: if you have numbers of 2xx, 4xx, 5xx in separated columns you can create chart by appending three makeTimeseries. And if you want sum of all these the solution is the same, you can create new column with sum of these values and by append add new timeseries to your query.

Bests

Michal

[zip-chanko]

Hi @MichalOlszewski , thank you for your reply. Please refer the below example data which I am trying to achieve.

data record(host = "example.com", http_status = "200"),
record(host = "example.com", http_status = "200"),
record(host = "example.com", http_status = "200"),
record(host = "example.com", http_status = "301"),
record(host = "example.com", http_status = "403"),
record(host = "example.com", http_status = "403"),
record(host = "example.com", http_status = "502"),
record(host = "example.com", http_status = "502"),
record(host = "example.com", http_status = "200"),
record(host = "example.com", http_status = "200"),
record(host = "example.com", http_status = "200")
| fieldsAdd httpstatus = toLong(http_status)
| fieldsRemove http_status
| makeTimeseries total = count()
| makeTimeseries 2xx = count(), by:{httpstatus == 200 and httpstatus == 201}
| makeTimeseries 4xx = count(), by:{httpstatus >= 400 and httpstatus < 500}
| makeTimeseries 5xx = count(), by:{httpstatus > 500}

[Eric_Yu · Accepted solution]

Hi @zip-chanko,
Your example data is missing timestamps for a timeseries, so I added it. The result should look something like this:

data record(host = "example.com", http_status = "200", timestamp="2024-05-07T12:00:00.0-05:00" ),
record(host = "example.com", http_status = "200", timestamp="2024-05-07T12:01:00.0-05:00"),
record(host = "example.com", http_status = "200", timestamp="2024-05-07T12:02:00.0-05:00"),
record(host = "example.com", http_status = "301", timestamp="2024-05-07T12:03:39.0-05:00"),
record(host = "example.com", http_status = "403", timestamp="2024-05-07T12:04:40.0-05:00"),
record(host = "example.com", http_status = "403", timestamp="2024-05-07T12:05:41.0-05:00"),
record(host = "example.com", http_status = "502", timestamp="2024-05-07T12:06:42.0-05:00"),
record(host = "example.com", http_status = "200", timestamp="2024-05-07T12:01:00.0-05:00"),
record(host = "example.com", http_status = "200", timestamp="2024-05-07T12:02:00.0-05:00"),
record(host = "example.com", http_status = "301", timestamp="2024-05-07T12:03:39.0-05:00"),
record(host = "example.com", http_status = "403", timestamp="2024-05-07T12:04:40.0-05:00"),
record(host = "example.com", http_status = "200", timestamp="2024-05-07T12:02:00.0-05:00"),
record(host = "example.com", http_status = "301", timestamp="2024-05-07T12:03:39.0-05:00"),
record(host = "example.com", http_status = "403", timestamp="2024-05-07T12:04:40.0-05:00"),
record(host = "example.com", http_status = "403", timestamp="2024-05-07T12:05:41.0-05:00"),
record(host = "example.com", http_status = "502", timestamp="2024-05-07T12:06:42.0-05:00"),
record(host = "example.com", http_status = "502", timestamp="2024-05-07T12:07:43.0-05:00"),
record(host = "example.com", http_status = "200", timestamp="2024-05-07T12:08:44.0-05:00"),
record(host = "example.com", http_status = "200", timestamp="2024-05-07T12:09:45.0-05:00"),
record(host = "example.com", http_status = "200", timestamp="2024-05-07T12:10:46.0-05:00")
| fieldsAdd httpstatus = toLong(http_status)
| fieldsRemove http_status
| makeTimeseries {
      total=count(),
      http_2xx = countIf(httpstatus == 200 and httpstatus == 201),
      http_4xx = countIf(httpstatus >= 400 and httpstatus < 500),
      http_5xx = countIf(httpstatus > 500)
    },
    by: {}