Hi,
This article is in response to a post regarding a report that shows the software usage for ALL software components. As opposed to using a drop down to select each one - link to article.
The underlying SQL wtih regards to actually gathering the required components has been completed by both Symantec and "mlombardo" (link to original article).
Description of changes made
In brief, my changes were to simply create a mechanism for passing through a concatenated string of component guids into the underlying Stored Procedures / Table Functions, where it is split and turned into a querable table.
The steps below detail how the new functionality works, should you be required to change / augment it.
- Declare a new variable called @container that will be used to store the concatenated string of guids
DECLARE @container varchar(max)
- Declare a Cursor that will run the SQL to collect the component guids and concatenate
DECLARE c CURSOR FOR
The code below is the start of the function required to concatenate the guids, note STUFF and the CASTING of the GUID field.
select (stuff((select ','+ cast(itm.Guid as varchar(max)) as [text()]
The code below is what you will need to change if you want to change which component guids are returned.
from dbo.vItem itm
JOIN dbo.Inv_Software_Product_Usage SPU ON spu._ResourceGuid = itm.Guid
WHERE
itm.ClassGuid = 'AA34B7C1-1C65-4085-A9D1-E7F83D633B2F'
AND SPU.IsUsageTracked = 1
--AND (itm.Guid IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
Order by itm.Name
The code below is the final part of the code used to concatenate the values. I'm using the FOR XML PATH option as it was the simplest what I could do it.
FOR XML PATH('')
),1,1,'')) AS concatString;
The code below what tells the cursor to go and fetch the 1 and only row from the query and store it in the @container variable. It'll run once because there are no other values to grab.
OPEN c;
FETCH NEXT FROM c INTO @container;
WHILE @@FETCH_STATUS=0 BEGIN
- Change the Input of the StoredProcedure to reference the @container value used in the cursor
The code below is used to pass the required values, including the concatenated guids into the Stored Procedures required to gather the ProductUsage
INSERT @ProductUsage
EXECUTE spAC_GetComputersBySoftwareProduct @ComputerName=N'%%'
, @SelectTop=1
, @Trustee=N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{402DC8EC-CC6C-4D4B-B389-F12AE18CB02F},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{9791A53A-6F81-4701-B878-E68C4C540057},{A8508E1D-C6A5-4F9B-A1F4-3D401C44564D},{AE41E526-1963-4C40-A456-A67E845F4D19},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}'
, @SoftwareComponentGuids=@container
- Change the where clause on the main sql query to bring back all guids
where itm.Guid like '%' and itm.ClassGuid = 'AA34B7C1-1C65-4085-A9D1-E7F83D633B2F'
- Close out the Cursor and End
FETCH NEXT FROM c INTO @container
END
CLOSE c;
DEALLOCATE c;
If you want to run this in SQL then no changes are required.
If you want to run this as an SMP report you may want to uncomment the following lines:
--DECLARE @v1_TrusteeScope nvarchar(max)
-- SET @v1_TrusteeScope = N'%TrusteeScope%'
--AND (itm.Guid IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
Any questions, please let me know.
Thanks,
Kevin