CPU.Family not showing correctly
I only added one line "i.[User] [User Name]," to the original Computer Summary Report and the CPU.FAMILY [CPU TYPE] column does not show correct values.
What's wrong?
Also, how do I add a column called "filters" where it lists the department which the computers belonging to?
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT DISTINCT i.Guid,
CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name])) ELSE i.[Name] END [Name],
i.[User] [User Name],
CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN REPLACE (i.[Name],SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name]))+ '.','') ELSE i.Domain END [Domain],
sn1.[Manufacturer] [Manufacturer],
sn1.[Model] [Computer Model],
sn1.[Total Physical Memory (Bytes)] / (1024 * 1024) [Memory (MB)],
cpu.Family [CPU Type],
ISNULL (i.[OS Name],os.[Name]) [Operating System],
d.[OS revision] [SP],
CST.[Max Media Size (Kilobytes)] / (1024) [Disk Size(MB)],
ld.[Free Space (Bytes)] [Free Space(MB)],
sn1.[Identifying Number] [Serial Number]
FROM dbo.vComputer i
JOIN dbo.ScopeMembership sm
ON sm.[ResourceGuid] = i.Guid
AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
LEFT JOIN dbo.Inv_AeX_AC_Identification d
ON d._ResourceGuid = i.Guid
JOIN dbo.CollectionMembership cm
ON cm.ResourceGuid = i.[Guid]
JOIN dbo.vCollection it
ON it.Guid = cm.CollectionGuid
LEFT JOIN dbo.vHWPhysicalMemory m1
ON m1.[_ResourceGuid] = i.Guid
JOIN dbo.vHWComputerSystem sn1
ON sn1.[_ResourceGuid] = i.Guid
LEFT JOIN dbo.Inv_HW_Processor cpu
ON cpu.[_ResourceGuid] = i.[Guid]
LEFT JOIN (SELECT SUM ([Free Space (Bytes)] / (1024 * 1024)) [Free Space (Bytes)], _ResourceGuid
FROM dbo.vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid) ld
ON i.Guid = ld._ResourceGuid
LEFT JOIN dbo.vOSOperatingSystem os
ON os._ResourceGuid = i.Guid
JOIN (SELECT SUM ([Max Media Size (Kilobytes)]) [Max Media Size (Kilobytes)],
_ResourceGuid FROM vHWStorage
WHERE [Media Type] = 29
GROUP BY _ResourceGuid)CST
ON CST._ResourceGuid = i.Guid
WHERE 1 = 1
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')
AND i.Domain LIKE '%Domain%'
AND lower (it.[Guid]) LIKE lower ('%Filter%')
Comments
Perhaps this will help
First off, what version of the software are you using? How exactly is the [CPU Type] not showing up as expected?
Looking at the description of the vComputer view, I don't see any reason why adding the [User] column would change your results. That view should only have one user per computer, based on the top 1 result in the Primary User data.
As far as the Inv_HW_Processor data goes, Inventory obtains that information from WMI. First the inventory agent queries the processors from Win32_Processor. After which it iterates through each one, selecting the properties in Win32_Processor for each.
You can see the information in WMI and compare it to the inventory results if you like. To do so, just use the wbemtest tool provided by microsoft (Start > Run > 'wbemtest'). With wbemtest open, click the 'Connect...' button. A connection window will open up. You need to specify the root\cimv2 namespace. If you are connecting to a remote computer then use \\COMPUTERNAME\root\cimv2. You also need to ensure you have permissions and that ports 135 and 445 are open for connecting remotely.
Once connected, click the 'Query...' button and enter in the following:
SELECT * FROM Win32_Processor
After applying the query, the results will show up. If there are multiple processors then there should be multiple results (but not with multiple cores). Double-click on one of the rows to get into the details for that class. In the new window (Object Editor), you will see a Properties pane in the middle. Scroll through the properties until you get to the 'Family' property. If the value for Family is different than what inventory is reporting then I'd say there is an issue. If not, then everything is behaving as designed.
Perhaps you are looking for the processor name? Try getting it from Inv_HW_Processor_Name_Windows.
Princ Tech Support Engineer
Symantec
I am using 7.0 Yes, I am
I am using 7.0
Yes, I am trying to get the Processor Name and another column called "filter" or "Group" which shows the exact filter each user belongs to.
What should I replace with the below 2 lines?
cpu.Family [CPU Type],
LEFT JOIN dbo.Inv_HW_Processor cpu
ON cpu.[_ResourceGuid] = i.[Guid]
Replace with this
pnw.[Processor Name],
LEFT JOIN dbo.Inv_HW_Processor_Name_Windows pnw
ON pnw.[_ResourceGuid] = i.[Guid]
Princ Tech Support Engineer
Symantec
That works great. How about
That works great.
How about adding a column called "filter" ?
Adding a column for filter
Adding a column for filter may be more complicated than you realize. Consider that a computer belongs to several filters by default. If you simply add a join to the CollectionMembership table on the ResourceGuid column, you will get all the rows for filters that the computer belongs to. This will cause your report results to have what appears to be several duplicate rows where only the filter name is different.
You could address this by constructing a list of filter guids for your department filters. The simplest way would be to create a subfolder under filters strictly for these department filters and then add a subquery in your report SQL that gets all filters in that folder. Then you could limit your results to only those filters. Of course, you would still have to ensure that a computer only belonged to one department filter or you would get duplicate rows again. But lets say you need them to belong to more than one department filter, it is possible to concatenate all the filters into a single string using the COALESCE() function. That is getting into advanced SQL though. I'll only go there if you need me to.
Another method of attacking this would be to use an organizational view for your company structure where all the groups in it are each department. In which case you could query the org group the resource belongs to from the ScopeMembership table, and only for org groups that are a part of your organizational view. If your Active Directory structure contains OUs that match your departments then this will be rather easy. Just perform an AD import for computer resources with the option to create organization groups turned on. That way the membership for all the org groups will match what's in Active Directory and you don't have to directly maintain each department's computer membership. Creating the SQL to query the org group is a little more advanced, but I could help you with that.
Edit:
Almost forgot one other way to approach this. If you have Asset/CMDB installed then we could look at any of the following data:
Tell me which approach you'd like to take and I'll post some examples.
Princ Tech Support Engineer
Symantec
Thanks Tyler. Unfortunately,
Thanks Tyler.
Unfortunately, we don't have Asset/CMDB installed nor our AD contains OUs of each departments.
How do we apply the Organizational View method?
That being the case, it will
That being the case, it will probably take less work to move all the filters you have created into a new folder solely for your 'Department Filters'. Using the Organizational View method would require you to create each group and add the members manually, something you have already done under filters.
So under 'Manage > Filters', right click on the Filters folder at the root level and create a new folder. Call it 'Department Filters' or whatever name you like.
Your next process will be to right-click on each of your filters that represent the departments in your company and select 'Move'. Place them in the new folder.
After that, right-click on the new 'Department Filters' folder and select 'Properties'. Copy the guid (without the braces).
Now, you can add the following SQL to the top of your report SQL. This will create a table variable that holds the guids for all the items located under the specified folder. You need to change the line that reads 'SET @StartFromFolder = ' so that variable is set to the guid of the folder you copied down.
After adding this, you need to change the main query of your report with the following:
In the SELECT section:
In the FROM section:
Princ Tech Support Engineer
Symantec
This DataSource is not in a
This DataSource is not in a runnable state"
DECLARE @FolderChildren TABLE (ItemGuid uniqueidentifier, ParentFolderGuid uniqueidentifier, IsFolder int)
DECLARE @StartFromFolder uniqueidentifier
SET @StartFromFolder = 'd338e322-69df-49b7-b01c-76653f9f0908' --Set to the guid of the folder you are getting all child items from
WHILE @@ROWCOUNT > 0
BEGIN
INSERT INTO @FolderChildren
SELECT i.*
FROM ItemFolder i
LEFT OUTER JOIN @FolderChildren f
ON f.ItemGuid = i.ItemGuid
AND f.ParentFolderGuid = i.ParentFolderGuid
WHERE (i.ParentFolderGuid = @StartFromFolder
OR i.ParentFolderGuid IN (SELECT ItemGuid FROM @FolderChildren))
AND f.ItemGuid IS NULL
END
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT DISTINCT i.Guid,
ci.[Name] [Department Filter],
CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name])) ELSE i.[Name] END [Name],
i.[User] [User Name],
CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN REPLACE (i.[Name],SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name]))+ '.','') ELSE i.Domain END [Domain],
sn1.Manufacturer [Manufacturer],
sn1.[Model] [Computer Model],
pnw.[Processor Name],
sn1.[Total Physical Memory (Bytes)] / (1024 * 1024) [Memory (MB)],
ISNULL (i.[OS Name],os.[Name]) [Operating System],
d.[OS revision] [SP],
CST.[Max Media Size (Kilobytes)] / (1024) [Disk Size(MB)],
ld.[Free Space (Bytes)] [Free Space(MB)],
sn1.[Identifying Number] [Serial Number]
FROM dbo.vComputer i
JOIN dbo.ScopeMembership sm
ON sm.[ResourceGuid] = i.Guid
AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
LEFT JOIN dbo.Inv_AeX_AC_Identification d
ON d._ResourceGuid = i.Guid
LEFT OUTER JOIN CollectionMembership cm
ON cm.ResourceGuid = c.[Guid]
AND cm.CollectionGuid IN (SELECT ItemGuid FROM @FolderChildren)
LEFT OUTER JOIN Item ci
ON ci.[Guid] = cm.CollectionGuid
LEFT JOIN dbo.vHWPhysicalMemory m1
ON m1.[_ResourceGuid] = i.Guid
JOIN dbo.vHWComputerSystem sn1
ON sn1.[_ResourceGuid] = i.Guid
LEFT JOIN dbo.Inv_HW_Processor_Name_Windows pnw
ON pnw.[_ResourceGuid] = i.[Guid]
LEFT JOIN (SELECT SUM ([Free Space (Bytes)] / (1024 * 1024)) [Free Space (Bytes)], _ResourceGuid
FROM dbo.vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid) ld
ON i.Guid = ld._ResourceGuid
LEFT JOIN dbo.vOSOperatingSystem os
ON os._ResourceGuid = i.Guid
JOIN (SELECT SUM ([Max Media Size (Kilobytes)]) [Max Media Size (Kilobytes)],
_ResourceGuid FROM vHWStorage
WHERE [Media Type] = 29
GROUP BY _ResourceGuid)CST
ON CST._ResourceGuid = i.Guid
WHERE 1 = 1
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')
AND i.Domain LIKE '%Domain%'
AND lower (it.[Guid]) LIKE lower ('%Filter%')
Your vComputer view is
Your vComputer view is aliased with 'i', so you will have to change the line:
ON cm.ResourceGuid = c.[Guid]
to:
ON cm.ResourceGuid = i.[Guid]
Additionally, the last line in your query has 'it.[Guid]' in it, but there is not table or subquery aliased with 'it', so the column cannot be bound. Your original query had the following JOIN to vCollection:
JOIN dbo.vCollection it
ON it.Guid = cm.CollectionGuid
When you removed that join it caused that last line to give an error. If you want be able to filter the results to a specific filter then I would change the last line as follows:
AND LOWER(cm.CollectionGuid) LIKE LOWER('%Filter%')
Princ Tech Support Engineer
Symantec
I guess I am still missing
I guess I am still missing something?..
Ok, I misunderstood how you
Ok, I misunderstood how you would be using the Filter parameter. You are going to have to add another join to CollectionMembership to filter the results to computers only belonging to that filter. Leave the first join to CollectionMembership alone as that is pulling the department name if the computer is in one of the child filters of the folder you specified. So here is what you need to add:
INNER JOIN CollectionMembership cm2
ON cm2.ResourceGuid = c.[Guid]
Additionally, you will change the last line to:
AND LOWER(cm2.CollectionGuid) LIKE LOWER('%Filter%')
Princ Tech Support Engineer
Symantec
Error returns..
Error returns..
Paste or attach the entire
Paste or attach the entire SQL and I'll look at it. There's probably a typo somewhere.
Princ Tech Support Engineer
Symantec
Here: DECLARE
Here:
DECLARE @FolderChildren TABLE (ItemGuid uniqueidentifier, ParentFolderGuid uniqueidentifier, IsFolder int)
DECLARE @StartFromFolder uniqueidentifier
SET @StartFromFolder = 'd338e322-69df-49b7-b01c-76653f9f0908' --Set to the guid of the folder you are getting all child items from
WHILE @@ROWCOUNT > 0
BEGIN
INSERT INTO @FolderChildren
SELECT i.*
FROM ItemFolder i
LEFT OUTER JOIN @FolderChildren f
ON f.ItemGuid = i.ItemGuid
AND f.ParentFolderGuid = i.ParentFolderGuid
WHERE (i.ParentFolderGuid = @StartFromFolder
OR i.ParentFolderGuid IN (SELECT ItemGuid FROM @FolderChildren))
AND f.ItemGuid IS NULL
END
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT DISTINCT i.Guid,
ci.[Name] [Department Filter],
CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name])) ELSE i.[Name] END [Name],
i.[User] [User Name],
CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN REPLACE (i.[Name],SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name]))+ '.','') ELSE i.Domain END [Domain],
sn1.Manufacturer [Manufacturer],
sn1.[Model] [Computer Model],
pnw.[Processor Name],
sn1.[Total Physical Memory (Bytes)] / (1024 * 1024) [Memory (MB)],
ISNULL (i.[OS Name],os.[Name]) [Operating System],
d.[OS revision] [SP],
CST.[Max Media Size (Kilobytes)] / (1024) [Disk Size(MB)],
ld.[Free Space (Bytes)] [Free Space(MB)],
sn1.[Identifying Number] [Serial Number]
FROM dbo.vComputer i
JOIN dbo.ScopeMembership sm
ON sm.[ResourceGuid] = i.Guid
AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
LEFT JOIN dbo.Inv_AeX_AC_Identification d
ON d._ResourceGuid = i.Guid
LEFT OUTER JOIN CollectionMembership cm
ON cm.ResourceGuid = i.[Guid]
AND cm.CollectionGuid IN (SELECT ItemGuid FROM @FolderChildren)
LEFT OUTER JOIN Item ci
ON ci.[Guid] = cm.CollectionGuid
LEFT JOIN dbo.vHWPhysicalMemory m1
ON m1.[_ResourceGuid] = i.Guid
JOIN dbo.vHWComputerSystem sn1
ON sn1.[_ResourceGuid] = i.Guid
LEFT JOIN dbo.Inv_HW_Processor_Name_Windows pnw
ON pnw.[_ResourceGuid] = i.[Guid]
LEFT JOIN (SELECT SUM ([Free Space (Bytes)] / (1024 * 1024)) [Free Space (Bytes)], _ResourceGuid
FROM dbo.vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid) ld
ON i.Guid = ld._ResourceGuid
LEFT JOIN dbo.vOSOperatingSystem os
ON os._ResourceGuid = i.Guid
JOIN (SELECT SUM ([Max Media Size (Kilobytes)]) [Max Media Size (Kilobytes)],
_ResourceGuid FROM vHWStorage
WHERE [Media Type] = 29
GROUP BY _ResourceGuid)CST
ON CST._ResourceGuid = i.Guid
WHERE 1 = 1
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')
AND i.Domain LIKE '%Domain%'
AND LOWER(cm.CollectionGuid) LIKE LOWER('%Filter%')
Use this
Use this:
DECLARE @FolderChildren TABLE (ItemGuid uniqueidentifier, ParentFolderGuid uniqueidentifier, IsFolder int)
DECLARE @StartFromFolder uniqueidentifier
SET @StartFromFolder = 'd338e322-69df-49b7-b01c-76653f9f0908' --Set to the guid of the folder you are getting all child items from
WHILE @@ROWCOUNT > 0
BEGIN
INSERT INTO @FolderChildren
SELECT i.*
FROM ItemFolder i
LEFT OUTER JOIN @FolderChildren f
ON f.ItemGuid = i.ItemGuid
AND f.ParentFolderGuid = i.ParentFolderGuid
WHERE (i.ParentFolderGuid = @StartFromFolder
OR i.ParentFolderGuid IN (SELECT ItemGuid FROM @FolderChildren))
AND f.ItemGuid IS NULL
END
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT DISTINCT i.[Guid]
, ci.[Name] [Department Filter]
, CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0
THEN SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name]))
ELSE i.[Name] END [Name]
, i.[User] [User Name]
, CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0
THEN REPLACE (i.[Name],SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name]))+ '.','')
ELSE i.Domain END [Domain]
, sn1.Manufacturer [Manufacturer]
, sn1.[Model] [Computer Model]
, pnw.[Processor Name]
, sn1.[Total Physical Memory (Bytes)] / (1024 * 1024) [Memory (MB)]
, ISNULL (i.[OS Name],os.[Name]) [Operating System]
, d.[OS revision] [SP]
, CST.[Max Media Size (Kilobytes)] / (1024) [Disk Size(MB)]
, ld.[Free Space (Bytes)] [Free Space(MB)]
, sn1.[Identifying Number] [Serial Number]
FROM dbo.vComputer i
INNER JOIN dbo.ScopeMembership sm
ON sm.[ResourceGuid] = i.[Guid]
AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
INNER JOIN CollectionMembership cm
ON cm.ResourceGuid = i.[Guid]
LEFT OUTER JOIN dbo.Inv_AeX_AC_Identification d
ON d._ResourceGuid = i.[Guid]
LEFT OUTER JOIN CollectionMembership cm2
ON cm2.ResourceGuid = i.[Guid]
AND cm2.CollectionGuid IN (SELECT ItemGuid FROM @FolderChildren)
LEFT OUTER JOIN Item ci
ON ci.[Guid] = cm2.CollectionGuid
LEFT OUTER JOIN dbo.vHWPhysicalMemory m1
ON m1.[_ResourceGuid] = i.[Guid]
LEFT OUTER JOIN dbo.vHWComputerSystem sn1
ON sn1.[_ResourceGuid] = i.[Guid]
LEFT OUTER JOIN dbo.Inv_HW_Processor_Name_Windows pnw
ON pnw.[_ResourceGuid] = i.[Guid]
LEFT OUTER JOIN (
SELECT SUM ([Free Space (Bytes)] / (1024 * 1024)) [Free Space (Bytes)]
, _ResourceGuid
FROM dbo.vHWLogicalDisk
WHERE [Logical Disk Type] = 3
GROUP BY _ResourceGuid
) ld
ON i.[Guid] = ld._ResourceGuid
LEFT OUTER JOIN dbo.vOSOperatingSystem os
ON os._ResourceGuid = i.[Guid]
LEFT OUTER JOIN (SELECT SUM ([Max Media Size (Kilobytes)]) [Max Media Size (Kilobytes)]
, _ResourceGuid
FROM vHWStorage
WHERE [Media Type] = 29
GROUP BY _ResourceGuid
) CST
ON CST._ResourceGuid = i.[Guid]
WHERE 1 = 1
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')
AND i.Domain LIKE '%Domain%'
AND LOWER(cm.CollectionGuid) LIKE LOWER('%Filter%')
Princ Tech Support Engineer
Symantec
beautiful. Many Thanks Tyler.
beautiful.
Many Thanks Tyler.
Is it possible that the automated email policy could email me the report in csv or xls format? I could receive the email with all data embedded in the email body but it's better to receive the report in csv so I can sort by the columns I wish to view.
Or is there a way to sort by department before the email is sent?
You can add ORDER BY criteria to the SQL
You can add ORDER BY criteria to the end of the SQL query to sort it. Use either the column name or number. Examples:
ORDER BY ci.[Name] --orders by ci.[Name] (department)
ORDER BY 2, 3 --orders by column 2 and 3
You can specifiy whether to order it ascending or descending using ASC or DESC.
More info at http://msdn.microsoft.com/en-us/library/ms188385.aspx
Princ Tech Support Engineer
Symantec
Works like a charm. Last
Works like a charm.
Last question and this will be perfect, is there a way to tell the automated report to send me the report in CSV format, like an attachment attached within the email?
Unfortunately, the send email
Unfortunately, the send email task only allows you to either send an email containing the results (using the %!Results!% parameter), or a link to the report snapshot (which could then be exported to a spreadsheet). If your report is potentially going to contain a large result set, then it is best to send a link to the report snapshot.
Princ Tech Support Engineer
Symantec
Got the %!results!% and the
Got the %!results!% and the link working.
Is there a way to put today's date on subject line? (ex. Altiris Hardware Inventory Report (%today_date%)) ?
Help
Is there anyway to get this report to show Hostname / IP / MAC / DOMAIN
And all the same results output? I´m trying to build a custom report to show just that and it just won´t work :(
Daniel Lopes de Oliveira
Endpoint Management Architect & Consultant
-----------------------------------------------
MCITP: EA
Would you like to reply?
Login or Register to post your comment.