Inventory Reports to Mirror Collections

Hi SMS Community

Since Advanced Clients cannot be assigned to secondary sites, the default
"Site Code" and "Site Code" reports are rendered useless

We Have 10 secondary sites (with proxy management point installed in each
2nd site) and only one Primary Site.

So I am wondering, how to tweak the default SMS reports to query by
Collection instead of my site code?

There is a Canned report in this category that matches what I would like to
use:

category - SMS Site - General
"computers in a specific site"

I have clone this report and used the table "V_Collection" to populate the
variable feature, but still recieve error msg:
The column prefix 'SYS' does not match with a table name or alias name used
in the query.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 107

Resident SQL experts, can you point me in the right direction please? :)


here is my code:

SELECT SYS.Netbios_Name0, SYS.User_Domain0, SYS.User_Name0,
ASSG.SMS_Installed_Sites0, SYS.Client_Version0, COLL.Name0
FROM v_R_System SYS, V_Collection COLL
JOIN v_RA_System_SMSInstalledSites as ASSG on SYS.ResourceID=ASSG.ResourceID
WHERE COLL.Name0 LIKE @variable
Order by SYS.Netbios_Name0


and then the linked report
begin
if (@__filterwildcard = '')
select CollectionID, Name from v_Collection order by Name
else
select CollectionID, Name from v_Collection
WHERE Name like @__filterwildcard
order by Name
end


Thanks
David
.

Relevant Pages

  • Re: Inventory Reports to Mirror Collections
    ... Since Advanced Clients cannot be assigned to secondary sites, ... There is a Canned report in this category that matches what I would like to ... Resident SQL experts, can you point me in the right direction please? ... select CollectionID, Name from v_Collection order by Name ...
    (microsoft.public.sms.inventory)
  • Re: Microsoft EA reporting
    ... I created a summary report that works for Access, Office Pro, Office ... CollectionID ... GROUP BY DisplayName0, Publisher0 ...
    (microsoft.public.sms.admin)
  • Re: Inventory Memory Specs
    ... select CollectionID, Name from v_Collection order by Name ... I don't know how many clients you have, ... How do I create a report for physical memory of a collection of PCs? ...
    (microsoft.public.sms.admin)