SCCM MAC address report

[kesh]

I have created a query in SCCM to report on MAC Address, IP and and machine name as per query below:. My issue is that when i select the colelction I want to view, it displays everything in my organisation adn not just on the collection i selected. Please advise how I can get the report to only display as per collection required.

SELECT v_RA_System_ResourceNames.Resource_Names0 AS [Resource name], v_RA_System_MACAddresses.MAC_Addresses0 AS [MAC Address],

v_RA_System_IPAddresses.IP_Addresses0 AS [iP Address]
FROM v_RA_System_MACAddresses INNER JOIN
v_RA_System_ResourceNames ON v_RA_System_MACAddresses.ResourceID = v_RA_System_ResourceNames.ResourceID INNER JOIN
v_RA_System_IPAddresses ON v_RA_System_MACAddresses.ResourceID = v_RA_System_IPAddresses.ResourceID

[GarthMJ]

Your query is not written to limit the results to a collection. This sample query will get you started. http://sccmug.ca/2014/01/13/limiting-a-report-to-a-collection/

 

BTW, you will get interesting results with that query. aka you will get more than 1 IP address for a computer. Why not look at the built-in reports for this?

[kesh]

yes, i know....it duplicates alot of stuff

 

Which built in reports can I use? I could not find anything that would give me machine name, mac and IP address

[kesh]

But what I actually nede is a query were I specify why collection I want from from the drop down box.

[GarthMJ]

yes, i know....it duplicates alot of stuff

 

Which built in reports can I use? I could not find anything that would give me machine name, mac and IP address

 

No only will it duplication the data, it will give you old data.

 

There are two network folders.. try them.

[GarthMJ]

But what I actually nede is a query were I specify why collection I want from from the drop down box.

 

Because you didn't tell the query to limit to a collection. Look at my blog post link as your starting point.

[kesh]

Looking at it now

[kesh]

I get this error when using the query from your blog:

 

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.v_Collection'.

[GarthMJ]

Did you change the database from Master to you CM database? Look at this blog post. https://www.enhansoft.com/blog/invalid-object-name-in-sql-server-management-studio-ssms