Left Outer join v_Gs_Operating_System on v_Gs_Operating_System. Left Outer join v_GS_SYSTEM_ENCLOSURE on v_GS_SYSTEM_ENCLOSURE.ResourceID = VRS.ResourceId Left Outer join v_GS_PROCESSOR on v_GS_PROCESSOR.ResourceID = VRS.ResourceId Left Outer join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID = VRS.ResourceId Left Outer join Computer_System_DATA on Computer_System_DATA.MachineID = VRS.ResourceId Left Outer join v_GS_WORKSTATION_STATUS on v_GS_WORKSTATION_STATUS.ResourceID = VRS.ResourceId Left Outer join Operating_System_DATA on Operating_System_DATA.MachineID = VRS.ResourceId Left Outer join PC_BIOS_DATA on PC_BIOS_DATA.MachineID = VRS.ResourceId V_GS_WORKSTATION_STATUS.LastHWScan as 'LastHWScan',ĭateDiff(D, v_GS_WORKSTATION_STATUS.LastHwScan, GetDate()) as 'LastHWScanAge' Max(v_GS_LOGICAL_DISK.Size0 / 1024) AS 'TotalHDDSize(GB)', Operating_System_DATA.Version00 as 'Version', Operating_System_DATA.CSDVersion00 as 'ServicePack', When Operating_System_DATA.Caption00 = 'Microsoft(R) Windows(R) Server 2003, Web Edition' Then 'Microsoft(R) Windows(R)Įlse Operating_System_DATA.Caption00 End 'OSName', When Operating_System_DATA.Caption00 = 'Microsoft(R) Windows(R) Server 2003, Standard Edition' Then 'Microsoft(R) Windows(R) Windows(R) Server 2003 Enterprise Edition' When v_GS_COMPUTER_SYSTEM.domainrole0 = 5 Then 'Primary Domain Controller'Ĭase when Operating_System_DATA.Caption00 = 'Microsoft(R) Windows(R) Server 2003, Enterprise Edition' Then 'Microsoft(R) When v_GS_COMPUTER_SYSTEM.domainrole0 = 4 Then 'Backup Domain Controller' When v_GS_COMPUTER_SYSTEM.domainrole0 = 3 Then 'Member Server' When v_GS_COMPUTER_SYSTEM.domainrole0 = 2 Then 'Standalone Server' When v_GS_COMPUTER_SYSTEM.domainrole0 = 1 Then 'Member Workstation' VRS.User_Domain0+'\'+ VRS.User_Name0 as 'UserName',Ĭase when v_GS_COMPUTER_SYSTEM.domainrole0 = 0 then 'Standalone Workstation' V_GS_COMPUTER_SYSTEM.Domain0 as 'DomainName', When Computer_System_DATA.Manufacturer00 like 'MICRO-STAR%' Then 'MICRO-STAR'Įlse Computer_System_DATA.Manufacturer00 End 'Manufacturer',Ĭomputer_System_DATA.SystemType00 as 'OSType', When Computer_System_DATA.Manufacturer00 like 'VIA Technologies%' Then 'VIA Technologies' When Computer_System_DATA.Manufacturer00 like 'Gigabyte%' Then 'Gigabyte' PC_BIOS_DATA.SMBIOSBIOSVersion00 as 'BiosVersion',Ĭase when Computer_System_DATA.Manufacturer00 like 'VMware%' Then 'VMWare' PC_BIOS_DATA.ReleaseDate00 as 'ReleaseDate', V_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 as 'AssetTag', PC_BIOS_DATA.SerialNumber00 as 'SerialNumber', VRS.Creation_Date0 as 'ClientCreationDate',ĭateDiff(D, VRS.Creation_Date0, GetDate()) 'ClientCreationDateAge',ĭbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS 'OSInstallDate',ĭateDiff(D, v_GS_OPERATING_SYSTEM.InstallDate0, GetDate()) 'OSInstallDateAge',Ĭonvert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100) as 'LastBootDate',ĭateDiff(D, Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100), GetDate()) as 'LastBootDateAge', V_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as 'AssignedSite', MAX (v_GS_NETWORK_ADAPTER_CONFIGUR.MACAddress0) as 'MACAddress', ISNULL(NULLIF(CHARINDEX(',',MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0)) - 1, -ġ),LEN(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0))))as 'IPAddress', LEFT(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0), When v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 24 Then 'Sealed-Case PC' When v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 23 Then 'Rack Mount Chassis' When v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 22 Then 'Storage Chassis' When v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 21 Then 'Peripheral Chassis' When v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 17 Then 'Main System Chassis' When v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 Then 'Space-Saving' When v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 13 Then 'All in One' When v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 7 Then 'Tower' When v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 Then 'Mini Tower' When v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN('3','4')Then 'Desktop' This should give you all the info for your Dell Computersĭistinct (VRS.Netbios_Name0) as 'Computer Name',Ĭase when VRS.Client0 = 1 Then 'Yes' Else 'No' End 'Client',Ĭase when VRS.Active0 = 1 Then 'Yes' Else 'No' End 'Active',Ĭase when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 1 Then 'VMWare' Know this is an old post, but its good for anyone looking for a SQL query
0 kommentar(er)
