Database

INTERSECT

-- Mailbox size query users who are set to unlimited, distinct username field with disabled mailboxes removed from search
SELECT AED.ObjectName,
       AED.ObjectCanonical,
       AEM.EventMessage,
       AED.OrganizationalUnit                               AS DirectoryOrganizationalUnit,
       AET.UserName,
       AET.SeverityID,
       AET.TimeDetected,
       AET.ValueOld,
       AET.ValueNew,
       ECS.SubsystemID,
       ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
       ISNULL(TSR.ServerName, TWP.MachineName)              AS ServerName,
       EAN.ActionName,
       ISNULL(TDN.DomainName, TWP.WorkgroupName)            AS DomainName,
       AET.ResultID,
       AET.EventID,
       ECS.ValueTypeID,
       AET.MissingOld,
       AET.MissingNew,
       AET.TimeZoneOffset
FROM   Audit.Event AS AET
       INNER JOIN [Event].Class AS ECS
         ON AET.EventClassID = ECS.EventClassID
       INNER JOIN Audit.EventMessage AS AEM
         ON AET.EventID = AEM.EventID
       INNER JOIN [Event].[Action] AS EAN
         ON AET.ActionID = EAN.ActionID
       LEFT OUTER JOIN Topology.[Server] AS TSR
         ON AET.AgentID = TSR.ServerID
       LEFT OUTER JOIN Topology.Domain AS TDN
         ON TSR.DomainID = TDN.DomainID
       LEFT OUTER JOIN Topology.Workgroup AS TWP
         ON AET.AgentID = TWP.MachineID
       LEFT OUTER JOIN Audit.EventDirectory AS AED
         ON AET.EventID = AED.EventID
WHERE  AET.TimeDetected >= '2012-06-08T04:00:00' 
       AND EXISTS(SELECT AET.EventID 
                  FROM   [Event].Class AS _EC 
                  WHERE  EXISTS(SELECT * 
                                FROM   Query.EventClass AS _ECQ 
                                WHERE  _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A' 
                                       AND _ECQ.ProcessID = 0 
                                       AND StorageClassID = 0 
                                       AND _ECQ.EventClassID = _EC.EventClassID) 
                         AND _EC.EventClassID = AET.EventClassID) 
       AND NOT EXISTS(SELECT * 
                      FROM   (SELECT _QA.SID AS UserSID 
                              FROM   Query.[Account] AS _QA 
                              WHERE  _QA.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A' 
                                     AND _QA.ProcessID = 0) AS _SA 
                      WHERE  _SA.UserSID = AET.UserSID) 
                    
       AND ValueNew = '<Not-Set>' 
       
       AND UserName NOT IN (
       
       SELECT DISTINCT           --AED.ObjectName, 
                --AED.ObjectCanonical, 
--                  AED.OrganizationalUnit                               AS DirectoryOrganizationalUnit, 
--                  AEM.EventMessage, 
--                  AET.TimeDetected, 
--                  ECS.SubsystemID, 
                  AET.UserName--, 
--                  ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName, 
--                  ISNULL(TSR.ServerName, TWP.MachineName)              AS ServerName, 
--                  EAN.ActionName, 
--                  ISNULL(TDN.DomainName, TWP.WorkgroupName)            AS DomainName, 
--                  AET.ResultID, 
--                  AET.EventID, 
--                  ECS.ValueTypeID, 
--                  AET.MissingOld, 
--                  AET.MissingNew, 
--                  AET.TimeZoneOffset 
FROM   Audit.Event AS AET 
       INNER JOIN [Event].Class AS ECS 
         ON AET.EventClassID = ECS.EventClassID 
       INNER JOIN Audit.EventMessage AS AEM 
         ON AET.EventID = AEM.EventID 
       INNER JOIN [Event].[Action] AS EAN 
         ON AET.ActionID = EAN.ActionID 
       LEFT OUTER JOIN Topology.[Server] AS TSR 
         ON AET.AgentID = TSR.ServerID 
       LEFT OUTER JOIN Topology.Domain AS TDN 
         ON TSR.DomainID = TDN.DomainID 
       LEFT OUTER JOIN Topology.Workgroup AS TWP 
         ON AET.AgentID = TWP.MachineID 
       LEFT OUTER JOIN Audit.EventDirectory AS AED 
         ON AET.EventID = AED.EventID 
WHERE  AET.TimeDetected >= '2012-06-07T04:00:00' 
       AND EXISTS(SELECT AET.EventID 
                  FROM   [Event].Class AS _EC 
                  WHERE  EXISTS(SELECT * 
                                FROM   Query.EventClass AS _ECQ 
                                WHERE  _ECQ.QueryID = 'C37D85D6-B725-4E98-A338-B8917CA5784F' 
                                       AND _ECQ.ProcessID = 0 
                                       AND StorageClassID = 0 
                                       AND _ECQ.EventClassID = _EC.EventClassID) 
                                       
                         AND _EC.EventClassID = AET.EventClassID) 
                    
                      
								  )
       
 ORDER BY ObjectName                   
                      
--ORDER  BY TimeDetected DESC, 
--          UserName DESC  

David Mujica wrote:

My response gave a viable option for him to determine the difference between tables in two databases. I did not try to read into the 'why' or 'what for'.

David Mujica wrote:

There is no reason to be sarcastic when replying. Just stay professional.

Bastard Programmer from Hell
if you can't read my code, try converting it here[^]

LionAM wrote:

how to implement this as a weak reference pattern.

LionAM wrote:

To make loading the data on startup not unnecessary slow