#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.

Powered by Struct AI

1

1

14
7mo
Solved
Join the chat
May 14, 2023 (7 months ago)
h121h
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.
Prabhat
Photo of md5-23052f31f8f3c4b1bb3297fbc3a2aec5
Prabhat
12:09 AM
Its okay being here or in #zincobserve. Trying to formulate query for you

1

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
Photo of md5-ac5d64b63e48fd1a3cf936c3e2221a2c
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.

1

12:15
h121h
12:15 AM
Is it possible to sort, ascending or descending by the count results?
Prabhat
Photo of md5-23052f31f8f3c4b1bb3297fbc3a2aec5
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
Photo of md5-ac5d64b63e48fd1a3cf936c3e2221a2c
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