SMS 2K3 Report.. help please...

[MexiCant]

Hello all, I'm trying to modify the canned report "Computers with a specific operating system and service pack". It works fine but I'd also like to get IP info on the machines. Here's the code :

Select SYS.Netbios_Name0, OPSYS.Caption0 as C054, OPSYS.Version0, OPSYS.CSDVersion0, OPSYS.InstallDate0, OPSYS.LastBootUpTime0, OPSYS.WindowsDirectory0, OPSYS.Description0
from v_R_System SYS
join v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
where OPSYS.Caption0 like @os and IsNULL(OPSYS.CSDVersion0,'') like @sp
order by SYS.Netbios_Name0

I have IP code but I just can't seem to integrate it into the code above. Any assistance would be greatly appreciated.

Thanks in advance.

 

[GSC]

Hopefully this Query will help you get the info that you want. I don't have a "report version" on it but you'll at least get the info you want to have and a bit more:

select distinct SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_G_System_OPERATING_SYSTEM.CSDVersion, SMS_G_System_SYSTEM_ENCLOSURE.SerialNumber, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_G_System_PROCESSOR.CurrentClockSpeed, SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory, SMS_R_System.LastLogonUserName, SMS_R_System.IPAddresses, SMS_R_System.MACAddresses from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PROCESSOR on SMS_G_System_PROCESSOR.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_X86_PC_MEMORY on SMS_G_System_X86_PC_MEMORY.ResourceID = SMS_R_System.ResourceId



I hope you find this post helpful.

Regards,
GSC

 

[MexiCant]

Thanks.. but my query works fine.. I just don't have the know how to convert it to a SQL statement... I'm sure someone else will know.

Take care.