Report that will give me the same info as the Status tab

Products

• IT Management Suite

Issue/Introduction

How do I make a report that will give me the same info as the Status tab found in Software Delivery?

Environment

ITMS 7.x, 8.x

Resolution

Below is an example XML report that will display the same status information that is available in the Software Delivery Job Status tab.

Create a report using the example XML below and import the file to your SMP Console under the Reports page.

Prior to running this report, it will be necessary to know the GUID of the Software Delivery job you need the status for.

To find the current GUID of the Software Delivery job, right-click on the Software Delivery job, choose Properties, and the GUID will be one of the first fields displayed.

 

Example XML:

<item guid="{97e6dbf0-106a-4e1d-b61a-cb8923fb3104}" classGuid="{67033a4e-a848-4a0b-b037-ab02854a315a}">
  <!--  Type: Altiris.NS.StandardItems.Query.Report  -->
  <name>Status tab report by GUID</name>
  <alias />
  <productGuid>{d0e33520-c160-11d2-8612-00104b74a9df}</productGuid>
  <itemAttributes>Normal</itemAttributes>
  <itemLocalizations>
    <culture name="">
      <description>Status tab report by GUID</description>
      <name>Status tab report by GUID</name>
    </culture>
    <culture name="en">
      <description>Status tab report by GUID</description>
    </culture>
  </itemLocalizations>
  <scheduling>
    <enabled>False</enabled>
    <sharedSchedule>{00000000-0000-0000-0000-000000000000}</sharedSchedule>
  </scheduling>
  <version>6.0</version>
  <manufacturer />
  <alwaysPromptParameters>True</alwaysPromptParameters>
  <queries>
    <parameter type="text" prompt="True" name="myGUID" substituted="true">
      <default><![CDATA[

  ]]></default>
      <valueType><![CDATA[NVarChar]]></valueType>
      <prompt><![CDATA[
Package GUID: 
  ]]></prompt>
    </parameter>
    <query type="builderQuery">
      <view type="grid">
        <enabled>True</enabled>
        <default>True</default>
        <hide><![CDATA[
Guid
  ]]></hide>
      </view><queryBuilder><directEdit legacy="true" /><userCustomized><![CDATA[
DECLARE @myGUID varchar(255)   
SET @myGUID = %myGUID%   
 
EXEC sp_executesql N'IF EXISTS ( 
 
SELECT *  
from dbo.sysobjects  
where id = object_id(N''[dbo].[Evt_AeX_SWD_Execution]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)   
 
SELECT DISTINCT Wrksta.Name As [Computer Name] , ''Not Run'' As Status, NULL AS [Attempt Time], ''01/01/1900'' As AttemptTimeEx  
FROM ItemReference IR INNER JOIN CollectionMembership CM ON  IR.ChildItemGuid = CM.CollectionGuid INNER JOIN Wrksta ON  CM.ResourceGuid = Wrksta.Guid   
WHERE LOWER(IR.Hint) = ''policyappliestocollection'' AND ParentItemGuid = @AdvertisementGuid  AND Wrksta.Guid NOT IN ( 
 
SELECT _ResourceGuid  
FROM Evt_AeX_SWD_Execution  WHERE AdvertisementId = @AdvertisementGuid )  UNION   
 
Select DISTINCT Wrksta.Name As [Computer Name], CASE WHEN EX.Status LIKE ''%Command executed%''  
 
THEN   ''Success'' ELSE ''Failed '' END AS ''Status'',  EX.Start  as [Attempt Time], EX.Start As AttemptTimeEx   
From Evt_AeX_SWD_Execution EX  INNER JOIN ItemReference IR ON  EX.AdvertisementId = IR.ParentItemGuid  
INNER JOIN Wrksta ON  EX._ResourceGuid = Wrksta.Guid   
WHERE LOWER(IR.Hint) = ''policyappliestocollection'' AND ParentItemGuid = @AdvertisementGuid  ELSE   
 
SELECT DISTINCT Wrksta.Name As [Computer Name], ''Not Run'' As Status, NULL AS [Attempt Time], ''01/01/1900'' As AttemptTimeEx  
FROM ItemReference IR  INNER JOIN CollectionMembership CM ON IR.ChildItemGuid = CM.CollectionGuid  INNER JOIN Wrksta ON  CM.ResourceGuid = Wrksta.Guid  
WHERE LOWER(IR.Hint) = ''policyappliestocollection'' AND ParentItemGuid = @AdvertisementGuid  UNION  Select  
DISTINCT Wrksta.Name As [Computer Name], CASE WHEN EX.Status LIKE ''%Command executed%''  
 
THEN   ''Success '' ELSE   ''Failed '' END AS ''Status'',  EX.Start  as [Attempt Time], EX.Start As AttemptTimeEx   
From Evt_AeX_SWD_Execution EX  INNER JOIN ItemReference IR ON  EX.AdvertisementId = IR.ParentItemGuid   
INNER JOIN Wrksta ON  EX._ResourceGuid = Wrksta.Guid   
WHERE LOWER(IR.Hint) = ''policyappliestocollection'' AND ParentItemGuid = @AdvertisementGuid   
ORDER BY [Computer Name],Status,[Attempt Time]', N'@AdvertisementGuid uniqueidentifier', @AdvertisementGuid = @myGUID
  ]]></userCustomized></queryBuilder></query>
  </queries>
  <parentFolderGuid>4d4d03c6-cb51-4502-886f-13f756198b1b</parentFolderGuid>
  <security owner="S-1-5-21-2838691025-1539222162-802988254-1106" inherit="True">
    <aces>
      <ace type="trustee" sid="S-1-5-21-2838691025-1539222162-802988254-1106" name="">
        <permissionGrants>
          <permissionGrant guid="{f57658a3-865e-47d8-8a77-0210ade119b0}" name="Run Report" />
          <permissionGrant guid="{0bef78d6-13f0-4f9e-9a56-1ba2c7a5dbe1}" name="Save Reports" />
          <permissionGrant guid="{ac296df1-eb40-4592-899f-25d5c07d45f6}" name="Write" />
          <permissionGrant guid="{819dae1e-b1a5-4643-81a1-26ef95feb8a8}" name="Change Permissions" />
          <permissionGrant guid="{93d33ee3-5703-4a24-ac4f-4dbe460b3b83}" name="Run Reports" />
          <permissionGrant guid="{983a2d22-7a82-4db0-a707-52c7d6b1441e}" name="Read" />
          <permissionGrant guid="{eca6254f-5017-4730-9b3f-5add230829b7}" name="Delete" />
          <permissionGrant guid="{726b1c09-7108-450d-ae24-5f8e93135ed6}" name="Clone" />
          <permissionGrant guid="{4ddc04c3-f0a5-4e88-84aa-c44c8c5ebcc4}" name="Read Permissions" />
        </permissionGrants>
      </ace>
    </aces>
  </security>
  <itemReferences />
</item>