Client Management Suite

Hi,

This query compiles, but runs without returning any results. The fact that it compiled clean I would think is a good sign. But can someone correct any problems with it? Thanks.

 

SELECT     vComputer.Name AS 'Computer Name', vComputer.[IP Address], vComputer.[OS Name], vComputer.[OS Revision], vHWComputerSystem.Manufacturer,
                      vHWComputerSystem.Model, vHWComputerSystem.[Identifying Number] AS 'Serial Number', vHWProcessor.Model AS 'Processor',
                      vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)', 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
                      vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid

WHERE     (vComputer.IsManaged = 1) AND (vHWComputerSystem.Model LIKE '%nc6220%') OR (vHWComputerSystem.Model LIKE '%nc6320%') OR (vHWComputerSystem.Model LIKE '%6510b%') OR (vHWComputerSystem.Model LIKE '%6530b%') OR (vHWComputerSystem.Model LIKE '%6455b%') OR (vHWComputerSystem.Model LIKE '%nc6000%') OR (vHWComputerSystem.Model LIKE '%6465b%') OR (vHWComputerSystem.Model LIKE '%2510p%') OR (vHWComputerSystem.Model LIKE '%2530p%') OR (vHWComputerSystem.Model LIKE '%2540p%') OR (vHWComputerSystem.Model LIKE '%2560p%' OR vHWComputerSystem.Model LIKE '%2570p%' OR vHWComputerSystem.Model LIKE '%6475b%'  OR vHWComputerSystem.Model LIKE '%9470m%')

and vComputer.Name NOT IN (select vComputer.Name FROM vComputer
join Inv_AddRemoveProgram arp on vComputer.Guid=arp._ResourceGuid
where arp.DisplayName like '%PGP Desktop%')

ORDER BY 'Computer Name'

 

Hi,

query runs well.

I guess there is an logical error with the last condition. What is your purpose? You want to have all computers that don't have pgp desktop installed?

 

Regards

Hi Briandra88, Please tell me which tab you won't so I can create a script.

try this query, SELECT dbo.Inv_AeX_AC_Identification.[Name][Host Name] , dbo.Inv_AeX_AC_Identification.[Os Name] , dbo.Inv_AeX_AC_Identification.[System Type] , dbo.Inv_AeX_AC_Identification.[Last Logon User] , dbo.vHWComputerSystem.[manufacturer][CPU Make] , dbo.vHWComputerSystem.[Model][CPU Model] , dbo.Inv_AeX_AC_TCPIP.[MAC Address] , dbo.Inv_AeX_AC_TCPIP.[IP Address] , dbo.Inv_HW_Baseboard.[Serial Number][Motherboard Serial Number] , dbo.Inv_HW_Physical_Memory.[Capacity (Bytes)][Memory Size (Bytes)] , dbo.Inv_HW_Processor.[Max Clock Speed (Mega-hertz)][Processor Speed (Mega-hertz)] FROM dbo.Inv_AeX_AC_Identification, dbo.vHWComputerSystem, dbo.Inv_AeX_AC_TCPIP, dbo.Inv_HW_Baseboard, dbo.Inv_HW_Physical_Memory, dbo.vHWProcessor, dbo.Inv_HW_Processor WHERE dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.vHWComputerSystem._ResourceGuid AND dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.Inv_AeX_AC_TCPIP._ResourceGuid AND dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.Inv_HW_Baseboard._ResourceGuid AND dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.Inv_HW_Physical_Memory._ResourceGuid

Yes, I want to have all computers that don't have pgp desktop installed.

Sachin,

It appears your re-writing this query to add in fields I don't need to. I need this to be as close as the one I posted originally.

 

 

 

 

Try this:

SELECT

vComputer.Name AS 'Computer Name',

vComputer.[IP Address],

vComputer.[OS Name],

vComputer.[OS Revision],

vHWComputerSystem.Manufacturer,

vHWComputerSystem.Model,

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

vHWProcessor.Model AS 'Processor',

vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)',

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 vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid

WHERE

(vComputer.IsManaged = 1)

AND (vHWComputerSystem.Model LIKE '%nc6220%'

OR vHWComputerSystem.Model LIKE '%nc6320%'

OR vHWComputerSystem.Model LIKE '%6510b%'

OR vHWComputerSystem.Model LIKE '%6530b%'

OR vHWComputerSystem.Model LIKE '%6455b%'

OR vHWComputerSystem.Model LIKE '%nc6000%'

OR vHWComputerSystem.Model LIKE '%6465b%'

OR vHWComputerSystem.Model LIKE '%2510p%'

OR vHWComputerSystem.Model LIKE '%2530p%'

OR vHWComputerSystem.Model LIKE '%2540p%'

OR vHWComputerSystem.Model LIKE '%2560p%'

OR vHWComputerSystem.Model LIKE '%2570p%'

OR vHWComputerSystem.Model LIKE '%6475b%'

OR vHWComputerSystem.Model LIKE '%9470m%')

