Variable Report

[mike]

Hello,

Can someone assist me with modifing SCCM Report ID 66? The statement for Report ID 66 is:

Select SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion

FROM v_GS_SoftwareProduct SP

JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID

WHERE SP.ProductName = @variable AND SP.ProductVersion = @variable1

Order by SP.ProductName, SP.ProductVersion

 

I want to add the computer's serial number into this statement. This is what I have:

SELECT

v_R_System.Netbios_Name0,

v_GS_PC_BIOS.SerialNumber0,

v_R_System.User_Name0,

v_SoftwareProduct.ProductName,

v_SoftwareProduct.ProductVersion,

v_SoftwareProduct.CompanyName

FROM

v_R_System,

v_GS_PC_BIOS,

v_SoftwareProduct

JOIN I get errors here as well becuase of the "="

v_R_System.ResourceID = v_GS_SoftwareProduct.ResourceID AND

v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID AND

v_GS_SoftwareProduct.ProductID = v_SoftwareProduct.ProductID

WHERE

v_SoftwareProduct.ProductName = @variable (I get Errors here because of the '@' sign)

ORDER BY

v_SoftwareProduct.ProductName, v_SoftwareProduct.ProductVersion

 

Can someone help out on why my errors are occurring? I have also trired different JOIN and FROM variations, but always encounter errors.

 

Thanks,

Mike

 

I made changes afer reviewing the above statement:

SELECT

v_R_System.Netbios_Name0,

v_GS_PC_BIOS.SerialNumber0,

v_R_System.User_Name0,

v_GS_SoftwareProduct.ProductName,

v_GS_SoftwareProduct.ProductVersion,

v_GS_SoftwareProduct.CompanyName

FROM

v_R_System,

v_GS_PC_BIOS,

v_GS_SoftwareProduct

JOIN v_R_System ON

v_R_System.ResourceID = v_GS_SoftwareProduct.ResourceID AND v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID

WHERE

v_GS_SoftwareProduct.ProductName = @variable

ORDER BY

v_GS_SoftwareProduct.ProductName, v_GS_SoftwareProduct.ProductVersion

 

But now I get the following error:

An error occurred when the report was run. The details are as follows:

The multi-part identifier "v_GS_PC_BIOS.ResourceID" could not be bound.

Error Number: -2147217900

Source: Microsoft OLE DB Provider for SQL Server

Native Error: 4104

[mike]

Hello,

Can someone assist me with modifing SCCM Report ID 66? The statement for Report ID 66 is:

Select SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion

FROM v_GS_SoftwareProduct SP

JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID

WHERE SP.ProductName = @variable AND SP.ProductVersion = @variable1

Order by SP.ProductName, SP.ProductVersion

 

I want to add the computer's serial number into this statement. This is what I have:

SELECT

v_R_System.Netbios_Name0,

v_GS_PC_BIOS.SerialNumber0,

v_R_System.User_Name0,

v_SoftwareProduct.ProductName,

v_SoftwareProduct.ProductVersion,

v_SoftwareProduct.CompanyName

FROM

v_R_System,

v_GS_PC_BIOS,

v_SoftwareProduct

JOIN I get errors here as well becuase of the "="

v_R_System.ResourceID = v_GS_SoftwareProduct.ResourceID AND

v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID AND

v_GS_SoftwareProduct.ProductID = v_SoftwareProduct.ProductID

WHERE

v_SoftwareProduct.ProductName = @variable (I get Errors here because of the '@' sign)

ORDER BY

v_SoftwareProduct.ProductName, v_SoftwareProduct.ProductVersion

 

Can someone help out on why my errors are occurring? I have also trired different JOIN and FROM variations, but always encounter errors.

 

Thanks,

Mike

 

I made changes afer reviewing the above statement:

SELECT

v_R_System.Netbios_Name0,

v_GS_PC_BIOS.SerialNumber0,

v_R_System.User_Name0,

v_GS_SoftwareProduct.ProductName,

v_GS_SoftwareProduct.ProductVersion,

v_GS_SoftwareProduct.CompanyName

FROM

v_R_System,

v_GS_PC_BIOS,

v_GS_SoftwareProduct

JOIN v_R_System ON

v_R_System.ResourceID = v_GS_SoftwareProduct.ResourceID AND v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID

WHERE

v_GS_SoftwareProduct.ProductName = @variable

ORDER BY

v_GS_SoftwareProduct.ProductName, v_GS_SoftwareProduct.ProductVersion

 

But now I get the following error:

An error occurred when the report was run. The details are as follows:

The multi-part identifier "v_GS_PC_BIOS.ResourceID" could not be bound.

Error Number: -2147217900

Source: Microsoft OLE DB Provider for SQL Server

Native Error: 4104

 

 

Finally got, I day and half later!

SELECT

v_R_System.Netbios_Name0,

v_GS_PC_BIOS.SerialNumber0,

v_R_System.User_Name0,

v_GS_SoftwareProduct.ProductName,

v_GS_SoftwareProduct.ProductVersion,

v_GS_SoftwareProduct.CompanyName

FROM v_GS_PC_BIOS JOIN v_GS_SoftwareProduct ON

v_GS_PC_BIOS.ResourceID = v_GS_SoftwareProduct.ResourceID

JOIN v_R_System ON

v_GS_SoftwareProduct.ResourceID = v_R_System.ResourceID

WHERE

v_GS_SoftwareProduct.ProductName = @variable

ORDER BY

v_GS_SoftwareProduct.ProductName, v_GS_SoftwareProduct.ProductVersion