Video Screencast Help

Computers checked in the last 30 days

Created: 29 Aug 2013 • Updated: 29 Aug 2013 | 9 comments
This issue has been solved. See solution.

Was looking for a SQL query to report computers that have checked in the last 30 days. I could create a report using the query builder, but could not figure out how to set the Client Date to less than 30 days. WOuld also like to have OS Name and SystemType fields in the SQL Report.

 

Thanks in advance.

Operating Systems:

Comments 9 CommentsJump to latest comment

andykn101's picture

I don't have a system in front of me at the moment but the SQL function you're after is DATEDIFF and use GETDATE() for the current date. SQL help should guide you.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

huseinm's picture

This is my query in SQL form. How do I change the date from a hard coded one to a dynamic one liek 30 days. Sorry not good with SQL.

 

DECLARE @v1_TrusteeScope nvarchar(max)
   SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name] AS [Workstation Name],
   [dca3_AeX AC Identification].[Client Date] AS [Last Checked In],
   [dca3_AeX AC Identification].[OS Name],
   [dca3_AeX AC Identification].[System Type]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
         ON ([vri2_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
WHERE
   (
      (
         ([dca3_AeX AC Identification].[Client Date] >= '2013-07-29T00:00:00')
      )
      AND
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )
 

huseinm's picture

I think I figured it out, thanks Andy!

DATEDIFF (dd, [dca3_AeX AC Identification].[Client Date], GETDATE()) <30

Now is "Client Date" the right property to determine if the machine is talking to NS or not?

CFarrell's picture

 Try using DATEADD instead of the date. The value 30 in abs(30) is number of days

  [dca3_AeX AC Identification].[Client Date] >= DATEADD (dd, -1*abs(30), getdate())

andykn101's picture

It would be something like:

WHERE

DATEDEIFF (d,[dca3_AeX AC Identification].[Client Date], GETDATE())>30

The easiest way to add tables and fields is to, in SQL Server Management Studio, expand the Symantec DB and right click on Views > New View. Paste this part of the SQL into the third pane down:

SELECT
[vri2_Computer].[Guid] AS [_ItemGuid],
[vri2_Computer].[Name] AS [Workstation Name],
[dca3_AeX AC Identification].[Client Date] AS [Last Checked In],
[dca3_AeX AC Identification].[OS Name],
[dca3_AeX AC Identification].[System Type]
FROM
[vRM_Computer_Item] AS [vri2_Computer]
LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
ON ([vri2_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
WHERE
(
(
(DATEDEIFF (d,[dca3_AeX AC Identification].[Client Date], GETDATE())>30)
)

Then you can select more fields in the top pane or add more tables or views. When you add more tables you need to create the joins by dragging and dropping fields from different tables to join them. Usually GUID fields.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

SOLUTION
huseinm's picture

I don't understand the reults. Is "Client Date=Last Basic Inventory Received" the right property to use? I would think we would want to use the "Last Configuration Request"

andykn101's picture

Yes, I think Client Date is the date of the last Inventory, Basic or from Inv Soln.

Isn't there a built in report for Last config?

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.