QUERY doesn't allow table aliases

 
1
0
-1

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 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

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 sys1
WHERE @filter AND
sys1.first_seen = sys1.last_seen AND
sys1.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.

Thanks,

Jim

 

    CommentAdd your comment...

    3 answers

    1.  
      1
      0
      -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_seen
        CommentAdd your comment...
      1.  
        1
        0
        -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

          CommentAdd your comment...
        1.  
          1
          0
          -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
            CommentAdd your comment...