All Apps and Add-ons

How to generate events from a search sql query in Splunk DB Connect?

gangwarj
New Member

I am running a sql query which use JOIN 4 tables and returns the results, the query is working as expected in SQL Explorer but when i try to run the same in search it does not generate any events, values get populated n statistics tab though.

 SELECT  * 
 FROM table1 
 INNER JOIN table2 
 ON table1.DEVICE_ID = table2.ID 
 LEFT JOIN table3 
 ON table2.CLIENT_ID = table3.ID 
 LEFT JOIN table4  
 ON table3.CLIENTTYPE_ID = table4.ID 
 WHERE table4.NAME = 'ABC'  OR table4.NAME = 'DEF'

I want to use some aggregation function (stats, eval, count) over the rows returned from my SQL query.

0 Karma

micahkemp
Champion

Specify your selected column names explicitly, like this:

| dbxquery connection="<connect>" query="SELECT table1.ID AS table1_ID, table4.NAME AS table4_NAME 
      FROM table1 
      INNER JOIN table2 
      ON table1.DEVICE_ID = table2.ID 
      LEFT JOIN table3 
      ON table2.CLIENT_ID = table3.ID 
      LEFT JOIN table4  
      ON table3.CLIENTTYPE_ID = table4.ID 
      WHERE table4.NAME = 'ABC'  OR table4.NAME = 'DEF'"

I just tried this and it resulted in this output:
alt text

0 Karma

micahkemp
Champion

Assuming you are running the query at search time (versus configuraing an input that periodically runs the SQL and indexes the resutls) I wouldn't expect running a SQL query to return events, I would only expect it to return statistics. The statistics fully represent the results of your query, and you can run aggregations on the statistics just as you can on events.

Is there a reason you also want to see something on the events tab?

0 Karma

gangwarj
New Member

In statistics tab the columns names are changed, How do I refer to a specific column or some table. The statistics are the result of joins on 4 different tables.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...