SQL Query To Find Obsolete Machines

Use this SQL query to find machines in your SMS database that are Obsolete along with information about the obsolete client machine(s)

 

SQL Query:

 

Select

SD.Netbios_Name0 Name,

 

'Obsolete' = Case

When SD.Obsolete0 = 1 Then 'YES'

Else 'NO'

End,

 

'Active' = Case

When SD.Active0 = 1 Then 'YES'

Else 'NO'

End,

 

'Client' = Case

When SD.Client0 = 1 Then 'YES'

Else 'NO'

End,

 

'Client Type' = Case

When SD.Client_Type0 = 0 Then 'Legacy Client'

When SD.Client_Type0 = 1 Then 'Advanced Client'

Else 'Device Client'

End,

 

SD.Client_Version0 'Client Version',

'Decommissioned DDR Set' = Case

When SD.Decommissioned0 = 1 Then 'YES'

Else 'NO'

End,

 

SD.Hardware_ID0 'Hardware ID',

SD.User_Domain0 'Account Domain',

SD.User_Name0 'Last Logon User Name',

SD.Operating_System_Name_and0 NOS,

SD.Resource_Domain_Or_Workgr0 'Resource Domain',

SD.Sms_Unique_Identifier0 'SMAS Unique Identifier',

Convert(Char(101), WD.LastHwScan) 'Last Hardware Scan Date',

Convert(Char(101), CS.TimeKey)  'Last Computer Time Stamp'

 

From System_Disc SD

Join WorkstationStatus_Data WD On WD.MachineID = SD.ItemKey 

Join Computer_System_Data CS On CS.MachineID = SD.ItemKey  

 

Where (SD.Obsolete0 = 1 AND SD.Active0 = 0)
Published Sunday, August 27, 2006 9:57 PM by dhite
Filed under:

Comments

No Comments