Software Management Group

I have reached a bit of a dead end with our internal support team.  I am one of the SAM analysts a have requested a report which the team cannot find a solution for.  All the inbuilt reports do provide information but are very clunky to use as you have to drill down or select each metered product in turn.

To speed up data return (about 2 hrs to extract the data for every product) I have requested a report that lists machine name, software product, last used date and installed date. 

The only difference that I am requesting is that the report returns all metered software products in one go (approx 100 products).  I understand that this will cause the report to fail as it exceeds the default 50,000 row limit of the console but wish this report to be exported so we can run automated analysys on it.

something that big I just run in sql on the sql server - also its real tough to do all software at once. I add run time data into only a few reports for individual business owners. This way the report is smaller and the data can be very focused on that software 

Here is a example bsed on a individual exe name 

SELECT DISTINCT 
ac.Name,  ac.Domain, ac.FQDN, ac.[OS Name], 
           convert(varchar, ac.[client date], 101) as 'Basic Scan Date',
           det.Name, det.[Path],  
 left (RIGHT(det.Path, 7),3) as 'Version Number',
vms.[Run Count],
vms.[Total Run Time],
vms.[Last Start] AS [Last Run],
vms.[Month Year], 
vms.[peak memory], 
vms.[average cpu usage]

    FROM dbo.Inv_AeX_AC_Identification ac
    left join vAsset on vasset._ResourceGuid = ac._ResourceGuid
     left join dbo.Inv_Installed_File_Details det on det._ResourceGuid= ac._ResourceGuid
    LEFT JOIN Inv_Monthly_summary vms ON  vms.FileResourceGuid = det.FileResourceGuid
        and vms._ResourceGuid =ac._ResourceGuid
    
                WHERE            det.Name ='tableau.exe'    
                                        and ac.[Client Date]> GETDATE ()-90
                order by ac.Name asc, det.Name asc,  vms.[Month Year] asc