Video Screencast Help
Search Video Help Close Back
to help

report or sql query to get all software releases with packages

Created: 18 Jul 2012 | Updated: 19 Jul 2012 | 1 comment
SaschaH's picture
SaschaH Partner Accredited
0 0 Votes
Login to vote
This issue has been solved. See solution.

or any other way to target all software releases resources that have packages and not the whole software catalog.

Trying to get a target for exporting software releases from workflow but bit stuck with this problem. actually can get all software releases items and all packages items in workflow, then filter the software resource collection against the packages by name. But first its dead slow and secondly it runs on one system and crashes on the next. so might be better to go the direct sql query way, but so far I didnt get were the Item table and the swdpackage table are linked.

thanks for suggestions..

Discussion Filed Under:
, , , , , , , ,

Comments 1 Comment • Jump to latest comment

SaschaH's picture
SaschaH Partner Accredited
report or sql query to get all software releases with packages - Comment:19 Jul 2012 : Link

okay will just quickly answer myself. maybe interesting for others too. just traced what the software replicator is doing.

select ri.Name, rm.Guid 
from ScopeMembership sc 
JOIN Item i on i.Guid = sc.ScopeCollectionGuid 
JOIN vSoftwareComponentResource rm on (sc.ResourceGuid = rm.Guid 
and ResourceTypeGuid in ('07849305-D41A-45C2-95F0-333FA79FB862',
'E52AE490-FA9C-45DE-B22E-47B7E9FB34B7',
'386E25C7-F12A-4D42-96B5-4E086D2EE850',
'EB86DCF7-1E14-4862-87DA-9B26EC931D75') and rm.Deleted=0) 
JOIN vResourceItem ri on rm.Guid=ri.Guid 
where i.Name = 'Software Release' AND 
rm.Guid in 
( SELECT distinct ra.ChildResourceGuid FROM ResourceAssociation ra 
LEFT OUTER JOIN vSoftwareComponentSearch it 
ON ra.ChildResourceGuid = it.Guid 
WHERE ra.ResourceAssociationTypeGuid = '2d12146f-8aa9-4a41-b7db-577892ef8692' ) 
AND lower(ri.Name) like N'%' order by ri.Name

important is the ResourceAssosciationType which marks it as deliverable software.

Quickest way, can also use table generator on the view vAC_DeliverableSoftware.

 

Bechtle – your strong IT partner. Today and tomorrow

If that seems to help, please "Mark as Solution"

SOLUTION
0
Login to vote
  • Actions