Custom Report - Microsoft

[raviladhawala]

Hi Team,

How to create a custom report with below requirements. one about servers and one about clients with following information:

Servers:

                server name

                physical or virtual

                number of CPUs and Cores à if physical

                number of vCores à if virtual

                Installation - Application name

                Application – Product

Application – Edition

Application - Version

                device status

                last inventory date

Clients:

                computer name

                user that access it (last logged on and primary user)

                physical or virtual

Installation - Application name

                Application – Product

Application – Edition

Application - Version

                device status

                last inventory date

[erwin_lindemann · Accepted solution]

Assuming that you are working with FNMS in an on-premises environment, the following SQL code should bring you close to a solution:

SELECT
         cc.ComputerName
       , cct.DefaultValue                   AS [ComplianceComputerType]
       , ccs.DefaultValue                   AS [Status]
	   , cc.InventoryDate
	   , cc.NumberOfProcessors
	   , cc.NumberOfCores
       , sti.Publisher               
       , sti.ProductName                    AS [Product]
       , sti.SoftwareTitleName              AS [Application]
       , sti.SoftwareTitleVersion           AS [Version]
       , sti.EditionName                    AS [Edition]
       , stc.DefaultValue                   AS [Classification]
FROM [ComplianceComputer]              cc
JOIN [ComplianceComputerTypeI18N]      cct 
	ON cct.ComplianceComputerTypeID = cc.ComplianceComputerTypeID
JOIN [ComplianceComputerStatusI18N]    ccs 
	ON ccs.ComplianceComputerStatusID = cc.ComplianceComputerStatusID
LEFT JOIN [InstalledSoftware]          isw 
	ON isw.ComplianceComputerID = cc.ComplianceComputerID
JOIN [SoftwareTitleInfo]               sti 
	ON sti.SoftwareTitleID = isw.SoftwareTitleID
JOIN [SoftwareTitleClassificationI18N] stc 
	ON stc.SoftwareTitleClassificationID = sti.SoftwareTitleClassificationID
ORDER BY
	  cc.ComputerName
	, sti.Publisher
	, sti.SoftwareTitleName

I would not recommend to convert this into a report, as it will return a lot of data.

Performance in a report will be slow, and the number of rows returned will probably exceed the default maximum of rows for reports (about 80K).

[SinghJ]

we can save it as store proc in database. So, when every required we can run it and we can add filter basis publisher also.

Regards,