Solved

Find last active date of SMS client?

Posted on 2009-04-08
2
841 Views
Last Modified: 2013-11-21
What is the easiest way to see the last time a SMS client was active?  I have several machines that show up in the database that no longer exist.  For now, this is how we actually want it.  But I need to run a query and see what is really out there.  I tried to run a Last Hardware Scan query but this isnt the filter Im looking for.  Just need to show the last check-in time I guess you could call it.

Thanks!
0
Comment
Question by:NBquery
2 Comments
 
LVL 6

Accepted Solution

by:
question earned 250 total points
ID: 24119595
See below SQL queries
SELECT SMS_R_System.Name0 AS [Computer Name], v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address],
SMS_R_System.Active0 AS [Is SMS Client Active?], SMS_R_System.Obsolete0 AS [Is SMS Client Obsolete ?],
SMS_G_System_LastSoftwareScan.LastUpdateDate AS [Last Software Inventory],
___System_WORKSTATION_STATUS0.LastHWScan AS [Last Hardware Inventory], SMS_R_System.SMS_Unique_Identifier0 AS [SMS GUID], SMS_G_System_OPERATING_SYSTEM.Caption0 AS [Operating System]
FROM System_DISC SMS_R_System INNER JOIN
Operating_System_DATA SMS_G_System_OPERATING_SYSTEM ON
SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey INNER JOIN
SoftwareInventoryStatus SMS_G_System_LastSoftwareScan ON SMS_G_System_LastSoftwareScan.ClientId = SMS_R_System.ItemKey INNER JOIN
WorkstationStatus_DATA ___System_WORKSTATION_STATUS0 ON ___System_WORKSTATION_STATUS0.MachineID = SMS_R_System.ItemKey INNER JOIN
v_RA_System_IPAddresses ON SMS_R_System.ItemKey = v_RA_System_IPAddresses.ResourceID
WHERE (SMS_R_System.Active0 = 0)
ORDER BY SMS_R_System.Name0

Obsolete SMS Clients

SELECT SMS_R_System.Name0 AS [Computer Name], v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address],
SMS_R_System.Active0 AS [Is SMS Client Active?], SMS_R_System.Obsolete0 AS [Is SMS Client Obsolete ?],
SMS_G_System_LastSoftwareScan.LastUpdateDate AS [Last Software Inventory],
___System_WORKSTATION_STATUS0.LastHWScan AS [Last Hardware Inventory], SMS_R_System.SMS_Unique_Identifier0 AS [SMS GUID],
SMS_G_System_OPERATING_SYSTEM.Caption0 AS [Operating System]
FROM System_DISC SMS_R_System INNER JOIN
Operating_System_DATA SMS_G_System_OPERATING_SYSTEM ON
SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey INNER JOIN
SoftwareInventoryStatus SMS_G_System_LastSoftwareScan ON SMS_G_System_LastSoftwareScan.ClientId = SMS_R_System.ItemKey INNER JOIN
WorkstationStatus_DATA ___System_WORKSTATION_STATUS0 ON
___System_WORKSTATION_STATUS0.MachineID = SMS_R_System.ItemKey INNER JOIN
v_RA_System_IPAddresses ON SMS_R_System.ItemKey = v_RA_System_IPAddresses.ResourceID
WHERE (SMS_R_System.Obsolete0 = 1)
ORDER BY SMS_R_System.Name0
0
 

Author Closing Comment

by:NBquery
ID: 31568343
Did not get a chance to test out.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

##the calculator has been updated to version 1.6 please download the use the updated version## Hi there, After the previous post of the original version of the calculator here : http://www.experts-exchange.com/articles/OS/Microsoft_Operatin…
Welcome to my series of short tips on migrations. Whilst based on Microsoft migrations the same principles can be applied to any type of migration. My first tip Migration Tip #1 – Source Server Health can be found listed in my profile here: http:…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question