3 answers
- 10-1
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!
Jim
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 sys1
WHERE @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_seenAdd your comment... - 10-1
Hi Mark,
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.
Jim
Add your comment... - 10-1
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
Add your comment...
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:
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:
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.
Thanks,
Jim