AI Docs

The use of Custom Groups

3 min read

certero logo_cropped_png-01 1.png

Custom Groups is an early feature introduced to support advanced data queries across the Certero solution.

The Certero platform now has a built-in API that customers can use for or complex reporting use cases. To learn how to get started with the API, search the help guides for the key words API and Power BI . Also refer to a modern custom reporting feature which addresses many of the use cases of Custom Groups:

How to create a Report | v8.1+

Why is this feature there?

Use of Custom Groups should only be considered when the data you require is not available through the standard reporting, searching, filtering, Dynamic Groups and exporting capabilities of the Certero user interface. Use of the standard platform features, and capability should always be considered first.

What is it?

A Custom Group is a SQL 'SELECT' statement that must be aligned to the underlying Certero Management Platform database schema. This schema is not published or documented. The feature is present to allow, under certain circumstances, a Certero consultant to provide customers with an alternative method of retrieving data.

I think a Custom Group is what I need - how do I make use of this feature?

To invoke this feature, you will need to log a service request via the Certero Help Desk detailing your data query requirements, ideally providing an example of the columns of data you require with some example data in, for example, Microsoft Excel format. This should be attached to the service request.

Certero can provide professional service to prepare a SQL statement that can meet your requirements, which can be an additional chargeable service. After logging such a request with the Certero Help Desk, your Certero Account Manager will normally make contact with you to discuss your options.

What does a Custom Group look like?

The main part of a Custom Group is the SQL Query . This will be prepared by a Certero consultant proficient in the Microsoft SQL database query language who understands the Certero Management Platform database schema. The following are examples of what the SQL Query element of a Custom Group can look like that are compatible with release 6.2 of the Certero Management Platform.

Microsoft Project Standard or Professional Editions not used in the last 3 months:

SELECT
[WSSP].[WindowsSystemObjectId]

FROM
[Inventory].[WindowsSystemSoftwareProduct] AS [WSSP]

INNER JOIN
[Inventory].[SoftwareProduct] AS [SP] ON
[WSSP].[SoftwareProductObjectId] = [SP].[SoftwareProductObjectId]
AND [WSSP].[IsDeleted] = 0

INNER JOIN
[Core].[Object] AS [O] ON
[WSSP].[WindowsSystemObjectId] = [O].[ObjectId]

WHERE
[O].[Status] = 0
AND ( [SP].[DisplayName] LIKE '%Project Professional%' OR
[SP].[DisplayName] LIKE '%Project Standard%' )
AND [SP].[Publisher] LIKE 'Microsoft%'
AND DATEDIFF(MONTH, [WSSP].[InstallDate], GETDATE()) > 3
AND NOT EXISTS
( SELECT NULL FROM [AppsMonitor].[MeteredFile] AS [MF]

INNER JOIN
[AppsMonitor].[ScannedFile] AS [SF] ON
[MF].[ScannedFileId] = [SF].[ScannedFileId]

WHERE [MF].[ComputerSystemObjectId] = [WSSP].[WindowsSystemObjectId]
AND [SF].[Filename] = 'Winproj.exe'
AND [SP].[Version] like SF.FileVersionMajor + '.%'
AND DATEDIFF(MONTH, [MF].[DateRun], GETDATE() )
<= 3 )

 

Identify Windows computers with an active Certero inventory record where the associated Active Directory computer object record has been disabled:

SELECT [CSI].[ComputerSystemObjectId]

FROM
[Core].[ComputerSystemInventory] [CSI]

INNER JOIN
[Core].[Client] [C] ON
[CSI].[ComputerSystemObjectId] = [C].[COMPUTERSYSTEMOBJECTID]
INNER JOIN
[Core].[Object] [O] ON
[CSI].[ComputerSystemObjectId] = [O].[ObjectId]
WHERE
[O].[Status] = 0
AND [C].[ActiveDirectoryStatus] = 0

Custom Groups can only be administered with the SYSADMIN permission level.