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

Adding Computer Name to Add/Remove Change Report

Created: 29 Dec 2009
byrdjg's picture

I often come across useful reports on here and in the Altiris Knowledgebase; however when they get a little more complex such as the one below (which is great report)  I have a hard time getting computer name ([vRM_Computer_Item].[Name]) nestled properly into the query so that it displays in the report .  Can anyone offer some assistance on how I would add computer name to the report below?

Ultimately my goal is to generate a report that provides me with software changes on all computers for select software.  When the report runs, It would only return computers whose add/remove programs have changed since the last time the report was run.

The report below gives me close to what I’m looking for minus the computer name and select software.  This report leaves out computer name and pulls ALL changes to software.

Any help is GREATLY Appreciated.  I did manage to modify the report sample that I got from the Knowledgebase to work with SMP 7 vs. NS6.  The one below works on SMP 7.

select

  c3.[_ResourceGuid]

  ,'Added' as [Added or Removed]

  ,c3.[LatestSnapshotDate] as InventoryDate

  ,c3.[DisplayName]

  ,c3.[DisplayVersion]

from

  (

    select

      c2.[_Resourceguid]

      ,c2.[LatestSnapshotDate]

      ,c2.[NextToLastSnapshotDate]

      ,ih4.[Snapshotid]

      ,ih4.[DisplayName]

      ,ih4.[DisplayVersion]

    from

      (

        select

          ih3.[_ResourceGuid]

          ,ih3.[LatestSnapshotDate]

          ,c1.[NextToLastSnapshotDate]

        from

          (

            select

              [_ResourceGuid]

              ,max([InventoryDate]) as LatestSnapshotDate

            from

              InvHist_AddRemoveProgram

            group by

              [_ResourceGuid]

          ) ih3

            join

              (

                select

                  ih2.[_ResourceGuid]

                  ,max([InventoryDate]) as [NextToLastSnapshotDate]

                from

                  InvHist_AddRemoveProgram ih1

                  Left Join

                    (

                      select

                        [_ResourceGuid]

                        ,max([InventoryDate]) as LatestSnapshotDate

                      from

                        InvHist_AddRemoveProgram

                      group by

                        [_ResourceGuid]

                    ) ih2

                  on

                    ih1.[_ResourceGuid]=ih2.[_ResourceGuid]

                where

                  ih1.[InventoryDate]<>ih2.[LatestSnapshotDate]

                group by

                  ih2.[_ResourceGuid]

              ) c1

            on

              ih3.[_ResourceGuid]=c1.[_ResourceGuid]

      ) c2

        join

          InvHist_AddRemoveProgram ih4

        on

          c2.[_ResourceGuid] = ih4.[_ResourceGuid] and c2.[LatestSnapshotDate]=ih4.[InventoryDate]

  ) c3

    left join

      InvHist_AddRemoveProgram ih5

    on

      c3.[_ResourceGuid] = ih5.[_ResourceGuid]

      and c3.[NextToLastSnapshotdate]=ih5.[InventoryDate]

      and c3.[DisplayName]=ih5.[DisplayName]

      and c3.[DisplayVersion]=ih5.[DisplayVersion]

where

  ih5.[Snapshotid] is null

UNION

select

  c3.[_ResourceGuid]

  ,'Removed' as [Added or Removed]

  ,c3.[NextToLastSnapshotDate] as InventoryDate

  ,c3.[DisplayName]

  ,c3.[DisplayVersion]

from

  (

    select

      c2.[_Resourceguid]

      ,c2.[LatestSnapshotDate]

      ,c2.[NextToLastSnapshotDate]

      ,ih4.[Snapshotid]

      ,ih4.[DisplayName]

      ,ih4.[DisplayVersion]

    from

      (

        select

          ih3.[_ResourceGuid]

          ,ih3.[LatestSnapshotDate]

          ,c1.[NextToLastSnapshotDate]

        from

          (

            select

              [_ResourceGuid]

              ,max([InventoryDate]) as LatestSnapshotDate

            from

              InvHist_AddRemoveProgram

            group by

              [_ResourceGuid]

          ) ih3

            join

              (

                select

                  ih2.[_ResourceGuid]

                  ,max([InventoryDate]) as [NextToLastSnapshotDate]

                from

                  InvHist_AddRemoveProgram ih1

                  Left Join

                    (

                      select

                        [_ResourceGuid]

                        ,max([InventoryDate]) as LatestSnapshotDate

                      from

                        InvHist_AddRemoveProgram

                      group by

                        [_ResourceGuid]

                    ) ih2

                  on

                    ih1.[_ResourceGuid]=ih2.[_ResourceGuid]

                where

                  ih1.[InventoryDate]<>ih2.[LatestSnapshotDate]

                group by

                  ih2.[_ResourceGuid]

              ) c1

            on

              ih3.[_ResourceGuid]=c1.[_ResourceGuid]

      ) c2

        join

          InvHist_AddRemoveProgram ih4

        on

          c2.[_ResourceGuid] = ih4.[_ResourceGuid] and c2.[NextToLastSnapshotDate]=ih4.[InventoryDate]

  ) c3

    left join

      InvHist_AddRemoveProgram ih5

    on

      c3.[_ResourceGuid] = ih5.[_ResourceGuid]

      and c3.[LatestSnapshotdate]=ih5.[InventoryDate]

      and c3.[DisplayName]=ih5.[DisplayName]

      and c3.[DisplayVersion]=ih5.[DisplayVersion]

where

  ih5.[Snapshotid] is null