Question about a query

 
1
0
-1

Hello,

I'm having a bit of trouble creating a particular query. I've been asked to create a query that would allow me to retrieve all the windows stations that don't have antivirus, can I use minus with SQL or is there a simpler solution? 

Do you know of a community that shares queries for open audit?

Thanks again! (smile) 

    CommentAdd your comment...

    1 answer

    1.  
      1
      0
      -1

      A couple of pages worth reading.

      Creating a Query

      How Do Reports, Queries and Summaries Work?

      You might look at modifying the existing query for Antivirus if it doesn't suit.

      You can run it from menu → Report → Software → Antivirus.

      Or view the SQL at menu → Manage → Queries → List Queries and then Antivirus

      The SQL is below.

      This should show all Windows machines regardless of them having AV installed or not. You might need to add the name of your particular AV into this.

      SELECT devices.id AS `devices.id`, devices.icon AS `devices.icon`, devices.type AS `devices.type`, devices.name AS `devices.name`, devices.domain AS `devices.domain`, devices.ip AS `devices.ip`, devices.class AS `devices.class`, devices.os_family AS `devices.os_family`, orgs.name AS `orgs.name`, windows.user_name AS `windows.user_name`, 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' AND (software.name LIKE '%virus%' or software.name LIKE '%trend micro%' or software.name LIKE '%endpoint%')) LEFT JOIN orgs ON (orgs.id = devices.org_id) LEFT JOIN windows ON (windows.device_id = devices.id AND windows.current = 'y') WHERE @filter AND devices.type = 'computer'
        CommentAdd your comment...