Skip to content

License Report - query help

Featured Replies

Hello,

 

i use the following query to get a license report but i´m not quite happy with the result and don´t know how to edit it.

 

declare @ChannelCode varchar(32)
select @ChannelCode = 'All'
declare @ProductName nvarchar(255)

select
ProductName,
FamilyName,
VersionCode,
coll.Name,
MAX(VersionSequence) as VersionSequence,
MAX(ChannelCode) as ChannelCode,
count(distinct ilp.ResourceID) as InventoryCount
from
dbo.vInventoriedLicensedProduct ilp
left join v_FullCollectionMembership fcm on ilp.ResourceID = fcm.ResourceID
left join v_Collection coll on fcm.CollectionID = coll.CollectionID
where
(ilp.ProductName = @ProductName OR @ProductName IS NULL)
and
(@ChannelCode is NULL or @ChannelCode = N'All' OR ilp.ChannelCode = @ChannelCode)
group by ProductName, FamilyName, VersionCode, coll.Name

 

 

The result is the following:

 

16568286kq.jpg

 

16568288eb.jpg

 

The problem with the result is that every collection is listed in the column Name. But i only want to choose some of them. How can i edit the query to get a result with only three collections ?

 

The best result would be the following, but for this i think i have to modify the whole query.

 

Any experts who can help me ?

 

 

16568496no.jpg

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.