Client Management Suite

 View Only
  • 1.  Altiris report using the month

    Posted Sep 23, 2020 06:09 PM
    Well this is a 2 part question that depends on the answer to the first part...

    I know that when you retire a computer, it will show in vFixedAssetResourceStatus. 
    My question is - Is there a table that tracks the date/time these computers are retired?

    Thanks for any insight.


  • 2.  RE: Altiris report using the month
    Best Answer

    Broadcom Employee
    Posted Sep 24, 2020 04:54 AM

    Hello Riggzie!

    1. By default, such information shown in resource manager of selected resource, on 'Resource Change History' page


    2. This information is collected as history, because by default save history data is enabled 

    3. This stored procedure will return same information about history changes of resource associations
         EXECUTE spResourcePlatform_GetResourceAssociationChangingHistory @resourceGuid='Put here resource GUID', @culture=N'en-US'

    4. This SQL table contains all history data when appropriate resource got changed own asset status, etc
         ResourceAssociationHistoryDelta

    Thanks,
    IP.


    ------------------------------
    Software QA Engineer
    Broadcom Inc.
    ------------------------------



  • 3.  RE: Altiris report using the month

    Posted Sep 24, 2020 04:10 PM
    Thanks Igor! 
    As usual, your knowledge always helps out.
    Since we post messages, and need to wait for approval before it is shown, I found out what you have listed above but found a way for my use.

    Searching google, I found where one person created a report based off of what you have listed and I found another piece of code that i utilized at the top which is not from an Altiris forum/report but works.

    this is what i have in place at this moment...

    DECLARE @PreviousMonthStart DATETIME2 DECLARE @PreviousMonthEnd DATETIME2 SET @PreviousMonthStart = DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0); SET @PreviousMonthEnd = DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)); PRINT @PreviousMonthStart; PRINT @PreviousMonthEnd ; SELECT vi.Name 'Computer',COALESCE(vi2.Name, '(Implied Active)') 'Status',vi.ModifiedDate AS 'Retired Date/Time' FROM vAsset va JOIN vItem vi ON vi.Guid = va._ResourceGuid LEFT JOIN ResourceAssociation ra ON ra.ParentResourceGuid = va._ResourceGuid AND ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' LEFT JOIN vItem vi2 ON vi2.Guid = ra.ChildResourceGuid WHERE ([Asset Type] = 'Computer' OR [Asset Type] = 'Virtual Machine') AND vi2.Name = 'Retired' and vi.ModifiedDate >= @PreviousMonthStart AND vi.ModifiedDate < @PreviousMonthEnd ORDER BY vi.ModifiedDate, vi.Name​Now this works but it goes back the previous month and the current month as it seems @PreviousMonthEnd is not working. I messed with the numbers and they didnt change anything but changing @PreviousMonthStart back a few negative numbers did actually go back a month or two or three....

    I placed it into an automation policy to send me an email via shared schedule of Monthly.
    That will work as by default it is 2:25am on day 1, so I doubt anyone will be retiring any machines by then so would work. My boss wanted me to be positive as we are a global company, so I just created a new Shared Schedule and set it to 12:01 on 1st day of the month.

    Here is the result, and it is exactly what I need as this email will go to someone who will import it into the ticket system and retire them from it.


    Retired Systems



  • 4.  RE: Altiris report using the month

    Posted Sep 24, 2020 07:40 PM
    You could use the following for your Declare/Set block. You only need to change the number of months you want to go back in the 'set @date' section. The example below goes back 2 months from today:

    declare @date datetime declare @PreviousMonthStart datetime2 declare @PreviousMonthEnd datetime2 set @date = DATEADD(month, - 2, GETDATE()) set @PreviousMonthStart = DATEADD(DAY, 1, EOMONTH(@date, - 1)) set @PreviousMonthEnd = EOMONTH(@date)​


  • 5.  RE: Altiris report using the month

    Broadcom Employee
    Posted Sep 24, 2020 10:03 PM
    I'm glad that your question is solved now :)

    ------------------------------
    Software QA Engineer
    Broadcom Inc.
    ------------------------------



  • 6.  RE: Altiris report using the month

    Posted Sep 24, 2020 07:55 AM
    You can find status changes in the table ResourceAssociationHistoryDelta where the parent is the GUID of the asset and the child is the GUID of the status.  Remember that this is only available for as long as your resource association retention setting.

    Stu

    ------------------------------
    Guggenheim Partners
    ------------------------------



  • 7.  RE: Altiris report using the month

    Posted Sep 27, 2020 08:49 PM
    Thanks all! 
    The big thing is we only want the last month. Since it will run on the 1st at 12:01 via my custom shared schedule, should work as we need it too.
    I will still check out the other samples given as i always like seeing what else there is!
    thanks again!
    Jeff


  • 8.  RE: Altiris report using the month

    Posted Sep 28, 2020 03:58 PM
    Hi Riggzie

    You could use the vitem table and join it back your table via the Guid column

    A query like below should give you what you are looking for.


    select
    vi.name [Computer Name]
    ,s.Status
    ,s.IsManaged
    ,vi.ModifiedBy
    ,vi.ModifiedDate
    from vFixedAssetResourceStatus s
    join vitem vi on vi.Guid = s.Guid
    order by vi.ModifiedDate desc


    Regards :)