Video Screencast Help

SQL query to filter a report against only computers that have logged in recently

Created: 06 Mar 2013 | 1 comment

Hi guys,

We do have our purge settings set at 3 weeks, so our database is pretty updated.  We do need to run some reports and filter for computers that have contacted the Altiris database in the past 3 days, etc and I am not that great at SQL queries.  Is there a table I can query with a join statement to filter my queries for very recent computers?  Just trying to remove the results in the reports for computers that are still in the database, but have not contact domain in 2 weeks, etc.  The query I am running is below

Thanks,

Dan

 

 

SELECT
   vC.Guid,
   vC.[Name],
   vC.[User],
   addrem.DisplayName,
   addrem.DisplayVersion
FROM
   [vComputer] vC
      INNER JOIN [Inv_AddRemoveProgram] AS [addrem]
         ON ([vC].[Guid] = [addrem].[_ResourceGuid])
WHERE
  [addrem].DisplayName like 'Microsoft Office%2003'
and [addrem].DisplayName NOT LIKE '%Primary%'
and [addrem].DisplayName NOT LIKE '%Communicator%'
and [addrem].DisplayName NOT LIKE '%Live%'
and [addrem].DisplayName NOT LIKE '%Viewer%'
and [addrem].DisplayName NOT LIKE '%Frontpage%'
and [addrem].DisplayName NOT LIKE '%Visio%'
and [addrem].DisplayName NOT LIKE '%Outlook%'
and [addrem].DisplayName NOT LIKE '%Project%'
and [addrem].DisplayName NOT LIKE '%Visual%'
and [addrem].DisplayName NOT LIKE '%Sharepoint%'
and [addrem].DisplayName NOT LIKE '%Access%'
and [addrem].DisplayName NOT LIKE '%Excel%'
and [addrem].DisplayName NOT LIKE '%Publisher%'
and [addrem].DisplayName NOT LIKE '%Word%'
and [addrem].DisplayName NOT LIKE '%Sounds%'
and [addrem].DisplayName NOT LIKE '%Converter%'
and [addrem].DisplayName NOT LIKE '%InfoPath%'
and [addrem].DisplayName NOT LIKE '%Proof%'
and [addrem].DisplayName NOT LIKE '%Service Pack%'
and [addrem].DisplayName NOT LIKE '%MUI%'
and [addrem].DisplayName NOT LIKE '%Components%'
and [addrem].DisplayName NOT LIKE '%File Validation%'
and [addrem].DisplayName NOT LIKE '%Runtime%'
and [addrem].InstallFlag = 1
 

Operating Systems:

Comments 1 CommentJump to latest comment

kpjernigan's picture

Try this out.

-----------------------------------------------------------------------------------------------------------------------------------

 

SELECT DISTINCT
   vC.Guid,
   vC.[Name],
   vC.[User],
   addrem.DisplayName,
   addrem.DisplayVersion
FROM
   vComputerExcludingIPInfo vC --Faster SQL View of vComputer when IP is not needed.
      INNER JOIN [Inv_AddRemoveProgram] AS [addrem]
         ON ([vC].[Guid] = [addrem].[_ResourceGuid])
         JOIN Inv_AeX_AC_Identification acid ON acid._ResourceGuid = vc.Guid
WHERE
DATEDIFF(dd, acid.[Client Date], GETDATE()) >= 14 --Number of Days
and [addrem].DisplayName like 'Microsoft Office%2003'
and [addrem].DisplayName NOT LIKE '%Primary%'
and [addrem].DisplayName NOT LIKE '%Communicator%'
and [addrem].DisplayName NOT LIKE '%Live%'
and [addrem].DisplayName NOT LIKE '%Viewer%'
and [addrem].DisplayName NOT LIKE '%Frontpage%'
and [addrem].DisplayName NOT LIKE '%Visio%'
and [addrem].DisplayName NOT LIKE '%Outlook%'
and [addrem].DisplayName NOT LIKE '%Project%'
and [addrem].DisplayName NOT LIKE '%Visual%'
and [addrem].DisplayName NOT LIKE '%Sharepoint%'
and [addrem].DisplayName NOT LIKE '%Access%'
and [addrem].DisplayName NOT LIKE '%Excel%'
and [addrem].DisplayName NOT LIKE '%Publisher%'
and [addrem].DisplayName NOT LIKE '%Word%'
and [addrem].DisplayName NOT LIKE '%Sounds%'
and [addrem].DisplayName NOT LIKE '%Converter%'
and [addrem].DisplayName NOT LIKE '%InfoPath%'
and [addrem].DisplayName NOT LIKE '%Proof%'
and [addrem].DisplayName NOT LIKE '%Service Pack%'
and [addrem].DisplayName NOT LIKE '%MUI%'
and [addrem].DisplayName NOT LIKE '%Components%'
and [addrem].DisplayName NOT LIKE '%File Validation%'
and [addrem].DisplayName NOT LIKE '%Runtime%'
and [addrem].InstallFlag = 1
AttachmentSize
SQL query to filter a report against only computers that have logged in recently.zip 821 bytes

-Kev