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

[gangwarj]

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.

[micahkemp]

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:

[micahkemp]

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?

[gangwarj]

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.