Client Management Suite

Hi,

I just want to tweak the canned Count of Computers by Manufacturer and Model report so I see only Windows XP systems.

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'

DECLARE @manufacturer varchar(100)
DECLARE @model varchar(100)

SET @manufacturer = '%Level1%'
SET @model = '%Level2%'


IF (@manufacturer = 'None')
BEGIN
IF (@model = 'None')
BEGIN
SELECT '' [Level1],
'' [Level2],
0 [Number of Computers]
END

ELSE
BEGIN
SELECT '' [Level1],
c.Model [Level2],
COUNT(DISTINCT CAST(i.Guid as varchar(40))) [Number of Computers]
FROM dbo.vComputer i
JOIN dbo.ScopeMembership sm
ON sm.[ResourceGuid] = i.Guid
AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
JOIN dbo.vHWComputerSystem c
ON c.[_ResourceGuid] = i.Guid
JOIN dbo.CollectionMembership cm
ON cm.ResourceGuid = i.[Guid]
JOIN dbo.vCollection it
ON it.Guid = cm.CollectionGuid
WHERE 1=1
AND ISNULL (c.Manufacturer,'') <> ''
AND ISNULL (c.Model,'') <> ''
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')
AND i.Domain LIKE '%Domain%'
AND lower(it.[Guid]) LIKE lower('%Filter%')
AND vComputer.[OS Name] like 'Microsoft Windows XP'
GROUP BY c.Model
ORDER BY
COUNT(DISTINCT CAST(i.Guid as varchar(40))) DESC
END
END
ELSE
BEGIN
IF (@model = 'None')
BEGIN
SELECT c.Manufacturer [Level1],
'' [Level2],
COUNT(DISTINCT CAST(i.Guid as varchar(40))) [Number of Computers]
FROM dbo.vComputer i
JOIN dbo.ScopeMembership sm
ON sm.[ResourceGuid] = i.Guid
AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
JOIN dbo.vHWComputerSystem c
ON c.[_ResourceGuid] = i.Guid
JOIN dbo.CollectionMembership cm
ON cm.ResourceGuid = i.[Guid]
JOIN dbo.vCollection it
ON it.Guid = cm.CollectionGuid
WHERE 1=1
AND ISNULL (c.Manufacturer,'') <> ''
AND ISNULL (c.Model,'') <> ''
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')
AND i.Domain LIKE '%Domain%'
AND lower(it.[Guid]) LIKE lower('%Filter%')
AND vComputer.[OS Name] like 'Microsoft Windows XP'
GROUP BY c.Manufacturer
ORDER BY
COUNT(DISTINCT CAST(i.Guid as varchar(40))) DESC
END
ELSE
BEGIN
SELECT c.Manufacturer [Level1],
c.Model [Level2],
COUNT(DISTINCT CAST(i.Guid as varchar(40))) [Number of Computers]
FROM dbo.vComputer i
JOIN dbo.ScopeMembership sm
ON sm.[ResourceGuid] = i.Guid
AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
JOIN dbo.vHWComputerSystem c
ON c.[_ResourceGuid] = i.Guid
JOIN dbo.CollectionMembership cm
ON cm.ResourceGuid = i.[Guid]
JOIN dbo.vCollection it
ON it.Guid = cm.CollectionGuid
WHERE 1=1
AND ISNULL (c.Manufacturer,'') <> ''
AND ISNULL (c.Model,'') <> ''
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')
AND i.Domain LIKE '%Domain%'
AND lower(it.[Guid]) LIKE lower('%Filter%')
AND vComputer.[OS Name] like 'Microsoft Windows XP'
GROUP BY
c.Manufacturer,
c.Model
ORDER BY
COUNT(DISTINCT CAST(i.Guid as varchar(40))) DESC
END
END

When the query runs I get a 'Datasource is not in a runnable state' error. Can someone help with fixing this query? Thanks.

You aliased vcomputer as "i".

In your query you'll need to replace vcomputer.[OS Name] with i.[OS Name]

Thanks. Do you know how I could tweak this so under Level 2 I would not have multiple models that are slightly different. Here is an example:

HP Compaq 6510b (GF918AT#ABA)

HP Compaq 6510b (RM332UT#ABA)

I am just looking for an overall tally count per model. With the way this canned report is if the model name is slightly different then you end up with multiple entries for the same one. I think this tweak can be implemented and I seem to recall seeing it before. Just not sure how the code needs to be adjusted.

That information is coming from the computer's BIOS.  Manipulation would have to happen on the SQL side to truncate the right-hand x# of characters if the value is like something.

That's a little beyond what I'm used to doing but maybe someone else can chime in here.