Need to report ONLY active assets (Computers)

[Migration User]

I need to include ONLY active assets in this report (below). How can I do this?

SELECT T0.[Name] AS 'Name', T1.[OEM OS] AS 'OEM OS', T0.[OS Name] AS 'OS Name', T0.[OS Type] AS 'OS Type', T2.[System Type] AS 'OS Version', T0.[OS Revision] AS 'OS Revision', T3.[ProductSuite] AS 'ProductSuite', T0.[System Type] AS 'System Type' FROM [Inv_AeX_AC_Identification] T0 LEFT OUTER JOIN [Inv_LPH_Asset_Details] T1 ON T0.[_ResourceGuid] = T1.[_ResourceGuid] LEFT OUTER JOIN [Inv_AeX_OS_System] T2 ON T0.[_ResourceGuid] = T2.[_ResourceGuid] LEFT OUTER JOIN [Inv_IUH_SW_WinVer] T3 ON T0.[_ResourceGuid] = T3.[_ResourceGuid]

ORDER BY T0.[Name] ASC

[Migration User]

such as Vcomputer, which has a 'ismanaged' field. ISMANAGED=1 means 'active', =0 means inactive.

The tie in will be vcomputer.guid. In fact vcomputer could almost replace your Inv_Aex_AC_Indentification.

There are other options, but that is the easiest.

[Migration User]

You can also do a join on the vasset class, which has a .Status field and then do a Where parameter for vasset.[Status] = Active.

[Migration User]

and that worked, and is now only displaying managed items. However, does active & managed mean the same? Can I have a computer that is disposed of, still being managed? Or, can I have a PC listed that is not managed, but active?

@nicoled18  I'm just learning, so where is the vasset class? I'm using the advanced query builder, and I don't see anything in the available objects pane matching that description.

[Migration User]

It's not available as a standard data class.  If you're in the query builder, under the Query tab, click Actions  > Add Joins.  Then you can type in vasset to find the vasset data class.  You can choose LeftOuter, then type vasset and select the match in the drop down box that populates.  For On, just choose Computer.Guid in the left hand and _ResourceGuid in the right.

Once you create that join, you can go to the Fields tab and vasset will now be available for you to add fields.

Hopefully that made sense.  If not, I can attach some screenshots for you.

[Migration User]

it reaches your 'retire\purge' after 'x' number of days. Then at that point it is either deleted or set to retired, which either way causes the record to drop or to be set to '0'. ISMANAGED='0' can also refer to discovered systems that don't yet have the agent.

[Migration User]

vComputer only contains computers with an 'Active' status.  IsManaged = 1 is still true for 'Retired' computers as well so that won't be sufficient.

Try this:

 

 

SELECT T0.[Name] AS 'Name', T2.[OEM OS] AS 'OEM OS', T0.[OS Name] AS 'OS Name', T1.[OS Type] AS 'OS Type', T3.[System Type] AS 'OS Version', T0.[OS Revision] AS 'OS Revision', T4.[ProductSuite] AS 'ProductSuite', T0.[System Type] AS 'System Type' 

FROM vComputer T0

INNER JOIN [Inv_AeX_AC_Identification] T1

   ON T1._ResourceGuid = T0.Guid

LEFT OUTER JOIN [Inv_LPH_Asset_Details] T2

   ON T1.[_ResourceGuid] = T2.[_ResourceGuid] 

LEFT OUTER JOIN [Inv_AeX_OS_System] T3

   ON T1.[_ResourceGuid] = T3.[_ResourceGuid] 

LEFT OUTER JOIN [Inv_IUH_SW_WinVer] T4

   ON T1.[_ResourceGuid] = T4.[_ResourceGuid]

WHERE T0.IsManaged = 1
 

ORDER BY T0.[Name] ASC

[KSchroeder]

We use the AC_Identification [Client Date] column in almost every collection and report we create.  In your WHERE clause, add the following (or similar):

WHERE
.....
AND DATEDIFF(dd, T1.[Client Date], GETDATE()) < 30 -- substitute whatever # of days you want to mean "active" 

This compares the "Client Date" column (sent by basic inventory, which should be sent every day) to the current date from GETDATE(), and only includes computers where the difference is less than 30 days.