BRONZE PARTNER:
BRONZE PARTNER:
Industry News:
| |
| |
|
|
|
|
|
| SMS FAQ: Query to Find and Delete Systems with Inventory over 30 Days Old |
|
|
|
By: Cliff Hobbs
Posted On: 6/27/2002
Contributed by Don Hite:
-- SQL query for workstation status greater than 30 days--
select SMS_R_System.Name,
SMS_R_System.LastLogonUserName,
SMS_G_System_WORKSTATION_STATUS.LastHardwareScan
from SMS_R_System
inner join SMS_G_System_WORKSTATION_STATUS
on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_LastSoftwareScan on
SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId
where
DATEPART(DD, SMS_G_System_LastSoftwareScan.LastScanDate) > 30
-- SQL Query to delete machines from the SMS database --Use SMS_xxx -- Your 3 Letter Site Code
GoDelete from System_DISC where Netbios_Name0 = "machine_name"
-- copy and paste netbios name below as well to kill the history.
Delete from System_DATA where Name0 = "machine_name" Go
You can set up a trigger to go off in 7 day increments to further automate the process or just initiate a delete special in the MMC.
Contributed by Eran Spitz 26/6/02:
Here's a SQL Query that achieves the same goal without using the Datepart(DD.... function (which parses the Day parameter out of the Date variable and then checks whether it's larger than 30).
This Query instead uses the Datediff function to return the Delta (by days) from the current date and the LastSoftwareScandate:
select sys.name0, sys.user_name0, sof.lastupdatedate
from system_disc sys, softwareinventorystatus sof
where sys.itemkey=sof.clientid
and datediff(day,sof.LastUpdateDate,getdate())>30
order by sof.lastupdatedate
This will return ComputerName;L.UserName;LastSoftwareScanDate with the condition LastSoftwareScanDate>30 days and will order it by scan date.
|
|
|
|
|
|
