Video Screencast Help

Filter to show agents not communicating for 24 hours

Created: 05 Mar 2013 • Updated: 06 Mar 2013 | 6 comments
This issue has been solved. See solution.

I currently have an open case with Symantec about this, but they are taking too long for my liking.  Here is the background...

 

We are running a nightly discovery against AD to find all computers.  We are then automatically pushing the Symantec agent out to all machines showing in the without agent installed filter.  That all works pefectly fine. The problem is when we reimage a computer.  They still show as having the agent installed in the console when it no longer is.  We do not have the Symantec agent installed on any of our images.

 

Symantec decided to create a new filter to list all computers that had not communicated with the server for 24 hours.  That filter could then be added in to the automatic push so it would attempt to reinstall the agent on those machines.  I'm not 100% sure this is what I want to do, but it seems like it will work to take care of the issues we have with getting the agent installed after reimaging.  We never had this problem with NS 6.5 prior to migrating to CMS 7.1.  Of couse...we also installed the Altiris agent via GPO then.  We are no longer doing that since the migration to CMS 7.1.

 

So Symantec helped me write a new filter.  Every time I try to update the results, though, I get the following error message:

"Error updating the filter membership. Please ensure the first column returned from the query is of type GUID."

 

I have searched and found this same error, but it is a known issue with 7.0 SP2 that was supposed to be resolved in 7.0 SP3.  It also happens when creating a new filter using Query Builder mode. I am creating my filter using Raw SQL.  Here is the SQL entry I am attempting to use:

 

DECLARE @TrusteeScope AS nvarchar(max)
SET @TrusteeScope = '{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{727546B7-D87F-4E70-93C2-EF2853A70046},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}'
  
DECLARE @Last_Modified_At_Least_N_Hours AS INT
SET @Last_Modified_At_Least_N_Hours = 24
SELECT DISTINCT
 vc.[Name] AS [Name],
 vc.[Description] AS [Description],
 [AeX_AC_Identification].[OS Name] AS [OS Name],
 [AeX_AC_TCPIP].[Primary DNS Suffix] AS [Primary DNS Suffix],
 rus.[CreatedDate] AS [Date Created],
 rus.[ModifiedDate] AS [Date Modified],
 DATEDIFF(hh, rus.[ModifiedDate], GETDATE()) AS [Hours Since Last Modified]
FROM
 [vRM_Computer_Item] AS vc
 LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [AeX_AC_Identification]
  ON vc.[Guid] = [AeX_AC_Identification].[_ResourceGuid]
 LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [AeX_AC_TCPIP]
  ON vc.[Guid] = [AeX_AC_TCPIP].[_ResourceGuid]
 LEFT OUTER JOIN (
   SELECT DISTINCT ResourceGuid, MAX(CreatedDate) CreatedDate, MAX(ModifiedDate) ModifiedDate
   FROM [ResourceUpdateSummary]
   GROUP BY ResourceGuid
  ) AS rus
  ON vc.[Guid] = rus.[ResourceGuid]
 INNER JOIN [fnGetTrusteeScopedResources](@TrusteeScope) AS [fnGTSR_5]
  ON [vc].[Guid] = [fnGTSR_5].[ResourceGuid] 
WHERE
 @Last_Modified_At_Least_N_Hours >= DATEDIFF(hh, rus.[ModifiedDate], GETDATE())

 

I am going to be honest, I absolutely cannot write a SQL report to save my life. This is basically data from a canned report that has been modified to fit my needs.  If anybody can shed any light on this for me I would greatly appreciate it.  Symantec is working on the issue, but they're taking a little longer then I would like. I just reimaged an entire lab today so I now have those 35 machines no longer communicating with the CMS server.  They show as having the agent installed so it will not install the agent again.  I know I could just delete them so they would get re discovered, but that defeats the purpose.  We have 8,000 workstations and multiple technicians that reimage computers every day.  That would require the CMS admin (me) to know every single computer that was reimaged so it could manually be deleted.  That, obviously, is not a viable solution.

Operating Systems:

Comments 6 CommentsJump to latest comment

SaschaH's picture

The first select needs to be vc.[Guid]. Easy fix.

...

