I am in the process of writing a report in 6.5 for our Asset Management team that gives someone the ability to query both the Audit Software and Add Remove Programs tables and return software information as well as hardware information that they need for licensing. (We had these as separate reports previously and they requested we merge them.) The query first creates a hash table to query executables or software and the creates an @SQL object and inserts the correct text into it based on the parameters supplied by the user. It works exactly as I want it to, except it is not applying to the chosen collection, instead applying to everything on the NS. After adding a @Collection object to my declared variables, it errors out. When I run it with the declarted @Collection variable, I'm getting the following error - "Conversion failed when converting from a character string to uniqueidentifier." My problem seems to stem from the fact that I'm trying to access @Collection within the @SQL object as I've tried both CASE and CONVERT to make @Collection a uniqueidentifier.
My report parameters are:
Collection - Item Picker - From CollectionMembership table
Server Name - Basic - From Identification table
Executable Name - Basic - From Audit Software table
Product Name - Basic - Also from Audit Software table
Software Title - Basic - From Add Remove Program table.
Here is the full SQL:
DECLARE @Collection UNIQUEIDENTIFIER;
DECLARE @CompName NVARCHAR(20);
DECLARE @FileName NVARCHAR(255);
DECLARE @AddRemove NVARCHAR(255);
DECLARE @ProductName NVARCHAR(255);
DECLARE @Operator NVARCHAR(16);
DECLARE @SQL NVARCHAR(max);
-- Report Parameters
SELECT
@CompName = LTRIM(RTRIM(N'%CompName%')),
@AddRemove = LTRIM(RTRIM(N'%AddRemove%')),
@FileName = LTRIM(RTRIM(N'%FileName%')),
@ProductName = LTRIM(RTRIM(N'%ProductName%'))
SET @Collection = '%@Collection%'
-- Identify Variable values
IF (@CompName = N'') OR (@CompName = N'%') SELECT @CompName = NULL
IF (@AddRemove = N'') OR (@AddRemove = N'%') SELECT @AddRemove = NULL
IF (@FileName = N'') OR (@FileName = N'%') SELECT @FileName = NULL
IF (@ProductName = N'') OR (@ProductName = N'%') SELECT @ProductName = NULL
-- Check Variables
IF (@Collection IS NULL)
Set @Collection = 'b677c36f-8cf8-4c57-aa6f-f11948e128c7' --All Windows Servers, Just in case
IF (@ProductName IS NULL) AND (@FileName IS NULL) AND (@AddRemove IS NULL)
BEGIN
SELECT N'Please narrow the search results by using a parameter'
END
ELSE
BEGIN
IF (@AddRemove IS NOT NULL) AND ((@FileName IS NOT NULL) OR (@ProductName IS NOT NULL))
BEGIN
SELECT N'Please select only one of the following parameters:',
N' File Name and/or Product Name',
N' - OR - ',
N' Add Remove Programs'
END
ELSE
BEGIN
IF ((@FileName IS NOT NULL) OR (@ProductName IS NOT NULL)) AND (@AddRemove IS NULL)
BEGIN
-- File Name or Product Name Report
--Build EXE Hash Table
IF object_id('tempdb..#HashTable') IS NOT NULL
BEGIN
DROP TABLE #HashTable
END
CREATE TABLE #HashTable (
[_KeyHash] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
[File Name] NVARCHAR(255),
[ProductName] NVARCHAR(255),
[Manufacturer] NVARCHAR(255),
[ProductVersion] NVARCHAR(128),
[File Size] BIGINT
)
SELECT @SQL = '
INSERT INTO #HashTable
SELECT [_KeyHash], [File Name], [ProductName], [Manufacturer], [ProductVersion], [File Size]
FROM Cmn_SW_Common
WHERE 1=1'
IF @FileName IS NOT NULL
BEGIN
SELECT @Operator = CASE CHARINDEX(N'%', @FileName) WHEN 0 THEN N'=' ELSE N' LIKE ' END
SELECT @SQL = @SQL + ' AND [File Name]' + @Operator + N'''' + @FileName + N''''
END
IF @ProductName IS NOT NULL
BEGIN
SELECT @Operator = CASE CHARINDEX(N'%', @ProductName) WHEN 0 THEN N'=' ELSE N' LIKE ' END
SELECT @SQL = @SQL + ' AND [ProductName]' + @Operator + N'''' + @ProductName + N''''
END
-- Run our nicely built query
EXEC (@SQL)
-- Build main SQL query
SELECT @SQL = '
SELECT
Ident.[Name] AS [Server Name],
Ident.[Domain],
Prim.[User],
HT.[Manufacturer] as [Software Vendor],
HT.[ProductName] AS [Product Name],
HT.[File Name],
HT.[ProductVersion] AS [Product Version],
SPT.[File Path],
HT.[File Size],
LOC.[Fully Qualified Domain Name] AS [FQDN],
TCPIP.[IP Address] as [Primary IP Address],
SN.[Computer Model],
CPU.[Type] AS [CPU Type],
CPU.[Number] AS [Number of Processors],
CPU.[Cores] AS [Number of Cores],
Ident.[OS Name],
Ident.[OS Type],
Ident.[System Type],
SN.[Serial Number]
FROM Inv_AeX_SW_Audit_Software_spt SPT
INNER JOIN CollectionMembership CM
ON CM.[ResourceGuid] = SPT.[_ResourceGuid]
AND CM.[CollectionGuid] = @Collection
INNER JOIN #HashTable HT
ON HT.[_KeyHash] = SPT.[_KeyHash]
INNER JOIN Inv_AeX_AC_Identification Ident
ON Ident.[_ResourceGuid] = SPT.[_ResourceGuid]
INNER JOIN Item I
ON I.[Guid] = SPT.[_ResourceGuid]
LEFT OUTER JOIN vSource vS
ON vS.[Guid] = I.[OwnerNSGuid]
INNER JOIN ItemResource IR
ON IR.[Guid] = SPT.[_ResourceGuid]
LEFT OUTER JOIN Inv_AeX_AC_TCPIP TCPIP
ON TCPIP.[_ResourceGuid] = SPT.[_ResourceGuid]
AND TCPIP.[_id] =
(
SELECT TOP 1 TCP2.[_id]
FROM Inv_AeX_AC_TCPIP TCP2
WHERE SPT.[_ResourceGuid] = TCP2.[_ResourceGuid]
AND TCP2.[IP Address] NOT IN (''127.0.0.1'', ''0.0.0.0'', '''')
AND TCP2.[IP Address] IS NOT NULL
ORDER BY TCP2.[IP Address] ASC
)
LEFT OUTER JOIN Inv_AeX_AC_Primary_User Prim
ON Prim.[_ResourceGuid] = SPT.[_ResourceGuid]
AND Prim.[_id] =
(SELECT TOP 1 [_id]
FROM Inv_AeX_AC_Primary_User
WHERE SPT.[_ResourceGuid] = [_ResourceGuid]
ORDER BY [_id] DESC)
LEFT OUTER JOIN Inv_AeX_HW_Serial_Number SN
ON SN.[_ResourceGuid] = SPT.[_ResourceGuid]
LEFT OUTER JOIN Inv_AeX_AC_Location LOC
ON LOC.[_ResourceGuid] = SPT.[_ResourceGuid]
LEFT OUTER JOIN Inv_AeX_HW_CPU CPU
ON CPU.[_ResourceGuid] = SPT.[_ResourceGuid]
WHERE IR.[IsManaged] = 1
'
IF @CompName IS NOT NULL
BEGIN
SELECT @Operator = CASE CHARINDEX(N'%', @CompName) WHEN 0 THEN N'=' ELSE N' LIKE ' END
SELECT @SQL = @SQL + ' AND Ident.[Name]' + @Operator + N'''' + @CompName + N''''
END
SELECT @SQL = @SQL + ' ORDER BY Ident.[Name], HT.[File Name]'
-- Run our nicely built main SQL query
EXEC(@SQL)
-- Done
DROP TABLE #HashTable
END
IF (@FileName IS NULL) AND (@ProductName IS NULL) AND (@AddRemove IS NOT NULL)
BEGIN
-- Add/Remove Report
--Build ARP Hash Table
IF object_id('tempdb..#ARPHashTable') IS NOT NULL
BEGIN
DROP TABLE #ARPHashTable
END
CREATE TABLE #ARPHashTable (
[_KeyHash] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
[Name] NVARCHAR(255),
[Publisher] NVARCHAR(255),
[Version] NVARCHAR(64),
[Estimated Size (Kb)] NVARCHAR(64)
)
SELECT @SQL = '
INSERT INTO #ARPHashTable
SELECT [_KeyHash], [Name], [Publisher], [Version], [Estimated Size (Kb)]
FROM Cmn_OS_Add_Remove_Programs_Common
WHERE 1=1'
IF @AddRemove IS NOT NULL
BEGIN
SELECT @Operator = CASE CHARINDEX(N'%', @AddRemove) WHEN 0 THEN N'=' ELSE N' LIKE ' END
SELECT @SQL = @SQL + ' AND [Name]' + @Operator + N'''' + @AddRemove + N''''
END
-- Run our nicely built query
EXEC (@SQL)
-- Build main SQL query
SELECT @SQL = '
SELECT
Ident.[Name] AS [Server Name],
Ident.[Domain],
Prim.[User],
AHT.[Publisher] as [Software Vendor],
AHT.[Name] AS [Product Name],
AHT.[Version] AS [Product Version],
SPT.[Install Path],
AHT.[Estimated Size (Kb)],
LOC.[Fully Qualified Domain Name] AS [FQDN],
TCPIP.[IP Address] as [Primary IP Address],
SN.[Computer Model],
CPU.[Type] AS [CPU Type],
CPU.[Number] AS [Number of Processors],
CPU.[Cores] AS [Number of Cores],
Ident.[OS Name],
Ident.[OS Type],
Ident.[System Type],
SN.[Serial Number]
FROM Inv_AeX_OS_Add_Remove_Programs_spt SPT
INNER JOIN CollectionMembership CM
ON CM.[ResourceGuid] = SPT.[_ResourceGuid]
AND CM.[CollectionGuid] = @Collection
INNER JOIN #ARPHashTable AHT
ON AHT.[_KeyHash] = SPT.[_KeyHash]
INNER JOIN Inv_AeX_AC_Identification Ident
ON Ident.[_ResourceGuid] = SPT.[_ResourceGuid]
INNER JOIN Item I
ON I.[Guid] = SPT.[_ResourceGuid]
LEFT OUTER JOIN vSource vS
ON vS.[Guid] = I.[OwnerNSGuid]
INNER JOIN ItemResource IR
ON IR.[Guid] = SPT.[_ResourceGuid]
LEFT OUTER JOIN Inv_AeX_AC_Primary_User Prim
ON Prim.[_ResourceGuid] = SPT.[_ResourceGuid]
AND Prim.[_id] =
(SELECT TOP 1 [_id]
FROM Inv_AeX_AC_Primary_User
WHERE SPT.[_ResourceGuid] = [_ResourceGuid]
ORDER BY [_id] DESC)
LEFT OUTER JOIN Inv_AeX_AC_TCPIP TCPIP
ON TCPIP.[_ResourceGuid] = SPT.[_ResourceGuid]
AND TCPIP.[_id] =
(
SELECT TOP 1 TCP2.[_id]
FROM Inv_AeX_AC_TCPIP TCP2
WHERE SPT.[_ResourceGuid] = TCP2.[_ResourceGuid]
AND TCP2.[IP Address] NOT IN (''127.0.0.1'', ''0.0.0.0'', '''')
AND TCP2.[IP Address] IS NOT NULL
ORDER BY TCP2.[IP Address] ASC
)
LEFT OUTER JOIN Inv_AeX_AC_Location LOC
ON LOC.[_ResourceGuid] = SPT.[_ResourceGuid]
LEFT OUTER JOIN Inv_AeX_HW_Serial_Number SN
ON SN.[_ResourceGuid] = SPT.[_ResourceGuid]
LEFT OUTER JOIN Inv_AeX_HW_CPU CPU
ON CPU.[_ResourceGuid] = SPT.[_ResourceGuid]
WHERE IR.[IsManaged] = 1
'
IF @CompName IS NOT NULL
BEGIN
SELECT @Operator = CASE CHARINDEX(N'%', @CompName) WHEN 0 THEN N'=' ELSE N' LIKE ' END
SELECT @SQL = @SQL + ' AND Ident.[Name]' + @Operator + N'''' + @CompName + N''''
END
SELECT @SQL = @SQL + ' ORDER BY Ident.[Name], AHT.[Name]'
-- Run our nicely built main SQL query
EXEC(@SQL)
-- Done
DROP TABLE #ARPHashTable
END
END
END