|
if (@SoftwareID = '') set @SoftwareID = NULL; if (@Tag1Name = '') set @Tag1Name = NULL; if (@Tag2Name = '') set @Tag2Name = NULL; if (@Tag3Name = '') set @Tag3Name = NULL; select MEM .Netbios_Name0 [Computer Name], SCUM .TopConsoleUser0 as 'TCU', Soft .NormalizedName [Product Name], TG1 .TagName [Label 1], TG2 .TagName [Label 2], TG3 .TagName [Label 3] from v_GS_INSTALLED_SOFTWARE_CATEGORIZED Soft inner join (select v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 from v_R_System_Valid join v_FullCollectionMembership ON v_R_System_Valid.ResourceID = v_FullCollectionMembership.ResourceID where v_FullCollectionMembership.CollectionID = @CollectionID )MEM ON MEM.ResourceID = Soft.ResourceID inner join v_LU_SoftwareList_Local ls ON ls.SoftwareID = Soft.SoftwareID left join v_LU_Tags TG1 ON TG1.TagID = ls.Tag1ID left join v_LU_Tags TG2 ON TG2.TagID = ls.Tag2ID left join v_LU_Tags TG3 ON TG3.TagID = ls.Tag3ID join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM ON SCUM.ResourceID = Soft.ResourceID where COALESCE (TG1.TagID, TG2.TagID, TG3.TagID) IS NOT NULL AND (@SoftwareID IS NULL OR Soft.SoftwareID = @SoftwareID) AND (@Tag1Name IS NULL OR TG1.TagName = @Tag1Name OR TG2.TagName = @Tag1Name OR TG3.TagName = @Tag1Name) AND (@Tag2Name IS NULL OR TG1.TagName = @Tag2Name OR TG2.TagName = @Tag2Name OR TG3.TagName = @Tag2Name) AND (@Tag3Name IS NULL OR TG1.TagName = @Tag3Name OR TG2.TagName = @Tag3Name OR TG3.TagName = @Tag3Name) order by MEM .Netbios_Name0, Soft .NormalizedName;
|
|
|