topic Re: How to join two table with aggrouped date value? in Splunk Search

How to join two table with aggrouped date value?

arriel96 — Tue, 31 Jan 2023 14:27:01 GMT

A have two tables anda i want to relation this two tables by nember of events in a hour, i manage to make a SQL query, but struggle to do in splank. I send the data of this 2 tables for two diferent indexes (simple copy) and want to make this:

WITH count_reserved as ( SELECT count (ru.id) reserved, to_char(ru.date,'yyyy-mm-dd hh24') as time FROM reserved ru GROUP BY to_char(ru.date,'yyyy-mm-dd hh24') ), count_concluid as ( SELECT count (u.id) as concluid, to_char(u.date,'yyyy-mm-dd hh24') as time FROM concluid u GROUP BY to_char(u.date,'yyyy-mm-dd hh24') ) SELECT coalesce(concluid,0) as concluid, reserved, count_reserved.time, ((coalesce(concluid::decimal,0)/reserved)*100) as percentage FROM count_reserved LEFT JOIN count_concluid ON count_concluid.time=count_reserved.time ORDER BY 3 ASC

the information that a want to return is the percentage value and the time to make a graph hour bar

Re: How to join two table with aggrouped date value?

yuanliu — Wed, 01 Feb 2023 05:53:32 GMT

Have you consulted Splunk SPL for SQL users? What is the sample code attempted? If you want to get concrete help, you will also need to illustrate the data in your index, illustrate the result you want (and not make people reverse engineer from some SQL code), and illustrate the result from your sample code, and compare to your desired result.

Re: How to join two table with aggrouped date value?

arriel96 — Wed, 01 Feb 2023 20:29:56 GMT

Sorry for the SQL, i have my data in Relational database and for B.I. query i want to use splunk so i'm importing all the data from tables in the relational database from splunk (1 table= become 1 index). So i'm basic strugle the construct more complex query's that use multiple features, in that case i managed to get the answer:

index=reserved_units_new_stock | eval date=strftime(_time, "%y-%m-%d %H") | STATS COUNT AS count_reserved_units BY date | JOIN type=left date [SEARCH index=units_new_stock | eval date=strftime(_time, "%y-%m-%d %H") | STATS COUNT AS count_units1 BY date] |eval count_units=coalesce(count_units1,0) |eval porcentagem=round((count_units/count_reserved_units)*100,2) | FIELDS count_units,count_reserved_units, date, porcentagem

Re: How to join two table with aggrouped date value?

yuanliu — Thu, 02 Feb 2023 09:41:38 GMT

Does this mean the question is answered? In general, using join should be the last resort. But if it works for you, you do not have to change, not while you are learning to use SPL.

Re: How to join two table with aggrouped date value?

arriel96 — Thu, 02 Feb 2023 13:07:18 GMT

Yes it's resolve , yanks for the attention