SELECT DISTINCT

 vc.[Guid],
 vc.[Name] AS [Name],
 vc.[Description] AS [Description],
 [AeX_AC_Identification].[OS Name] AS [OS Name],
 [AeX_AC_TCPIP].[Primary DNS Suffix] AS [Primary DNS Suffix],
 rus.[CreatedDate] AS [Date Created],
 rus.[ModifiedDate] AS [Date Modified],

...

Everything is done with the Guids internally so if the filter need to deliver that first so any task or policy knows what resource to talk to.

Bechtle – your strong IT partner. Today and tomorrow

If that seems to help, please "Mark as Solution"

SOLUTION
jeremyboger's picture

SaschaH,

 

Thank you!  That is exactly what I needed.  Amazing how one little line keeps the entire thing from working.  I really need to work on my SQL query writing skills.

jeremyboger's picture

Looks like I spoke a little too soon.  The filter is working now...in as much as it is showing results.  Unfortunately it appears to be showing all computers known to the management server.  I need it to show me machines that have not communicated with the server for 24 hours or more.

I'm sure this is just another SQL query mistake on my part.  It is more than likley not looking in the correct location.  I just need the filter to show me all machines that have not communicated with the server for 24 hours or more so I can add that filter to the automatic agent push job.  This will take care of our problems with machines not having the agent installed after they are reimaged.  Here is the SQL query I am currently using for the filter:

DECLARE @TrusteeScope AS nvarchar(max)
SET @TrusteeScope = '{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{727546B7-D87F-4E70-93C2-EF2853A70046},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}'
  
DECLARE @Last_Modified_At_Least_N_Hours AS INT
SET @Last_Modified_At_Least_N_Hours=24
SELECT DISTINCT
 vc.[Guid],
                vc.[Name] AS [Name],
 vc.[Description] AS [Description],
 [AeX_AC_Identification].[OS Name] AS [OS Name],
 [AeX_AC_TCPIP].[Primary DNS Suffix] AS [Primary DNS Suffix],
 rus.[CreatedDate] AS [Date Created],
 rus.[ModifiedDate] AS [Date Modified],
 DATEDIFF(hh, rus.[ModifiedDate], GETDATE()) AS [Hours Since Last Modified]
FROM
 [vRM_Computer_Item] AS vc
 LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [AeX_AC_Identification]
  ON vc.[Guid] = [AeX_AC_Identification].[_ResourceGuid]
 LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [AeX_AC_TCPIP]
  ON vc.[Guid] = [AeX_AC_TCPIP].[_ResourceGuid]
 LEFT OUTER JOIN (
   SELECT DISTINCT ResourceGuid, MAX(CreatedDate) CreatedDate, MAX(ModifiedDate) ModifiedDate
   FROM [ResourceUpdateSummary]
   GROUP BY ResourceGuid
  ) AS rus
  ON vc.[Guid] = rus.[ResourceGuid]
 INNER JOIN [fnGetTrusteeScopedResources](@TrusteeScope) AS [fnGTSR_5]
  ON [vc].[Guid] = [fnGTSR_5].[ResourceGuid] 
WHERE
 @Last_Modified_At_Least_N_Hours >= DATEDIFF(hh, rus.[ModifiedDate], GETDATE())

 

I'm sure that it is just a bonehead mistake on my part. I am absolutely no good with SQL.  If anybody can point out where I went wrong with this, I would greatly appreciate it. Thanks.

jeremyboger's picture

JoeShmo,

We have just recently migrated to CMS 7.1 from NS 6.  With NS6, we always deployed the agent via group policy.  We decided to just use the CMS server this time to push the agent out instead of using a group policy.  We do not have it on our images because we were migrating.  When our images get updated in the future this might be added to them.  That's not been decided at this time.  For now, though, it is not on there because we just migrated to CMS within the past month.  Yes...we are that far behind with the rest of the world.

jeremyboger's picture

Looks like the answer was right in front of my face.  Thanks to kpjernigan's post on https://www-secure.symantec.com/connect/forums/sql-query-filter-report-against-only-computers-have-logged-recently I was able to get a filter to show me what I wanted.  Here is the final SQL query I ended up using for the filter. I ended up going with 2 days instead of 1 because 1 day showed too many machines still.  1 day was showing over 2000 machines and 2 days (currently) is showing 300.  I figure we can go for 2 days without the agent installed.

 

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()) >= 2 --Number of Days