Video Screencast Help

Specific Computer Model per Location

Created: 14 Oct 2013 • Updated: 15 Oct 2013 | 4 comments
This issue has been solved. See solution.

Hi,

I found a post in the forums that Hightower helped me out with. What I am trying to do is get a report a specific computer model at each defined location. I think with some cleanup this would work.

SELECT

    vComputer.Name AS 'Computer Name',

    vComputer.[OS Name],

    vComputer.[OS Revision],

    vHWComputerSystem.Model,

    vHWChassis.[Chassis Package Type],

    vHWComputerSystem.[Identifying Number] AS 'Serial Number',

    I.Name AS 'Location',

    vComputer.[User] AS 'Primary Owner'

    FROM vComputer LEFT OUTER

    JOIN ResourceAssociation AS loc ON (loc.ChildResourceGuid = vComputer.Guid OR

    loc.ParentResourceGuid = vComputer.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C' LEFT OUTER

    JOIN vItem AS I ON loc.ChildResourceGuid = I.Guid LEFT OUTER

    JOIN vHWProcessor ON vComputer.Guid = vHWProcessor._ResourceGuid LEFT OUTER

    JOIN vHWChassis ON vComputer.Guid = vHWChassis._ResourceGuid LEFT OUTER

    JOIN vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid

    WHERE

    (vComputer.IsManaged = 1)

    AND (vHWComputerSystem.Model LIKE '%8300%')

    ORDER BY 'Computer Name'

 

This code may still require further clean up, but what I am wondering is does anyone have something like a model / location summary report as well?

 

Operating Systems:

Comments 4 CommentsJump to latest comment

kpjernigan's picture

Try this, as long as you have your sites and subnets assigned, this will show you the computers that are 8300's assigned to the site.

------------------------------------------------------------------------------------------

SELECT DISTINCT  
i.Name AS 'Site'
,ip.[Subnet]
,vc.[Name]
,vc.[OS Revision]
,vhw.Model
,vhc.[Chassis Package Type] AS 'Serial Number'
,vhw.[Identifying Number]
,vhw.Manufacturer
,vc.[User] AS 'Primary Owner'
  
FROM 
vsite i  
LEFT JOIN vSiteResource sr on i.Guid=sr.Guid  
LEFT JOIN vSiteSubnetMap ss on sr.Guid=ss._ResourceGuid  
LEFT JOIN vSubnet sn on ss.SubnetGuid=sn.Guid  
LEFT JOIN Inv_AeX_AC_Tcpip tcp on tcp.Subnet=sn.Subnet and tcp.[Subnet Mask]=sn.[Subnet Mask]  
LEFT JOIN vComputer vc on vc.Guid = tcp._resourceGuid 
LEFT JOIN Inv_Aex_AC_TCPIP ip ON ip.[_ResourceGuid] = vc.GUID 
LEFT JOIN vHWComputerSystem vhw ON vc.GUID = vhw.[_ResourceGuid]
LEFT JOIN vHWProcessor vhp ON vc.GUID = vhp.[_ResourceGuid] 
LEFT JOIN vHWChassis vhc ON vc.Guid = vhc._ResourceGuid
LEFT JOIN [CollectionMembership] cm ON cm.[ResourceGuid] = vc.GUID
WHERE vc.IsManaged = '1' AND vhw.Model LIKE '%8300%'

 

-Kev

SOLUTION
Briandr88's picture

Hi,

Very cool. Can this be tweaked to exclude sites with a site server? I only want to know defined sites without a site server.

Thank you.

 

kpjernigan's picture

That should pull up any site, regardless if there is a site server there or not.  Since you can create sites with out assigning a Site Server to them.  

-Kev

Briandr88's picture

I am only looking to see which sites don't have a site server. The sites with site servers I just assume exclude for now. Could I use the code from the site servers canned filter to exclude them somehow?

 
JOIN ResourceAssociation ra on r.Guid=ra.ChildResourceGuid and Ra.ResourceAssociationTypeGuid='5F00E96B-93F3-41f0-94A7-7DBBB8AEF841'
              join vSiteServiceResource ss on ra.ParentResourceGuid=ss.Guid
              where r.IsLocal = 1