none
SQL Query Help

    Question

  • I'm working with a Sophos 5.21 database - attempting to build a report that identifies active viruses that have had no action taken. I have 2 tables that I am pulling data from:  [Sophos Reporting Interface].vThreatInstances.FullFilePath and  [Sophos Reporting Interface].vThreatEventData.ActionTakenName.

    In this case Sophos is writing multiple lines to the DB. I'll see the same file recorded 3 times in FullFilePath and each line has a different ActionTakenName - None, Blocked, and Cleaned up. I'm simply trying to find items that have only ActionTakenName = None


    I need some way to compare FulleFilePath to ActionTakenName. Below is the query I've built thus far, along with a screenshot example of what I'm dealing with.

    SELECT     TOP (100) PERCENT dbo.Computers.Name, dbo.Computers.IPAddressText, dbo.Computers.Managed, dbo.Computers.Connected, dbo.Computers.LastMessageTime, 
                          [Sophos Reporting Interface].vGroupPathAndNameData.PathAndName, [Sophos Reporting Interface].vThreatEventData.ActionTakenName, 
                          [Sophos Reporting Interface].vThreatInstances.FullFilePath
    FROM         dbo.Computers INNER JOIN
                          dbo.ComputerGroupMapping ON dbo.Computers.ID = dbo.ComputerGroupMapping.ID INNER JOIN
                          [Sophos Reporting Interface].vGroupPathAndNameData ON dbo.ComputerGroupMapping.GroupID = [Sophos Reporting Interface].vGroupPathAndNameData.GroupID INNER JOIN
                          [Sophos Reporting Interface].vThreatInstances ON dbo.Computers.ID = [Sophos Reporting Interface].vThreatInstances.ComputerID INNER JOIN
                          [Sophos Reporting Interface].vThreatEventData ON [Sophos Reporting Interface].vThreatInstances.ThreatID = [Sophos Reporting Interface].vThreatEventData.ThreatID
    WHERE     (dbo.Computers.LastMessageTime > DATEADD(dd, - 30, GETDATE())) AND ([Sophos Reporting Interface].vGroupPathAndNameData.PathAndName LIKE N'%Engineering Lab%') AND 
                          (dbo.Computers.Name = N'WIN-SERVER')

    Thursday, June 19, 2014 5:43 PM

Answers

  • Execution through the query window worked, but unfortunately I still don't get any results.

    I forgot the FullFilePath column.

    What is the difference between PathName and FullFilePath columns?

    The ID column in the dbo.Computer table identifies each computer uniquely?

    Try

    -- code #2 v9
    IF Object_ID('tempdb..#TabEvents') is not null DROP TABLE #TabEvents;
    
    SELECT C.ID, C.Name, C.IPAddressText, C.Managed, C.Connected, C.LastMessageTime, 
           GP.PathAndName, 
           E.ActionTakenName, 
           I.FullFilePath
      INTO #TabEvents
      FROM dbo.Computers as C  INNER JOIN
           dbo.ComputerGroupMapping as CGM ON C.ID = CGM.ID  INNER JOIN
           [Sophos Reporting Interface].vGroupPathAndNameData as GP ON CGM.GroupID = GP.GroupID  INNER JOIN
           [Sophos Reporting Interface].vThreatInstances as I ON C.ID = I.ComputerID  INNER JOIN
           [Sophos Reporting Interface].vThreatEventData as E ON I.ThreatID = E.ThreatID
      WHERE (C.LastMessageTime > DATEADD(dd, -30, GETDATE())) 
            --AND (GP.PathAndName LIKE N'%Engineering Lab%') 
            --AND (C.Name = N'WIN-SERVER')
    
    --
    CREATE clustered INDEX I1 on #TabEvents(ID);  -- optional, case #TabEvents has few rows
    
    --
    SELECT Name, IPAddressText, Managed, Connected, LastMessageTime, 
           PathAndName, ActionTakenName, FullFilePath
      from #TabEvents as T1
      where ActionTakenName = 'None'
            and not exists (SELECT * from #TabEvents as T2
                              where T2.ID = T1.ID
                                    and T2.FullFilePath = T1.FullFilePath
                                    and T2.ActionTakenName <> 'None');
    
    --DROP TABLE #TabEvents;

     
    If the execution of the code above does not show any results, please run the following code:

    SELECT top(5) * from #TabEvents order by ID, FullFilePath;
    SELECT Count(*) as Qty from #TabEvents where ActionTakenName = 'None';
    SELECT top (10) T.* from #TabEvents as T inner join (SELECT T2.ID, T2.FullFilePath from #TabEvents as T2 where T2.ActionTakenName = 'None') as F3 on T.ID = F3.ID and T.FullFilePath = F3.FullFilePath
    order by T.ID, T.FullFilePath;


        José Diz     Belo Horizonte, MG - Brasil


    Friday, June 20, 2014 5:22 AM

