Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

List all command lines associated with the deliverable software resource.

Created: 06 Feb 2014 • Updated: 10 Feb 2014 | 6 comments
This issue has been solved. See solution.

Hello,

I hope someone can guide me in the right direction. Using ASDK we developed a software delivery tool that allows our helpdesk to setup the SWD task without the need to visit the console.

Basically the analyst selects the package from the dropdown list. Based on the package selection they select the command (Install, Uninstall etc.) assigned to the given software package. It worked like a charm in NS6. When we migrated to NS7.1, it also worked because we moved the packages to the new platform.

The troubles started when started adding the software packages. NS 7.1. The new version allows you to create command lines assigned to the software resource even without the software package as far as I could find.

In the old NS6 all command lines were assigned to the packages.
We use the following queries to obtain the command lines assigned to any given package.

First query runs to get the GUID of the package given the name...
SELECT [SWDPackage].[PackageID] AS 'PackageID' FROM [SWDPackage] WHERE [Name] = '302963 Adobe Reader XI 11.0.04'

2nd query uses this GUID to then get the commands:
SELECT Distinct [SWDProgram].[Name] AS 'Name' FROM [SWDProgram] WHERE [PackageID] = '36827F8B-BA21-48BC-BAEF-8435119BF787' ORDER BY Name

Question is: how to get all command lines assigned to the deliverable software resource regardless the command line requires a package or not in NS7.1?

Thank you.
Tomasz

Operating Systems:

Comments 6 CommentsJump to latest comment

andykn101's picture

You need to look at Resource Associations. So you first select the Software Resource, the Resource Assocation lookup will give you all the command lines associated with tha Software Resource. once one of those is chosen you can then use Resource Associations in the background to see if there is a package associated with that command line.

To examine the way the Resource Associations work you can open Resource Manager and select a command line in the top box. Then select Resource Association Diagram to see how it all fits together (you might need to adjust the Levels to 1 or 2) and Resource Assocaition Grid for an easier to read list.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

Tomasz Wozniak's picture

Hello Andy,

Thank for the tip. I have already been to the Association Diagram before posting the discussion. More or less undersand the associations. I also studied the database schema. But still have not idea how the SQL tables are associated with each other.

Basically what I am looking for is the name of the SQL table for command lines, and software resource. I no longer care if the sortware resource contains a package, but it must a sofware release and deliverable.

I found the vsoftwarerelease and vsoftwarecommandline views. What I cannot figure out yet what is the direct or indirect connections between them ?

For SWDPackage and SWDProgram the foreign key is pretty obvious. In 7.1 not that really :)

Thank you.

Tomasz

 

andykn101's picture

The connection is in the Resource Associations table. There will be an association of Software Release contains Software Package and another of Command line depends on Software Package. Each of these Resource Associations will have a GUID and they are all tied together in the Resource Associations table.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

SOLUTION
SK's picture

The following may help:-  https://www-secure.symantec.com/connect/blogs/unde...

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

SOLUTION
SK's picture

Using that information, I was able to create the following query: 

SELECT vi1.Name AS Package,vi2.Name AS CommandLineName
FROM vItem vi1
JOIN ResourceAssociation ra ON ra.ParentResourceGuid = vi1.[Guid]
JOIN vItem vi2 ON vi2.[Guid] = ra.ChildResourceGuid
WHERE ra.ResourceAssociationTypeGuid = '64727DE8-29BD-4529-8622-BD0DD92E4258' --Command Line Requires Package
ORDER BY vi1.Name ASC

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

Tomasz Wozniak's picture

Hello Andy and SK,

Thank you for your input.
I have reviewed again the Resource Association table as well as the Association Diagram. I figured out that
the relationship between software resource and the command line I look for is 'Software Program Installs Software Component'

I was not sure how to filter out the resources only to sofware releases.
Anyway the following code is satisfactory to me. It list all command lines associated with the Sofware resources regardless the command line requires the package or not. :-)

SELECT
vi2.Name AS CommandLine,
vi3.Name AS SoftwareResource
FROM vItem vi1
JOIN ResourceAssociation ra ON ra.ResourceAssociationTypeGuid = vi1.[Guid]
JOIN vItem vi2 ON vi2.[Guid] = ra.ParentResourceGuid
JOIN vItem vi3 ON vi3.[Guid] = ra.ChildResourceGuid
where ra.ResourceAssociationTypeGuid = '2D12146F-8AA9-4A41-B7DB-577892EF8692' -- Software Program Installs Software Component
and vi3.classguid ='B6FE92FD-D5AC-4DC2-A023-A56C2973A413' --Software Release
ORDER BY 2 DESC

Thank you for your help !
Tomasz

SOLUTION