Report: General Computer Info based on SN

[JKFischer]

I have a querie that I would like to turn into a report, but I am having trouple changing the pop-up into a report prompt. Any help would be greatly appreciated. Here is the code used for the querie.

 

select distinct SMS_R_System.NetbiosName, SMS_R_System.LastLogonUserName, SMS_R_System.IPAddresses, SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_COMPUTER_SYSTEM.Model from SMS_R_System inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PC_BIOS.SerialNumber = ##PRM:SMS_G_System_PC_BIOS.SerialNumber##

[JKFischer]

Got a reply from another location and thought I would post it here.

 

MAIN SQL QUERY:

 

select distinct v_R_System.Netbios_Name0, v_R_System.User_Name0, v_RA_System_IPAddresses.IP_Addresses0, v_R_System.Operating_System_Name_and0, v_GS_COMPUTER_SYSTEM.Model0 from v_R_System inner join v_GS_PC_BIOS on v_GS_PC_BIOS.ResourceID = v_R_System.ResourceId inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId inner join v_RA_System_IPAddresses on v_RA_System_IPAddresses.ResourceID = v_R_System.ResourceID where v_GS_PC_BIOS.SerialNumber0 = @SerialNumber

 

PROMPT

Name: SerialNumber

Text: Please Enter the Serial Number of the machine:

 

SQL STATEMENT

begin

if (@__filterwildcard = '')

SELECT DISTINCT SerialNumber0 FROM v_GS_PC_BIOS with (NOLOCK) ORDER BY SerialNumber0

else

SELECT DISTINCT SerialNumber0 FROM v_GS_PC_BIOS with (NOLOCK) WHERE

SerialNumber0 like @__filterwildcard ORDER BY SerialNumber0

end

 

Hope this helps someone.

[diablo]

nice query .. thankx...

[yyyyuuuu386]

thanks for your post .

I have a question :

v_R_System.Operating_System_Name_and0 value is like "Microsoft Windows NT Workstation 5.1 " , what do I do to show like "windows xp"

 

wish to get reply

[wmmayms]

You could use "select case" or you could join the view v_gs_operatingsystem (note im offline atm so name might not be 100%)

[yyyyuuuu386]

get it

thanks for your reply