FirstWave Forum - Recent Topics https://community.firstwave.com/community/ FirstWave Discussion Board en-US Fri, 24 Apr 2026 03:54:15 +0000 wpForo 60 How to create custom SQL queries in Open-Audit https://community.firstwave.com/community/open-audit/how-to-create-custom-sql-queries-in-open-audit/ Tue, 09 Dec 2025 23:16:50 +0000 Hello, 
I have tried creating a custom SQL query in Open-Audit and running the SQL query but it fails to return the data I am looking for.
I am following standard SQL query command structure, but I think I am doing something wrong. There isn't enough information in the WIKI page around creating a custom SQL query and what syntax is required.
Let me give an example.
Lets say that you want to reach into the Software database table and Devices table and get the following information:
Software Name, Software Version number, Operating System Name, Operating System Version Number.
Lets say that you are only running this query against RedHat Linux systems.
How would you write a SQL query to return this information?
I know that from the Web UI you can go to manage > queries > create query.
I have written several custom queries, but none of them work. They time out and don't return data, or OpenAudit throws an error bar and I don't get the information returned.

Here is the first query I wrote as a test to pull software data:
SELECT devices.id AS 'devices.id', software.name AS 'software.name', software.version AS 'software.version' FROM devices LEFT JOIN software on (software.device_id = devices.id AND software.current = 'y') WHER @filter

This query fails.

Another query I wrote:

SELECT devices.id AS 'devices.id', system.name AS 'device_name', system.os_name AS 'os_name', system.os_version AS 'os_version', software.name AS 'software.name', software.version AS 'software.version' FROM devices LEFT JOIN software ON 9software.device_id = devices.id AND software.current = 'y') LEFT JOIN system ON system.device_id = devices.id

Both of these query statements failed to return data in the web UI.


Not exactly sure what is wrong with this SQL query statement, if anyone could post what the correct SQL query should look like, I would really appreciate it!

]]> Nathan_Smith_81 https://community.firstwave.com/community/open-audit/how-to-create-custom-sql-queries-in-open-audit/ Test Q https://community.firstwave.com/community/open-audit/test-q/ Mon, 26 Aug 2024 23:51:26 +0000 How does OA working.

]]> Luke https://community.firstwave.com/community/open-audit/test-q/