All replies

  • Moved to: http://social.technet.microsoft.com/Forums/en-US/1885a6ae-a001-4f82-aa74-55a8faaa9ccf/sql-query-help?forum=transactsql

    Thursday, June 19, 2014 5:39 PM
  • Please move this question to the TSQL forum.

    Thursday, June 19, 2014 5:41 PM
  • I'm simply trying to find items that have only ActionTakenName = None

    Try

    -- code #1 v2
    SELECT C.Name, C.IPAddressText, C.Managed, C.Connected, C.LastMessageTime, 
           GP.PathAndName, 
           max(E.ActionTakenName), 
           I.FullFilePath
      FROM dbo.Computers as C  INNER JOIN
           dbo.ComputerGroupMapping as CGM ON C.ID = CGM.ID  INNER JOIN
           [Sophos Reporting Interface].vGroupPathAndNameData as GP ON CGM.GroupID = GP.GroupID  INNER JOIN
           [Sophos Reporting Interface].vThreatInstances as I ON C.ID = I.ComputerID  INNER JOIN
           [Sophos Reporting Interface].vThreatEventData as E ON I.ThreatID = E.ThreatID
      WHERE (C.LastMessageTime > DATEADD(dd, - 30, GETDATE())) 
            AND (GP.PathAndName LIKE N'%Engineering Lab%') 
            AND (C.Name = N'WIN-SERVER')
      GROUP BY C.Name, C.IPAddressText, C.Managed, C.Connected, C.LastMessageTime, GP.PathAndName, I.FullFilePath
      HAVING Count(*) = 1
             AND (max(E.ActionTakenName) = 'None');


        José Diz     Belo Horizonte, MG - Brasil


    • Edited by Jose.Diz Thursday, June 19, 2014 6:45 PM
    Thursday, June 19, 2014 6:44 PM
  • Try

    SELECT C.Name, C.IPAddressText, C.Managed, C.Connected, C.LastMessageTime, 
           GP.PathAndName, 
           max(E.ActionTakenName), 
           I.FullFilePath
      FROM dbo.Computers as C  INNER JOIN
           dbo.ComputerGroupMapping as CGM ON C.ID = CGM.ID  INNER JOIN
           [Sophos Reporting Interface].vGroupPathAndNameData as GP ON CGM.GroupID = GP.GroupID  INNER JOIN
           [Sophos Reporting Interface].vThreatInstances as I ON C.ID = I.ComputerID  INNER JOIN
           [Sophos Reporting Interface].vThreatEventData as E ON I.ThreatID = E.ThreatID
      WHERE (C.LastMessageTime > DATEADD(dd, - 30, GETDATE())) 
            AND (GP.PathAndName LIKE N'%Engineering Lab%') 
            AND (C.Name = N'WIN-SERVER')
      GROUP BY C.Name, C.IPAddressText, C.Managed, C.Connected, C.LastMessageTime, GP.PathAndName, I.FullFilePath
      HAVING min(E.ActionTakenName) = 'None'
             AND max(E.ActionTakenName) = 'None';


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, June 19, 2014 6:48 PM
  • I'm simply trying to find items that have only ActionTakenName = None

    Has each computer a unique name?

     

    -- code #2 v4
    ;with TabEvents as(
    SELECT C.Name, C.IPAddressText, C.Managed, C.Connected, C.LastMessageTime, 
           GP.PathAndName, 
           E.ActionTakenName, 
           I.FullFilePath
      FROM dbo.Computers as C  INNER JOIN
           dbo.ComputerGroupMapping as CGM ON C.ID = CGM.ID  INNER JOIN
           [Sophos Reporting Interface].vGroupPathAndNameData as GP ON CGM.GroupID = GP.GroupID  INNER JOIN
           [Sophos Reporting Interface].vThreatInstances as I ON C.ID = I.ComputerID  INNER JOIN
           [Sophos Reporting Interface].vThreatEventData as E ON I.ThreatID = E.ThreatID
      WHERE (C.LastMessageTime > DATEADD(dd, -30, GETDATE())) 
            --AND (GP.PathAndName LIKE N'%Engineering Lab%') 
            --AND (C.Name = N'WIN-SERVER')
    )
    SELECT Name, IPAddressText, Managed, Connected, LastMessageTime, 
           PathAndName, ActionTakenName, FullFilePath
      from TabEvents as T1
      where ActionTakenName = 'None'
            and not exists (SELECT * from TabEvents as T2
                              where T2.Name = T1.Name and T2.ActionTakenName <> 'None');

     
    PS: The above code can be optimized, by converting the TabEvents CTE on a temporary table indexed by Name column.


        José Diz     Belo Horizonte, MG - Brasil



    • Edited by Jose.Diz Thursday, June 19, 2014 9:06 PM
    Thursday, June 19, 2014 7:07 PM
  • Thank you both. The DB is hosted on SQL Server 2008 R2.

    Unfortunately all 3 queries offered blank results. It should spit back 12 records with 12 unique file paths.(confirmed manually). 

    Thursday, June 19, 2014 8:50 PM
  • Has each computer a unique name?

    Please test code #2  above, which has been rewritten.


        José Diz     Belo Horizonte, MG - Brasil


    • Edited by Jose.Diz Thursday, June 19, 2014 9:04 PM
    Thursday, June 19, 2014 9:03 PM
  • Yes - each computer has a unique name listed under "dbo.Computers.Name".

    I copied the #2 query and it returned "unable to parse query text".

    Thursday, June 19, 2014 9:06 PM
  • Please transcribe the complete error message.

    DATEADD(dd, - 30, GETDATE())

    DATEADD(dd, -30, GETDATE())

    ????


        José Diz     Belo Horizonte, MG - Brasil


    • Edited by Jose.Diz Thursday, June 19, 2014 9:20 PM
    Thursday, June 19, 2014 9:15 PM
  • Probably you want to separate data into subquery, e.g.

    SELECT GP.GroupID, I.ComputerID,   
    GP.FileFullPath, MAX(I.ActionTaken) as ActionTaken
    [Sophos Reporting Interface].vGroupPathAndNameData as GP  INNER JOIN
           [Sophos Reporting Interface].vThreatInstances as I ON C.ID = I.ComputerID  INNER JOIN
           [Sophos Reporting Interface].vThreatEventData as E ON I.ThreatID = E.ThreatID
    
    GROUP BY GP.GROUPID, I.ComputerID
    HAVING MAX(ActionTaken) = 'NONE'
    AND MIN(ActionTaken) = 'NONE'

    Check this query alone and then use that result to inner join with the rest of your tables.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, June 19, 2014 9:17 PM
  • Naomi's query auto corrected itself in the query window. The 1 that won't execute is:

    -- code #2 v4
    ;with TabEvents as(
    SELECT C.Name, C.IPAddressText, C.Managed, C.Connected, C.LastMessageTime, 
           GP.PathAndName, 
           E.ActionTakenName, 
           I.FullFilePath
      FROM dbo.Computers as C  INNER JOIN
           dbo.ComputerGroupMapping as CGM ON C.ID = CGM.ID  INNER JOIN
           [Sophos Reporting Interface].vGroupPathAndNameData as GP ON CGM.GroupID = GP.GroupID  INNER JOIN
           [Sophos Reporting Interface].vThreatInstances as I ON C.ID = I.ComputerID  INNER JOIN
           [Sophos Reporting Interface].vThreatEventData as E ON I.ThreatID = E.ThreatID
      WHERE (C.LastMessageTime > DATEADD(dd, -30, GETDATE())) 
            --AND (GP.PathAndName LIKE N'%Engineering Lab%') 
            --AND (C.Name = N'WIN-SERVER')
    )
    SELECT Name, IPAddressText, Managed, Connected, LastMessageTime, 
           PathAndName, ActionTakenName, FullFilePath
      from TabEvents as T1
      where ActionTakenName = 'None'
            and not exists (SELECT * from TabEvents as T2
                              where T2.Name = T1.Name and T2.ActionTakenName <> 'None');

    Thursday, June 19, 2014 9:19 PM
  • Post also result of 

    SELECT @@VERSION


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, June 19, 2014 9:23 PM
  • I tested here the code #2 v4 and there was no syntax error.
    Which program are you using to test queries? Management Studio?

    Try

    -- code #2 v8
    IF Object_ID('tempdb..#TabEvents') is not null DROP TABLE #TabEvents;
    SELECT C.ID, C.Name, C.IPAddressText, C.Managed, C.Connected, C.LastMessageTime, GP.PathAndName, E.ActionTakenName, I.FullFilePath INTO #TabEvents FROM dbo.Computers as C INNER JOIN dbo.ComputerGroupMapping as CGM ON C.ID = CGM.ID INNER JOIN [Sophos Reporting Interface].vGroupPathAndNameData as GP ON CGM.GroupID = GP.GroupID INNER JOIN [Sophos Reporting Interface].vThreatInstances as I ON C.ID = I.ComputerID INNER JOIN [Sophos Reporting Interface].vThreatEventData as E ON I.ThreatID = E.ThreatID WHERE (C.LastMessageTime > DATEADD(dd, -30, GETDATE())) --AND (GP.PathAndName LIKE N'%Engineering Lab%') --AND (C.Name = N'WIN-SERVER') --
    CREATE clustered INDEX I1 on #TabEvents(ID);

    --
    SELECT Name, IPAddressText, Managed, Connected, LastMessageTime, PathAndName, ActionTakenName, FullFilePath from #TabEvents as T1 where ActionTakenName = 'None' and not exists (SELECT * from #TabEvents as T2 where T2.ID = T1.ID and T2.ActionTakenName <> 'None');

    DROP TABLE #TabEvents;


        José Diz     Belo Horizonte, MG - Brasil


    • Edited by Jose.Diz Thursday, June 19, 2014 9:57 PM
    Thursday, June 19, 2014 9:34 PM
  • @@VERSION:

    Microsoft SQLServer 2008 R2 (SP1) - 10.50.2500.0 (X64) 

    Jun 17 2011 00:54:03 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    I'm using Microsoft SQL Server Management Studio to run these queries. I copied the last example you provided and it gives me another syntax error: SQL text cannot be represented in the grid pane and diagram pane.

    I think I understand the method your using. I will try manipulating the query you gave and see if I can't get it to run on my end.

    Thursday, June 19, 2014 10:07 PM
  • Don't try it in the query (or view designer). Just try it directly in the new sql query window.

    Also, SQL Server 2008 R2 has Service Pack 2. With SQL Server you always want to be on the latest service pack, so I suggest you to upgrade.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, June 19, 2014 10:29 PM
  • Execution through the query window worked, but unfortunately I still don't get any results.

    I'll let my DBA know we're behind on releases =)

    Thursday, June 19, 2014 10:33 PM
  • Did you also try what I suggested of only checking the relevant tables? 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, June 19, 2014 10:36 PM
  • I tried the subquery.

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near 'Sophos Reporting Interface'.

    Thursday, June 19, 2014 10:40 PM
  • I syntax checked the code and reformatted. Seems to be OK. What is @@version?

    Is this [Sophos Reporting Interface] a database or schema?

    IF Object_id('tempdb..#TabEvents') IS NOT NULL 
      DROP TABLE #tabevents; 
    
    SELECT C.id, 
           C.name, 
           C.ipaddresstext, 
           C.managed, 
           C.connected, 
           C.lastmessagetime, 
           GP.pathandname, 
           E.actiontakenname, 
           I.fullfilepath 
    INTO   #tabevents 
    FROM   dbo.computers AS C 
           INNER JOIN dbo.computergroupmapping AS CGM 
                   ON C.id = CGM.id 
           INNER JOIN [Sophos Reporting Interface].vgrouppathandnamedata AS GP 
                   ON CGM.groupid = GP.groupid 
           INNER JOIN [Sophos Reporting Interface].vthreatinstances AS I 
                   ON C.id = I.computerid 
           INNER JOIN [Sophos Reporting Interface].vthreateventdata AS E 
                   ON I.threatid = E.threatid 
    WHERE  ( C.lastmessagetime > Dateadd(dd, -30, Getdate()) ) 
    
    --AND (GP.PathAndName LIKE N'%Engineering Lab%')  
    --AND (C.Name = N'WIN-SERVER') 
    -- 
    CREATE CLUSTERED INDEX i1 
      ON #tabevents(id); 
    
    -- 
    SELECT name, 
           ipaddresstext, 
           managed, 
           connected, 
           lastmessagetime, 
           pathandname, 
           actiontakenname, 
           fullfilepath 
    FROM   #tabevents AS T1 
    WHERE  actiontakenname = 'None' 
           AND NOT EXISTS (SELECT * 
                           FROM   #tabevents AS T2 
                           WHERE  T2.id = T1.id 
                                  AND T2.actiontakenname <> 'None'); 
    
    DROP TABLE #tabevents;   


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Friday, June 20, 2014 12:46 AM
  • Can you post what you tried? Did you see my suggestion - removing for now extra tables except for Sophos tables.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, June 20, 2014 2:16 AM
  • Execution through the query window worked, but unfortunately I still don't get any results.

    I forgot the FullFilePath column.

    What is the difference between PathName and FullFilePath columns?

    The ID column in the dbo.Computer table identifies each computer uniquely?

    Try

    -- code #2 v9
    IF Object_ID('tempdb..#TabEvents') is not null DROP TABLE #TabEvents;
    
    SELECT C.ID, C.Name, C.IPAddressText, C.Managed, C.Connected, C.LastMessageTime, 
           GP.PathAndName, 
           E.ActionTakenName, 
           I.FullFilePath
      INTO #TabEvents
      FROM dbo.Computers as C  INNER JOIN
           dbo.ComputerGroupMapping as CGM ON C.ID = CGM.ID  INNER JOIN
           [Sophos Reporting Interface].vGroupPathAndNameData as GP ON CGM.GroupID = GP.GroupID  INNER JOIN
           [Sophos Reporting Interface].vThreatInstances as I ON C.ID = I.ComputerID  INNER JOIN
           [Sophos Reporting Interface].vThreatEventData as E ON I.ThreatID = E.ThreatID
      WHERE (C.LastMessageTime > DATEADD(dd, -30, GETDATE())) 
            --AND (GP.PathAndName LIKE N'%Engineering Lab%') 
            --AND (C.Name = N'WIN-SERVER')
    
    --
    CREATE clustered INDEX I1 on #TabEvents(ID);  -- optional, case #TabEvents has few rows
    
    --
    SELECT Name, IPAddressText, Managed, Connected, LastMessageTime, 
           PathAndName, ActionTakenName, FullFilePath
      from #TabEvents as T1
      where ActionTakenName = 'None'
            and not exists (SELECT * from #TabEvents as T2
                              where T2.ID = T1.ID
                                    and T2.FullFilePath = T1.FullFilePath
                                    and T2.ActionTakenName <> 'None');
    
    --DROP TABLE #TabEvents;

     
    If the execution of the code above does not show any results, please run the following code:

    SELECT top(5) * from #TabEvents order by ID, FullFilePath;
    SELECT Count(*) as Qty from #TabEvents where ActionTakenName = 'None';
    SELECT top (10) T.* from #TabEvents as T inner join (SELECT T2.ID, T2.FullFilePath from #TabEvents as T2 where T2.ActionTakenName = 'None') as F3 on T.ID = F3.ID and T.FullFilePath = F3.FullFilePath
    order by T.ID, T.FullFilePath;


        José Diz     Belo Horizonte, MG - Brasil


    Friday, June 20, 2014 5:22 AM
  • That last query did the trick! Thank you both for your assistance =)
    Friday, June 20, 2014 4:39 PM