Help with SQL query for custom Report in SMS

Not familiar with SMS at all, but just looking at your query syntax and the fact that you are querying tables, try this (assuming Netbios_Name0 is the machine):

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 SYS.Netbios_Name0 NOT IN 
(
    Select DISTINCT SYS.Netbios_Name0
    FROM v_R_System SYS  
    INNER JOIN v_GS_SoftwareProduct SP
    on SP.ResourceID = SYS.ResourceID
    WHERE SP.ProductName = @variable
)
Order by SP.ProductName, SP.ProductVersion

Select allOpen in new window

It throws an error "Must declare the scalar variable "@variable"
I tried adding Microsoft .NET Framework 1.1 and it just returns a huge list of software on various machines.

Sorry, make the following mod. This will return just the machines/user name that don't contain what you are looking for. The query as I wrote it will give you a list of all software on all machines that don't have the requested software.

DECLARE @variable VARCHAR(100)
SET @@variable = 'Microsoft .NET Framework 1.1'
 
SELECT DISTINCT SYS.Netbios_Name0, SYS.User_Name0
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID
WHERE SYS.Netbios_Name0 NOT IN 
(
    Select DISTINCT SYS.Netbios_Name0
    FROM v_R_System SYS  
    INNER JOIN v_GS_SoftwareProduct SP
    on SP.ResourceID = SYS.ResourceID
    WHERE SP.ProductName = @variable
)
Order by SP.ProductName, SP.ProductVersion

Select allOpen in new window

I get "Must declare the scalar variable "@@variable"

Try removing the first two lines and replace @variable in line 13 with ''Microsoft .NET Framework 1.1" or whatever program you are looking for. I'm a SQL Server developer and not certain about the exact syntax that SMS is looking for.

Okay, tried what you said, the report was created successfully.
However, when I run it, I receive the following:
An error occurred when the report was run. The details are as follows:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 145

Remove the order by clause. It doesn't make sense to have it when you are only listing machines. I left it in only because I copied your original code. Sorry I missed it.

This returns a list that looks like all our whole AD...my machine name is on the list and I know I have .NET installed.  Thanks for helping with this...not sure what is wrong.

We're sure this report would give us who DOESN'T have it?

In the code below, the subquery is selecting all machines that have a product named 'Microsoft .NET Framework 1.1'. The outer query is saying give me all machines that are not in the set of machines that have 'Microsoft .NET Framework 1.1' as a product. It is a pretty clear-cut SQL query. I would check and make sure your program name is correct. Query a specific machine you know has the products installed and see what values are returned:

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 SYS.Netbios_Name0 = 'known netbios name'
Order by SP.ProductName, SP.ProductVersion

SELECT DISTINCT SYS.Netbios_Name0, SYS.User_Name0
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID
WHERE SYS.Netbios_Name0 NOT IN 
(
    Select DISTINCT SYS.Netbios_Name0
    FROM v_R_System SYS  
    INNER JOIN v_GS_SoftwareProduct SP
    on SP.ResourceID = SYS.ResourceID
    WHERE SP.ProductName = 'Microsoft .NET Framework 1.1'
)

Select allOpen in new window

Turns out I need the .NET 2.0 report.  I can change the 1.1 to 2.0.
Can we possibly add more code that would narrow it down to product version 2.0.50727.42 or 2.0.50727.832?
These are the values of my product version when I query my machine.

WHERE SP.ProductName = 'Microsoft .NET Framework 2.0' AND SP.ProductVersion IN ('2.0.50727.42', '2.0.50727.832')

or

WHERE SP.ProductName = 'Microsoft .NET Framework 2.0' AND SP.ProductVersion LIKE '2.0.50727.%'

the % is a wild-card character.

This query returns 331 machines - which is basically all of them.  Not sure where to go from here....
appreciate you helping though!

So your entire query is:

SELECT DISTINCT SYS.Netbios_Name0, SYS.User_Name0
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID
WHERE SYS.Netbios_Name0 NOT IN 
(
    Select DISTINCT SYS.Netbios_Name0
    FROM v_R_System SYS  
    INNER JOIN v_GS_SoftwareProduct SP
    on SP.ResourceID = SYS.ResourceID
    WHERE SP.ProductName = 'Microsoft .NET Framework 2.0' AND SP.ProductVersion IN ('2.0.50727.42', '2.0.50727.832')
)

Select allOpen in new window

Here's what I have

SELECT DISTINCT SYS.Netbios_Name0, SYS.User_Name0
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID
WHERE SYS.Netbios_Name0 NOT IN 
(
    Select DISTINCT SYS.Netbios_Name0
    FROM v_R_System SYS  
    INNER JOIN v_GS_SoftwareProduct SP
    on SP.ResourceID = SYS.ResourceID
    WHERE SP.ProductName = 'Microsoft .NET Framework 2.0' AND SP.ProductVersion LIKE '2.0.50727.%'

Select allOpen in new window

Use:
SP.ProductVersion IN ('2.0.50727.42', '2.0.50727.832')

I wonder if the SQL used by SMS doesn't recognize the % as a wildcard, and is thus looking for it as part of the string.

Try the following it works for me.

Cheers

SELECT DISTINCT
	SYS.Netbios_Name0,
	SYS.User_Name0,
FROM v_R_System SYS
WHERE SYS.Netbios_Name0 NOT IN 
(Select SYS.Netbios_Name0
	FROM v_R_System SYS  
	INNER JOIN v_GS_SoftwareProduct SP on SP.ResourceID = SYS.ResourceID
	WHERE SP.ProductName = "Microsoft .NET Framework 2.0" AND SP.ProductVersion LIKE "2.0.50727.%")

Select allOpen in new window

Basically, what matrixnz is saying is to replace the apostrophes (') with double-quotes (")

Actually I believe the error is within the first query
=========================================
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID
=========================================

Both apostrophes and double-quotes will work

NB Error: Need to Remove comma(',') after SYS.User_Name0 before compiling otherwise it will error.

Cheers

Matrix - when entering the code that works for you, I receive:
Incorrect syntax near the keyword 'FROM'

remove the comma after the column name in line 3 of his code.

I removed the column, It's still returning all machines in our AD...

First cloned report shows 421 total systems
Second report gave me 66 systems
The last report (add/remove) gave me 96 systems
Does it sound like the last report is probably my best bet?

So the first report I sent earlier what was that count? did it equal 355 Total Machines?

And yes as I mentioned I'd use Add/Remove Programs over Software Products, it doesn't give you as much freedom with version numbers, the versions don't display minor version information, well not that I could see.

Cheers

Thanks for the points, any reason for the Grade?