Video Screencast Help
Scheduled Maintenance: Symantec Connect is scheduled to be down Saturday, April 19 from 10am to 2pm Pacific Standard Time (GMT: 5pm to 9pm) for server migration and upgrades.
Please accept our apologies in advance for any inconvenience this might cause.

Help with report - Mac Addresses?

Created: 12 Sep 2012 • Updated: 25 Sep 2012 | 10 comments
This issue has been solved. See solution.

Can someone help me to get a report that shows all computers and associated mac addresses with each?  

Thanks

Comments 10 CommentsJump to latest comment

Sally5432's picture

I saw that link but it doesn't really address how to create a report in CMS 7 showing each machine with it's mac addresses.  Thanks anyway.

---
Don't forget to mark posts as helpful if they are, and mark answers as solutions.

markfreeman's picture

This SQl report we use may help you, it will display multiple rows for each computer where there is more than one interface:

SELECT DISTINCT vc.Guid, vc.Name, IP.[IP Address], IP.[MAC Address],

IP.[Subnet Mask], IP.[Default Gateway], vc.[OS Name], vc.[OS Version], va.Status, va.Manufacturer, va.Model, va.[Serial Number]

FROM dbo.Inv_AeX_AC_TCPIP as ip

Left Outer JOIN

dbo.vComputer as vc ON IP._ResourceGuid = vc.Guid INNER JOIN

dbo.vAsset as va ON vc.Guid = va._ResourceGuid

ORDER BY vc.Name, IP.[IP Address]

Sally5432's picture

I did right click on reports, new SQL report and put that in under parameterized query but am getting

"This DataSource is not in a runnable state."

Am I doing something stupid?

---
Don't forget to mark posts as helpful if they are, and mark answers as solutions.

odami's picture

You may have left the security scoping code in the report when you pasted the other report?

 

Try clearing everything in the Parameterized Query box and pasting in this:

 

 

DECLARE @v1_TrusteeScope nvarchar(max)

   SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT DISTINCT vc.Guid AS [_ItemGuid], vc.Name, IP.[IP Address], IP.[MAC Address],

IP.[Subnet Mask], IP.[Default Gateway], vc.[OS Name], vc.[OS Version], va.Status, va.Manufacturer, va.Model, va.[Serial Number]

FROM dbo.Inv_AeX_AC_TCPIP as ip

Left Outer JOIN

dbo.vComputer as vc ON IP._ResourceGuid = vc.Guid INNER JOIN

dbo.vAsset as va ON vc.Guid = va._ResourceGuid

WHERE

   (

      (ip._ResourceGuid IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))

   )

ORDER BY vc.Name, IP.[IP Address]

markfreeman's picture

Clear the paramertized query before pasting the sql code in. The parameters are not required for this report.

Sally5432's picture

@mark and @odami - thanks for the replies, i had cleared the parameterized query completely before pasting anything in.

 

I tried both code suggestions, but both resulted in "This DataSource is not in a runnable state."  

 

 

---
Don't forget to mark posts as helpful if they are, and mark answers as solutions.

andykn101's picture

You haven't got Asset Management Suite installed so no vAsset.

You still get MAC address without those columns, don't forget to add the TrusteeScope as a Query Parameter:

DECLARE @v1_TrusteeScope nvarchar(max)


   SET @v1_TrusteeScope = N'%TrusteeScope%'


SELECT DISTINCT vc.Guid AS [_ItemGuid], vc.Name, IP.[IP Address], IP.[MAC Address],


IP.[Subnet Mask], IP.[Default Gateway], vc.[OS Name], vc.[OS Version]

FROM dbo.Inv_AeX_AC_TCPIP as ip


Left Outer JOIN


dbo.vComputer as vc ON IP._ResourceGuid = vc.Guid

WHERE


   (


      (ip._ResourceGuid IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))


   )


ORDER BY vc.Name, IP.[IP Address]
 

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

SOLUTION
Sally5432's picture

Thanks this worked great!  I wish reporting was more intuitive for people not familiar with the tables, etc.

---
Don't forget to mark posts as helpful if they are, and mark answers as solutions.

andykn101's picture

There's always IT Analytics, a couple of good articles have been posted recently:

http://aka-community.symantec.com/connect/pt-br/ar...

http://www.symantec.com/connect/articles/integrati...

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.