Client Management Suite

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.

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.

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))))
   )
 

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?

 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())

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.

Thanks all!

Thanks CFarrell!

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"

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?