#general

Querying and Grouping with ZincObserve and SQL

TLDR h121h needed help with a ZincObserve log query. Prabhat provided a SQL query and sorting options to achieve the desired results.

7mo

Join the chat

May 14, 2023 (7 months ago)

Photo of md5-ac5d64b63e48fd1a3cf936c3e2221a2c

h121h

12:05 AM

Does someone have a moment to give me some help? Using ZincObserve, using the following log (in a reply) - I want to query all logs having “edgecolocode” equals “SJC” then counting or grouping by the value of “ja3hash”.

12:06

h121h

12:06 AM

{
“_timestamp”: 1683926881065422,
“edgecfconnectingo2o”: false,
“edgecolocode”: “SJC”,
“edgecoloid”: 465,
“edgeendtimestamp”: “2023-05-12T21:20:36Z”,
“edgepathingop”: “wl”,
“edgepathingsrc”: “macro”,
“edgepathingstatus”: “nr”,
“edgeratelimitaction”: “”,
“edgeratelimitid”: 0,
“edgeresponsebodybytes”: 1248,
“edgeresponsebytes”: 1887,
“edgeresponsecompressionratio”: 9.23,
“edgeresponsecontenttype”: “application/json”,
“edgeresponsestatus”: 200,
“ja3hash”: “773906b0efdefa24a7f2b8eb6985bf37",
“origindnsresponsetimems”: 0,
“originrequestheadersenddurationms”: 0,
“originresponsebytes”: 0,
“originresponsedurationms”: 179,
“originresponseheaderreceivedurationms”: 167,
“originresponsehttpexpires”: “”,
“originresponsehttplastmodified”: “”,
“originresponsetime”: 168000000,
“origintcphandshakedurationms”: 0,
“origintlshandshakedurationms”: 0,
“parentrayid”: “00",
“smartroutecoloid”: 12,
“uppertiercoloid”: 0,
}

12:07

h121h

12:07 AM

My SQL sucks so I’m not having any luck with this.

12:08

h121h

12:08 AM

Also, I can move this to the #zincobserve channel if it would be better there.

Photo of md5-23052f31f8f3c4b1bb3297fbc3a2aec5

Prabhat

12:09 AM

Its okay being here or in #zincobserve. Trying to formulate query for you

12:12

Prabhat

12:12 AM

You can't really do this in standard SQL in a single query unless you start using subqueries. We will not go there right now. However let's look at this:

12:12

Prabhat

12:12 AM

select ja3hash, count(*) from logstream where edgecolocode = 'SJC' group by ja3hash

12:12

Prabhat

12:12 AM

this should give you basic results

12:13

Prabhat

12:13 AM

Does this help?

h121h

12:14 AM

Absolutely it helps and gets what I need.

12:14

h121h

12:14 AM

Super responsive as always Prabhat. Much love my friend.

12:15

h121h

12:15 AM

Is it possible to sort, ascending or descending by the count results?

Prabhat

12:18 AM

give this a shot

select ja3hash, count(*) as counter from logstream where edgecolocode = 'SJC' group by ja3hash order by counter desc

h121h

12:19 AM

Phenomenal. Thank you. 🙏

OpenObserve

OpenObserve is an open-source, petabyte-scale observability platform for the cloud native realm, offering a 10x cost reduction and 140x less storage use compared to competitors like Elasticsearch or Splunk. Built in Rust for exceptional performance, it offers comprehensive features like logs, metrics, traces, dashboards, and more | Knowledge Base powered by Struct.AI

Indexed 406 threads (74% resolved)

Join Our Community

Similar Threads

Discussing Cast/Schema Extension in 0.4.8 Update.

Mark inquired about a casting issue from 0.4.8 version, Prabhat suggested a workaround involving an environment variable, yet this didn't resolve the issue completely. Ashish recommended testing with a different dev image.

5mo

Troubleshooting Zincobserve Upload Error

Simon encountered a 500 error when uploading logs to Zincobserve. Hengfei suggested using `--data-binary "@demo.log"` instead of `-d`, which resolved the issue.

6mo

Issues with openobserve(v0.5.0) API Response Code

Shashank is facing issues with an API call getting a bad response code. Hengfei indicated the query may be incorrect and suggested an upgrade. Prabhat showed typical stream page queries. The issue is unresolved.

3mo

Erroneous Triggering of Alarm in 0.5.1

Chris expressed experiencing occasional erroneous alerts trigger on a specific stream query. Ashish suggested the issue might be linked to a known duplicates bug. Uncertain, Chris decided to monitor the issue further and report if it persists.

3mo

ZO Kubernetes Issues: Query Log Error and Adding Users

Sa had issues with querying logs in ZO on Kubernetes and creating users. Hengfei provided solutions for both problems, including updating with a dev version and setting memory cache values.

100

6mo