SQL Query for a report

ksweet · 09-29-2006, 06:03 PM

What table or tables holds the info for Sequential Software Delivery Tasks?
Whch Package \ Progams are assigned to it and thier order?

This will only show the SSD Tasks that have been enabled and assigned to a collection, But I also want to see the ones that have just been created and not enabled yet... There has to be another table to use then "Inv_Synch_Status"...

Code:

SELECT DISTINCT 
                      Item.Name AS [Sequential Task Name], SWDPackage.Name AS [Package Name], SWDProgram.Name AS [Program Name], 
                      SWDAdvertisement.Name AS [Advertisment Name]
FROM         Item INNER JOIN
                      Class ON Item.ClassGuid = Class.Guid INNER JOIN
                      SWDAdvertisement INNER JOIN
                      Inv_Synch_Status ON SWDAdvertisement.AdvertisementId = Inv_Synch_Status.TaskId INNER JOIN
                      SWDProgram ON SWDAdvertisement.ProgramId = SWDProgram.ProgramId INNER JOIN
                      SWDPackage ON SWDProgram.PackageId = SWDPackage.PackageId INNER JOIN
                      ItemReference ON Inv_Synch_Status.SynchronizationId = ItemReference.ChildItemGuid ON Item.Guid = ItemReference.ParentItemGuid
WHERE     (Class.Type = N'Altiris.NS.SWDWin32.SequentialSWD.SequentialSWDItem') AND (SWDAdvertisement._Latest = 1) AND (SWDProgram._Latest = 1) AND
                       (SWDPackage._Latest = 1) AND (ItemReference.Hint = N'sequentialswditem uses active synchronization item')

ksweet · 09-29-2006, 06:18 PM

VBScript I used to Scan Database to filnd tables that hold the GUIDs of the SSDTasks

My results...
GUID Found In Table Evt_NS_Item_Management Column ItemGuid
GUID Found In Table Evt_NS_Security_Descriptor_Change Column ItemGuid
GUID Found In Table Item Column Guid
GUID Found In Table Item Column SecurityGuid
GUID Found In Table ItemActive Column Guid
GUID Found In Table ItemCache Column ItemGuid
GUID Found In Table ItemFolder Column ItemGuid
GUID Found In Table ItemNameCache Column ItemGuid
GUID Found In Table ItemPresentation Column BaseGuid
GUID Found In Table ItemPresentation Column Guid
GUID Found In Table ItemReference Column ChildItemGuid
GUID Found In Table ItemReference Column ParentItemGuid
GUID Found In Table SecurityACE Column EntityGuid
GUID Found In Table SecurityACEChanged Column EntityGuid
GUID Found In Table SecurityEntity Column Guid
GUID Found In Table String Column BaseGuid
GUID Found In Table StringCacheRN Column BaseGuid

Code:

MyServer="Server"
MyLogon="UserID"
MyPassword="Password"

Set DBCon = CreateObject("ADODB.Connection") 
DBCon.Open "Provider=SQLOLEDB;uid=" & MyLogon & ";pwd=" & MyPassword & ";Data Source=" & MyServer & ";database=Altiris"

SQL = "SELECT Item.Guid FROM Item INNER JOIN Class ON Item.ClassGuid = Class.Guid WHERE (Class.Type = N'Altiris.NS.SWDWin32.SequentialSWD.SequentialSWDItem')"
Set TargetGUID = DBCon.Execute(SQL)
Do While TargetGUID.EOF = False
    MyGUID = TargetGUID.Fields("GUID").Value
    SQL = "SELECT sysobjects.name AS MyTable, syscolumns.name AS MyColumn FROM sysobjects INNER JOIN  syscolumns ON sysobjects.id = syscolumns.id WHERE (sysobjects.xtype = 'U') AND (syscolumns.xtype = 36) AND  (NOT (sysobjects.name LIKE N'xdls%') AND NOT (sysobjects.name LIKE N'xdlc%')) ORDER BY sysobjects.name, syscolumns.name"
    Set TableList = DBCon.Execute(SQL)
    Do While TableList.EOF = False
        SQL = "SELECT DISTINCT " & TableList.Fields("MyColumn").Value & " FROM " & TableList.Fields("MyTable").Value & " WHERE " & TableList.Fields("MyColumn").Value & " = '" & TargetGUID.Fields("GUID").Value & "'"
        Set GUIDSearch = DBCon.Execute(SQL)
        Do While GUIDSearch.EOF = False
            WScript.Echo "GUID " & TargetGUID.Fields("GUID").Value & " Found In Table " & TableList.Fields("MyTable").Value & " Column " & TableList.Fields("MyColumn").Value
            GUIDSearch.MoveNext
        Loop
        TableList.MoveNext
    Loop
    TargetGUID.MoveNext
Loop

DBCon.Close

Set DBCon = Nothing 
Set TargetGUID = Nothing
Set TableList = Nothing
Set GUIDSearch = Nothing

ksweet · 10-03-2006, 05:38 PM

any one have info on this?

Tom.Reyntjens · 10-04-2006, 06:27 AM

If I'm not mistaken, this information is stored only in the state field in the Item table for the Synchronisation Task related to the Sequential Software Delivery Task.

You can get the xml that describes the task as follows :

select i.State
from Item
join ItemReference ir on i.Guid=ir.ChildItemGuid
where ParentItemGuid={GUID-OF-THE-SEQUENTIAL-SOFTWARE-TASK}
and hint='sequentialswditem uses active synchronization item'

You could write a small vbs that performs this query and saves the xml in a file. Then you could use "normal" xml-processing code to process the item\Synchronization\Tasks key.

ksweet · 10-04-2006, 12:10 PM

No wonder I couldn't find it... Well there goes the reports I wanted to write to view all Sequential SWD Tasks...

Tom.Reyntjens · 10-04-2006, 06:14 PM

Unless of course you create your own table with Sequential Task information, and schedule a script to update that table regularly using a combination of the query and the XML-parsing. Shouldn't be too hard

ksweet · 10-05-2006, 11:52 AM

Quote:

Originally Posted by Tom.Reyntjens

Unless of course you create your own table with Sequential Task information, and schedule a script to update that table regularly using a combination of the query and the XML-parsing. Shouldn't be too hard

No it wouldn't, but I am that lazy...