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.
Original Message:
Sent: 09-24-2020 04:53 AM
From: Igor Perevozchikov
Subject: Altiris report using the month
Hello Riggzie!
1. By default, such information shown in resource manager of selected resource, on 'Resource Change History' page
------------------------------
Software QA Engineer
Broadcom Inc.
Original Message:
Sent: 09-21-2020 09:47 AM
From: Jeffrey Riggs
Subject: Altiris report using the month
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.