SCOM 2019 Datawarehouse SQL query for Agent Count for each period of time

Question

What would be a good SQL Query for SCOM 2019 data warehouse to get the Agent Count for each period of time.

Answer 1

Hi @D D ,

I played a bit and could prepare this for you:

SELECT DisplayName,DWCreatedDateTime   
FROM [OperationsManagerDW].[dbo].[vManagedEntity]   
WHERE [FullName] LIKE 'Microsoft.SystemCenter.HealthService:%'  
AND DWCreatedDateTime between '2022-01-01' and '2022-09-13'  

this will give you all agents installed between '2022-01-01' and '2022-09-13'. You can adjust the dates according to your needs. You need to count the rows in the output.

If you want to get the count only, then you can use the "COUNT()" function with the query:

    SELECT COUNT(*)  
    FROM [OperationsManagerDW].[dbo].[vManagedEntity]   
    WHERE [FullName] LIKE 'Microsoft.SystemCenter.HealthService:%'  
    AND DWCreatedDateTime between '2022-01-01' and '2022-09-13'  

This will return the count of the agents for the given period.

I hope I was able to help you out.

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)
Regards
Stoyan Chalakov

Answer 2

Hi @D D ,

the second query returns the total agent count for the specific period. In your case:

     SELECT COUNT(*)  
     FROM [OperationsManagerDW].[dbo].[vManagedEntity]   
     WHERE [FullName] LIKE 'Microsoft.SystemCenter.HealthService:%'  
     AND DWCreatedDateTime between '2022-03-01' and '2022-08-01'  

This returns the total count of agents with a "DWCreatedDateTime" between MArch and August 2022.

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)
Regards
Stoyan Chalakov

Answer 3

Hi,

Here are some similar threads for your reference:

SCOM Agent list and count from datawarehouse (not operations database)
Count the number of the SCOM agents which have critical alerts for a specific period

Thanks for your time,

Best regards,
Simon

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.