and vComputer.Name NOT IN (select vComputer.Name FROM vComputer

join Inv_AddRemoveProgram arp on vComputer.Guid=arp._ResourceGuid

where arp.DisplayName like '%PGP Desktop%')

ORDER BY 'Computer Name'

I like to find a "standard" formatting for SQL queries... one that looks right to my eye.  When I copied your query into my standard it immediately jumped out to me that not all of your OR statements for the HWComputerSystem were not captured in the same parenthesis.  I removed a bunch of your parens and the query completes almost immediately and returns data.

Hi Hightower,

 

I wrestled with the NOT IN part of this most of this past weekend. As luck would have it and yet again I get burned by a parenthesis. Only wish I heard from you soooner.

Thank you for your help though. If your romaning the boards could you help add in the join I would need to get system type?

 

"Chassis Type" is under vHWComputerChassisType.[Chassis Type].  I added that to your query:

SELECT

vComputer.Name AS 'Computer Name',

vComputer.[IP Address],

vComputer.[OS Name],

vComputer.[OS Revision],

vHWComputerSystem.Manufacturer,

vHWComputerSystem.Model,

vHWChassisType.[Chassis Type],

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

vHWProcessor.Model AS 'Processor',

vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)',

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 vHWChassisType ON vComputer.Guid = vHWChassisType._ResourceGuid LEFT OUTER

JOIN vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid

WHERE

(vComputer.IsManaged = 1)

AND (vHWComputerSystem.Model LIKE '%nc6220%'

OR vHWComputerSystem.Model LIKE '%nc6320%'

OR vHWComputerSystem.Model LIKE '%6510b%'

OR vHWComputerSystem.Model LIKE '%6530b%'

OR vHWComputerSystem.Model LIKE '%6455b%'

OR vHWComputerSystem.Model LIKE '%nc6000%'

OR vHWComputerSystem.Model LIKE '%6465b%'

OR vHWComputerSystem.Model LIKE '%2510p%'

OR vHWComputerSystem.Model LIKE '%2530p%'

OR vHWComputerSystem.Model LIKE '%2540p%'

OR vHWComputerSystem.Model LIKE '%2560p%'

OR vHWComputerSystem.Model LIKE '%2570p%'

OR vHWComputerSystem.Model LIKE '%6475b%'

OR vHWComputerSystem.Model LIKE '%9470m%')

and vComputer.Name NOT IN (select vComputer.Name FROM vComputer

join Inv_AddRemoveProgram arp on vComputer.Guid=arp._ResourceGuid

where arp.DisplayName like '%PGP Desktop%')

ORDER BY 'Computer Name'

Hi,

It does not seemn to like this:

 

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

 

We have NS 7.1

 

 

Does that View exist in your database? 

I wrote this for 7.1 SP2 MP1.  If that view doesn't exist I'm not sure what I'd need to alter the query.  Sorry.

This works, but I need to see System Type (32-bit system or 64 bit system). I don't think we want to be looking at a HW table, right??

 

SELECT

    vComputer.Name AS 'Computer Name',

    vComputer.[IP Address],

    vComputer.[OS Name],

    vComputer.[OS Revision],

    vHWComputerSystem.Manufacturer,

    vHWComputerSystem.Model,

    vHWChassis.[Chassis Package Type],

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

    vHWProcessor.Model AS 'Processor',

    vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)',

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

    OR vHWComputerSystem.Model LIKE '%nc6320%'

    OR vHWComputerSystem.Model LIKE '%6510b%'

    OR vHWComputerSystem.Model LIKE '%6530b%'

    OR vHWComputerSystem.Model LIKE '%6455b%'

    OR vHWComputerSystem.Model LIKE '%nc6000%'

    OR vHWComputerSystem.Model LIKE '%6465b%'

    OR vHWComputerSystem.Model LIKE '%2510p%'

    OR vHWComputerSystem.Model LIKE '%2530p%'

    OR vHWComputerSystem.Model LIKE '%2540p%'

    OR vHWComputerSystem.Model LIKE '%2560p%'

    OR vHWComputerSystem.Model LIKE '%2570p%'

    OR vHWComputerSystem.Model LIKE '%6475b%'

    OR vHWComputerSystem.Model LIKE '%9470m%')

    and vComputer.Name NOT IN (select vComputer.Name FROM vComputer

    join Inv_AddRemoveProgram arp on vComputer.Guid=arp._ResourceGuid

    where arp.DisplayName like '%PGP Desktop%')

    ORDER BY 'Computer Name'
 

You get OS Bit from vComputer.[System Type].  It will give you Win32 or Win64.

 

(Sorry, I originally thought that by "System Type" you wanted notebook, desktop, etc.)