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.
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:
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?
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.