Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Find last active date of SMS client?

Posted on 2009-04-08
2
Medium Priority
?
853 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 750 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

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 is around source server preparation. No migration is an easy migration, there is a…
Understanding the various editions available is vital when you decide to purchase Windows Server 2012. You need to have a basic understanding of the features and limitations in each edition in order to make a well-informed decision that best suits …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

571 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