Reporting Group

I found the built in report for "Computers that have not reported inventory in last N days)" (Reports->All reports->Discovery and Inventory->Inventory->Cross-platform->Inventory Agent->Inventory Diagnostics), which works great.

I need to be able to run something like this (machines that haven't checked in last 90 days), but I also need to include username.

I've found some old threads on this (including one of mine and this one), but can't figure out how to get it work (I don't have asset management installed).  Any help would be greatly appreciated!

When are you retiring assets ? if your auto retiring at 90 days they the following report is usless. if 120 days they this will help. 

I used to create a montly report off this data and send it to all my tech managers out in the field to go look for them. 

Try this one 

select distinct   
  
  ac.name,

ac.[OS Name] AS 'OS Name', 

  convert(varchar, ac.[client date], 101) as 'ScanDate', 
  vasset.Manufacturer,
vAsset.Model,
vAsset.[Serial Number],
vc.[IP Address],
vAsset.[Status], 
Prime.[primary user],                                                                                                
T20.[Given Name] as 'First Name',                                                                                                                                        
ISNULL(T20.[Surname],'') AS 'Last Name',                                 
T20.[office telephone],                                                    
T20.department
   

 FROM dbo.Inv_AeX_AC_Identification ac
 left JOIN      vcomputer i ON  i.Guid = ac._ResourceGuid 
left join vAsset on vasset._ResourceGuid = ac._ResourceGuid
left join vComputer vc on vc.Guid =ac._ResourceGuid 
 
left join (SELECT     _ResourceGuid, MAX([user]) AS [primary user]  
    FROM         dbo.Inv_AeX_AC_Primary_User   
    WHERE     ([User] IS NOT NULL) AND ([User] <> '')     GROUP BY _ResourceGuid  ) Prime                          
    on ac._resourceguid =  Prime._ResourceGuid
LEFT   JOIN (select distinct vuser.*   from  vuser where [Given Name] !='' )t20 
    ON Prime.[primary user] =   T20.name 
  
        
      
WHERE  
--(ac.Name like  'c70%' and ac.Name like 'l70%')and
 --and (vAsset.[Serial Number]not like 'vm%' OR vasset.[Serial Number]is null)
vasset.status !='retired'
and ac.[Client Date]< GETDATE() -90
 

--and (ISNULL(office2.office, office1.Software)) is not null
order by  ac.[Name]asc

Hi Sally,

I took mentioned report and pimped it for your needs:

 
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT DISTINCT   
    i.[Guid],
 i.[Name][Computer Name],    
 MAX (r.[Collection Time]) [Last Collection Date] ,
i.[user]
FROM dbo.vComputer i  
 JOIN dbo.ScopeMembership sm
  ON sm.[ResourceGuid] = i.Guid
  AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
 JOIN dbo.Inv_Inventory_Results r    
  ON r.[_ResourceGuid] = i.Guid  
    AND r.Agent = 'Inventory Agent'
 JOIN dbo.CollectionMembership cm  
  ON cm.ResourceGuid = i.[Guid]
 JOIN dbo.vCollection it  
  ON it.Guid = cm.CollectionGuid  
WHERE DATEDIFF(day,r.[Collection Time],GETDATE()) >= %Days%   
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')   
AND ('%Domain%' = '%' OR i.Domain = '%Domain%')
AND (('%OS Platform%' = 'Windows' AND i.[System Type] LIKE 'Win%')
 OR ('%OS Platform%' = 'Unix/Linux' AND i.[System Type] LIKE 'Unix%')
 OR ('%OS Platform%' = 'Macintosh' AND i.[System Type] LIKE 'Mac%')
 OR ('%OS Platform%' = 'Other' AND i.[System Type] NOT LIKE 'Win%'
  AND i.[System Type] NOT LIKE 'Unix%'
  AND i.[System Type] NOT LIKE 'Mac%')
 OR ('%OS Platform%' = '%')) 
AND it.[Guid] =  '%Filter%'
GROUP BY i.Guid,i.Name,i.[user]
ORDER BY MAX (r.[Collection Time])     
 

Please have a try.

Regards

Thanks for the quick feedback.  Does this SQL query require Asset management maybe?  When I try to run it I get following in console

"An error occurred on the page. Please check the log for more details... This DataSource is not in a runnable state."

I believe this is what happened last time I tried to get this report working.

Quite a long error in log viewer, starting with

"

Failed to refresh the LinkRegion 'UserConfigurationPanel'.
Altiris.Reporting.Common.Exceptions.ReportSaveException (Altiris.Reporting.UI): Failure saving changes to the Report.
   at Altiris.Reporting.UI.ReportEditControl.Save()
   at Altiris.Reporting.Controls.StandardReportEditControl.editPanel_Refresh(Object sender, LinkRegionRefreshArgs e)
   at Altiris.Reporting.UI.Controls.LinkCallbackForm.OnRefresh(LinkRegionRefreshArgs args)
   at Altiris.Reporting.Common.ControlLinking.Controls.LinkRegion.RaiseCallbackEvent(String eventArgument)

Altiris.DataSource.Exceptions.DataSourceSchemaException (Altiris.Reporting.DataSource): An unexpected SQL error occurred when retrieving the schema from the RawSqlDataSource.

"

Asset Management is not required for that SQL. What is required is that you setup the parameter for %Days%.

I have had numerous issues if I try to setup the parameter after I copy the SQL into a new report.

To overcome this, I create the parameter first, then copy the query into the builder. You will get a "yellow" bar error when you switch between the 2 tabs, but it will let you continue. After you paste the query, make sure you switch from the "Parameterized Query" tab to the "Query Parameters" tab to add the parameter there as well. Save, and enjoy.

Hi,

I just cloned "Computers that have not reported inventory in last N days)"  and made changes to the query. IMHO just clone the report and paste my query over the original one.

Regards

That worked, thanks so much for replying again.  

Would'nt the last basic inventory or the last configuration fields be the most accurate fields to use to indentify in-active machine. The fields and data from the Inv_Inventory_Results table is from the last inventory. Which may or not work depending on the configured Inventory Plug-in policies.

I would think the last basic inventory field [Client Date] from the Inv_Aex_AC_Indentification table and/or the _eventtime from the Evt_NS_client _Config_Request table would be the most accruate in indenity inactive machines.

Here is a query that I use to indentify in-active machines in my environment I also have included the Last inventory collection field.

declare @days integer
set @days = 90

SELECT
   VC.Name AS COMPUTER,
   VC.[OS Name],
   vc.[User] as 'Logon User',
   vc.IsManaged,
   ISNULL(CAST(ai.[Client Date] AS NVARCHAR), '') as 'Last Basic Inventory',
   DATEDIFF(dd,ai.[Client Date],GETDATE())as 'Days since Last BI',
   ISNULL(CAST(cr.[Last Config Request] AS NVARCHAR), '') as 'Last Config Request',
   DATEDIFF(dd,cr.[Last Config Request],GETDATE())as 'Days since Last CR',
   ISNULL(CAST(ir.[Collection Time] AS NVARCHAR), '') as 'Last Inventory Collection',
   DATEDIFF(dd,ir.[Collection Time],GETDATE())as 'Days since Last IC'

FROM vComputer VC
left join ( select resourceguid, max(_eventtime) as 'Last Config Request'
From Evt_NS_Client_Config_Request group by resourceguid ) cr
   on cr.ResourceGuid = vc.Guid
left join Inv_AeX_AC_Identification ai on ai._ResourceGuid = vc.Guid
left join Inv_Inventory_Results ir on ir._ResourceGuid = vc.Guid
where DATEDIFF(dd,ai.[Client Date],GETDATE()) >= @Days
or DATEDIFF(dd,cr.[Last Config Request],GETDATE()) >= @Days

That is helpful, thanks.  I did have a few results on the previous report where machines had done config updates recently.

After looking at the vcomputer view, I added VC.[IP Address], to the select statement as IP address helps for us to track down machines (VLAN info).

I have a basic understanding of SQL, but I am not sure where to begin to get a better understanding of all of the tables and views available in CMS 7.x short of just poking around Management Studio?

I'd like to learn how to add Computer model to the report but not sure where that is in the CMS back end.

Sally,

The easiest way to find the table names for Inventory items is from Resource manager.  While in resource manager for a computer that has the information you want, click View, Inventory.  Navigate to the data you want to report on then click the Status tab.  On that tab you will find the database table name.  Inventory tables normally have a _ResourceGuid field you can use to join with whatever tables you want.  I usually start with the view vComputer since it has many of the common items you would report on for a computer like name, domain, ip address, OS info, user info, etc.

Joe

Sally

Joe is right. The Resource Manager will show most of the the table need to give you the info you want. How ever some tables/views are not listed.

For computer model you could use the vHWComputerSystem view and join it to the vcomputer table via the _resourceguid field...

I.e.

select
     c.name Computer
     ,c.[IP Address]
     ,isnull(m.[Model],'No Inventory Collected') [Computer Model]
from vcomputer c
left join vhwComputerSystem m
     on m._resourceguid = c.guid
--for subnet info you add the following
left join Inv_AeX_AC_TCPIP ip on ip._ResourceGuid = c.Guid
-- ip and mac join to eliminate duplicates for machines with duplicate ip address
and ip.[IP Address] = c.[IP Address] and ip.[MAC Address] = c.[MAC Address]
--- this gives you subnet info in subnet infor CIDR format
left join vSubnet sub on sub.Subnet = ip.Subnet and ip.[Subnet Mask] = sub.[Subnet Mask]
left join vItem i on i.Guid = sub.Guid

do not user Inv_AeX_AC_TCPIP - use Vcomputer for IP and MAC as the sql on the back end figures out for you the best IP address. 

IPV6 creates duplicates in the Inv_AeX_AC_TCPIP table 

All very helpful.  I sure wish the report builder in CMS was more SQL newb friendly that let you pick the fields, etc you want, but at least I have something to go off of now.

Thanks everyone.