Wednesday, September 17, 2014

SQL Report for the below (Model & Name & Last Logon Timestamp & Manufacturer and more)

SQL :
Select distinct SMS_R_System.ItemKey,
SMS_R_System.Name0,SMS_R_System.User_Name0,SMS_R_System.Last_Logon_Timestamp0,
SMS_G_System_OPERATING_SYSTEM.Caption00,
SMS_G_System_OPERATING_SYSTEM.InstallDate00,
SMS_G_System_SYSTEM_ENCLOSURE.Manufacturer00,
SMS_G_System_COMPUTER_SYSTEM.Model00,
SMS_G_System_SYSTEM_ENCLOSURE.SerialNumber00,
SMS_G_System_SYSTEM_ENCLOSURE.SMBIOSAssetTag00,
SMS_G_System_PC_BIOS.SMBIOSBIOSVersion00 
from vSMS_R_System AS SMS_R_System 
LEFT OUTER JOIN System_System_OU_Name_ARR AS __mSystem_System_OU_Name_ARR0 ON SMS_R_System.ItemKey = __mSystem_System_OU_Name_ARR0.ItemKey INNER JOIN System_Enclosure_DATA AS SMS_G_System_SYSTEM_ENCLOSURE ON SMS_G_System_SYSTEM_ENCLOSURE.MachineID = SMS_R_System.ItemKey 
INNER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey 
INNER JOIN PC_BIOS_DATA AS SMS_G_System_PC_BIOS ON SMS_G_System_PC_BIOS.MachineID = SMS_R_System.ItemKey INNER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey 
where __mSystem_System_OU_Name_ARR0.System_OU_Name0 = N'Domain.com/OU/COMPUTERS' 
and (SMS_G_System_OPERATING_SYSTEM.Caption00= @OSVersion)

No comments:

Post a Comment