Splunk Search

How to join two table with aggrouped date value?

arriel96
Explorer

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

Labels (3)
0 Karma
1 Solution

arriel96
Explorer

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

View solution in original post

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

arriel96
Explorer

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
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

arriel96
Explorer

Yes it's resolve , yanks for the attention

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...