Specific Computer Model per Location

[Briandr88]

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?

 

[Migration User]

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%'

 

[Briandr88]

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.

 

[Migration User]

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.  

[Briandr88]

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