We have multiple criteria to identify a device on our network, not IP address alone. However, we frequently have systems which are already in the database get rediscovered by nmap, where SSH and SNMP don't work because of password or software changes. These end up multiple times in the System table as new devices.
I'm trying to craft a query which finds all entries in the table which were only found once by nmap, and where the IP address was seen before. To do this, I need to be able to do a self-join on the system table; and this is only possible using table aliases in the SQL.
Here's one version; I've also tried using the INNER JOIN clause:
SELECT s.id, s.type, s.name, s.domain, s.ip, s.last_seen FROM system s, system tWHERE @filter ANDs.ip = t.ip ANDs.id != t.id ANDs.first_seen = s.last_seen ANDs.last_seen_by = 'nmap' ANDs.first_seen > t.first_seenORDER BY s.ip, s.last_seen
The OpenAudIT web page gives me an error when I try to run it, similar to the one for an SQL syntax error. In fact, even this query fails:
SELECT sys1.id, sys1.type, sys1.name, sys1.domain, sys1.ip, sys1.last_seen FROM system sys1WHERE @filter ANDsys1.first_seen = sys1.last_seen ANDsys1.last_seen_by = 'nmap'ORDER BY sys1.ip, sys1.last_seen
Does OpenAudIT not allow table aliases in the SQL? Is there some other way to join the table to itself? I could accomplish the same thing in two steps using a temporary table, but for various reasons would prefer not to.
Here is the query which ended up working. Only the second instance of system uses a table alias; not sure why that should matter, but it runs as expected. Thanks all for your help!
SELECT DISTINCT system.id AS `system.id`, system.type AS `system.type`, system.ip AS `system.ip`, system.last_seen AS `system.last_seen` FROM system, system AS sys1WHERE @filter AND system.id != sys1.id AND system.ip = sys1.ip AND system.first_seen > sys1.first_seen AND system.first_seen = system.last_seen AND system.last_seen_by = 'nmap' AND system.type = 'unknown'ORDER BY system.ip, system.last_seen
Thanks for your suggesting - it helped, in that I'm no longer getting an error when I hit the "Play" button. The query runs; but it returns no records. I know from working with the exported data that it should return hundreds of records. I also tried
... FROM system AS s, system AS t ...
but still no records. I'll try rephrasing the query and report back how it goes.
Try using fully qualified names as output. IE - for your first query, rewrite is as:
SELECT s.id AS `system.id`, s.type AS `system.type`, s.name AS `system.name`, s.domain AS `system.domain`, s.ip AS `system.ip`, s.last_seen AS `system.last_seen`
FROM system s, system t
WHERE @filter AND
s.ip = t.ip AND
s.id != t.id AND
s.first_seen = s.last_seen AND
s.last_seen_by = 'nmap' AND
s.first_seen > t.first_seen
ORDER BY s.ip, s.last_seen
Powered by a free Atlassian Confluence Open Source Project License granted to Opmantek. Evaluate Confluence today.