Asset Management Suite

 View Only
  • 1.  Altiris Collection based on "Division" in MS-Exchange

    Posted Jan 19, 2011 03:54 AM

    Hi,

     

    I need to create collections based on Division. I am not an advanced report builder and have no SQL background i've just learned what i have by breaking down existing reports and scanning the web.

     

    I have the below code to create a SQL query based collection. This works BUT some divisions are massive and the Query times out when populating the collection.

     

    The following error occurred on the page 'CollectionModify.aspx':

    Request timed out.

     

    Is there a better way to get a collection for Divisions?

     

    t0.[Guid]
    FROM Inv_AeX_EU_Contact_Detail T1
    Left Join [vcomputer] T0 on t0.[User] = T1.[Account]
                 
    Where      
    T1.[Department] Like '%Claims%'      

     



  • 2.  RE: Altiris Collection based on "Division" in MS-Exchange

    Posted Feb 16, 2011 10:54 PM

    In general, you should avoid using the vComputer "view" in Collections, for the very reason you mention...it is very inefficient and slow; if you look at the query behind it, it is truly a wonder if ever completes! :)

    Also you should be doing an INNER JOIN (or just JOIN, INNER is implied in that case).  INNER JOINs are much faster and the intermediate in-memory table is smaller.

    I might use something like the following:

     SELECT vr.Guid FROM vResource vr
    JOIN Inv_AeX_AC_Primary_User pu ON vr.Guid = pu._ResourceGuid
    JOIN Inv_AeX_EU_Contact_Detail contact ON vr.Guid = contact._ResourceGuid
         AND pu.[User] = contact.[Account]
    WHERE contact.[Department] LIKE '%Claims%'
    AND pu.Month = DATEPART(month, GETDATE()) 
    

    Alternatively, you could use Inv_AeX_AC_Machine_Usage.  There is a [User] column in that table as well that you could join to Contact_Detail, and add "AND Rank = 1" to the WHERE clause (I think I have that right...I don't have access to my NS at the moment).  A final few options are to use Inv_AeX_EU_Logon_Users, which also has a [User] column and a "total hours logged on", or lastly (and not highly recommended) the [Last Logon User] column in Inv_AeX_AC_Identification.

    I hope that helps!