Kaseya Community

Help with another SQL Query (Really useful query if it can work)

  • Hi All, 

    I am looking for a query that will show all performance counter metrics for all machines across a period of time.

    The problem i can see with all of this is that there are numerous disparate tables in the database that hold the information, but joining that information is hard. 

     

    At the moment, i can see tables that log all performance data for a single day, this is fine as i can use it to practice my analytics. 

    So far this is the query i have 

     

     

    select * 
    from ksubscribers.dbo.monitorCounterLog20120208
    join ksubscribers.dbo.monitorCounter
    on ksubscribers.dbo.monitorCounterLog20120208.monitorCounterLogId = ksubscribers.dbo.monitorCounter.monitorCounterId
    inner join ksubscribers.dbo.vAgentConfiguration
    on ksubscribers.dbo.vAgentConfiguration.agentGuid = ksubscribers.dbo.monitorCounterLog20120208.agentGuid

    The logic of the query is:

    1. Select everything from Monitorcounterlog (a daily collection of performance data). This table has to be joined with the MonitorCounter table to get the name of the performance counter and the log data.

    2. Also do a join on the vAgentConfiguration on AgentGuid to get the name of the machine matched up against the machine logged in the Monitorcounterlog table. 

    It returns every row but because of the joins - some of the columns are full of NULLS - the important ones - the performance counter names.

    My suspicion is with that query is that the join is the wrong way around, or, more likely, i have gotten the relationships completely wrong. I thought you could join three tables like i did, where you can join the tables on different columns if they matched on each join (a.monitorcounterlog = b.monitorcounterlog & a.agentguid = b.agentguid).

    Is there a SQL gun out there that could see about making this query work? 

     

    Has anyone done this so i don't have to keep toiling away? Eager to hear the communities thoughts. 

     

     



    [edited by: Mark Boyd at 1:55 PM (GMT -8) on 2-9-2012] edited to add picture
  • Your query works fine for me as is. Have you checked to see that you have valid data in all three tables?

  • Hi Zippo - sorry to clarify

    All those NULLS are what i am trying to avoid, they should have the names and data of the performance counter metrics (name - type - etc)

    I don't know if the data is valid across all three tables

    MonitorCounterLog and MonitorCounter have MonitorCounterID in common, MonitorCounterLog and vAgentConfiguration have AgentGuid in common.

    So maybe it's just i'm using the wrong tables. The query executes, but the results aren't what i was after. Hope this clarifies.

    Cheers Mark.

  • The query executes fine for me and returns data, not nulls, so that's what prompted my question to you.

  • Ahh sorry again

    select *

    from ksubscribers.dbo.monitorCounterLog20120208

    left join ksubscribers.dbo.monitorCounter

    on ksubscribers.dbo.monitorCounterLog20120208.monitorCounterLogId = ksubscribers.dbo.monitorCounter.monitorCounterId

    inner join ksubscribers.dbo.vAgentConfiguration

    on ksubscribers.dbo.vAgentConfiguration.agentGuid = ksubscribers.dbo.monitorCounterLog20120208.agentGuid

    Try that query - the left join gets all the data but NULLS the performance monitor data.

  • Yes, with the "Left Join" it fails for me too. But try using just a "Join" in place of the "Left Join" and see what you get...

  • It turns out i wasn't querying data consistant across the tables - i needed monitorcounter tables with agent guids. BUT, it is still not doing what i wanted.

    select *

    from ksubscribers.dbo.monitorCounterLog20120208

    join ksubscribers.dbo.monitorCounter

    on ksubscribers.dbo.monitorCounterLog20120208.monitorCounterId = ksubscribers.dbo.monitorCounter.monitorCounterId

    join ksubscribers.dbo.monitorDeploymentSummary

    on ksubscribers.dbo.monitorDeploymentSummary.MonitorSetID = ksubscribers.dbo.monitorCounter.monitorSetId

    join ksubscribers.dbo.vAgentConfiguration

    on ksubscribers.dbo.vAgentConfiguration.agentGuid = ksubscribers.dbo.monitorCounterLog20120208.agentGuid

    That query - returns everything , but i think it produces a cartesian product (www.fluffycat.com/.../Cartesian-Joins)

    It got to 5 million rows and kept climbing - so i stopped it - if you want to test this be EXTREMELY careful, however your opinions would be appreciated.

    I now believe i just have the join syntax right but the data is correct.

    Any ideas from SQL Query joining experts? Any ideas Zippo?

  • That'll take me a bit longer to parse - someone else will probably be quicker at it than I am...

  • I got it! This works a treat!

    select *
    from ksubscribers.dbo.monitorCounterLog20120208
    inner join ksubscribers.dbo.monitorCounter
    on ksubscribers.dbo.monitorCounterLog20120208.monitorCounterId = ksubscribers.dbo.monitorCounter.monitorCounterId
    inner join ksubscribers.dbo.vAgentConfiguration
    on ksubscribers.dbo.monitorCounterLog20120208.agentGuid = ksubscribers.dbo.vAgentConfiguration.agentGuid
    order by ksubscribers.dbo.vAgentConfiguration.machName

    This will give you the entire days performance data for all servers - with 180 servers, it returns 60 Megabytes of data, i am going to work on the column filtering and look at creating a stored procedure that runs each day and pumps all this data into one big table. It is going to be amazing having a full years data of performance stuff to look at! Just have to get an understanding of how the business intelligence stuff works out - how do i query a table with 24Gigabytes of data into it! 

    People reading this thread from here on in. Use this query and play with it - where Monitorcounterlog20120208 needs to be changed to reflect a date you want to query. 

    This query is also a brilliant way to tell how efficient your performance counter metrics are.

  • Last one here i promise.

    SELECT    ksubscribers.dbo.vAgentConfiguration.machName AS MachineName, ksubscribers.dbo.vAgentConfiguration.groupName AS GroupName,
        ksubscribers.dbo.monitorCounter.name AS CounterName, ksubscribers.dbo.monitorCounter.description AS CounterDescription,
                        ksubscribers.dbo.monitorCounterLog20120208.counterValue AS CounterValue,
                        ksubscribers.dbo.monitorCounterLog20120208.eventDateTime AS EventTime
    FROM         ksubscribers.dbo.monitorCounterLog20120208 INNER JOIN
                        ksubscribers.dbo.monitorCounter ON
                        ksubscribers.dbo.monitorCounterLog20120208.monitorCounterId = ksubscribers.dbo.monitorCounter.monitorCounterId INNER JOIN
                        ksubscribers.dbo.vAgentConfiguration ON
                       ksubscribers.dbo.monitorCounterLog20120208.agentGuid = ksubscribers.dbo.vAgentConfiguration.agentGuid
    ORDER BY MachineName, CounterName, CounterDescription

  • Interesting work, Mark. Thanks for posting the query.

  • Thank you Mark for your queries.

    Anyone knows how to aggregate  the info from all these monitorCounterLogyyyyddmm for the last n days?

    Say for a week or a month.

    I was trying to pull some data for %Processor Time  (and I found  IDs) but stuck with creating dataset which will pull data for a period of time.

    monitorCounter.monitorCounterId = '1201'

    --monitorcounterids for Processor time 1201, 1219, 1228

    Kind regards,

    Vadim Gonzalez

  • Try this:

    DECLARE @query VARCHAR(MAX)

    SELECT @query = COALESCE(@query + ' UNION ALL ', '') + 'SELECT monitorCounterId, AVG(counterValue) AS AvgCounterValue, CAST(MIN(eventDateTime) AS DATE) AS eventDate FROM ' + TABLE_NAME + ' WHERE monitorCounterId = 1201 GROUP BY monitorCounterId'

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE = 'BASE TABLE'

     AND TABLE_NAME LIKE 'monitorCounterLog%'

     AND NOT (TABLE_NAME LIKE 'monitorCounterLog'

            OR TABLE_NAME LIKE 'monitorCounterLogSummary')

    EXEC (@query)

    That will output the Average counter value per day for a given counter for every day you have a log table. You can output that into a temp table and query for a specific day if required. It should get you close if this isn't exactly what you want.

  • Hi Jondie,

    Thank you for your prompt response.

    is there any way to pull this info like on the screen attached so we could  have datapoints with values for counter and server. (this is an example for querying data for 1 day  )

    Kind regards,

    Vadim Gonzalez

    --performance graphs
    --29.11.2016
    SELECT
      NEWID() AS id,
      vAgentConfiguration.machName AS MachineName,
      vAgentConfiguration.groupName AS GroupName,
      monitorCounter.monitorCounterId AS CounterId,
      monitorCounter.name AS CounterName,
      monitorCounter.description AS CounterDescription,
      monitorCounterLog20161128.counterValue AS CounterValue,
      monitorCounterLog20161128.eventDateTime AS EventTime,
      kasadmin.org.orgName,
      CASE
    WHEN ((vMachine.OsType = '2000' and vMachine.OsInfo not like 'Professional%') OR vMachine.OSType = '2003' OR vMachine.OSType = '2008' OR vMachine.OSType = '2012') THEN 'Server'
    ELSE 'Workstation'
    END AS machine_type,
        vMachine.OsType
    FROM monitorCounterLog20161128
    INNER JOIN monitorCounter
      ON monitorCounterLog20161128.monitorCounterId = monitorCounter.monitorCounterId and monitorCounter.monitorCounterId = '1201'
       --monitorcounterids for Processor time 1201, 1219, 1228
     
    INNER JOIN vAgentConfiguration
      ON monitorCounterLog20161128.agentGuid = vAgentConfiguration.agentGuid
    INNER JOIN machGroup
      ON vAgentConfiguration.groupName = machGroup.reverseName
    INNER JOIN kasadmin.org
      ON machGroup.orgFK = kasadmin.org.id
    INNER JOIN vMachine
      ON monitorCounterLog20161128.agentGuid = vMachine.agentGuid
    --Filtering by OS type
      Where  ((vMachine.OsType = '2000' and vMachine.OsInfo not like 'Professional%') OR vMachine.OSType = '2003' OR vMachine.OSType = '2008' OR vMachine.OSType = '2012')
  • Hi everyone,

    Not sure if this is considered a contribution, however, i'm fairly certain if you're on 9.0 or above, there is a rollup of the dates in one table for performance counters. I'll have a look for it later but it'll be in a view somewhere, you don't need to do joins on counter logs. I have since moved right away from such complicated queries. My best suggestion is to reach out to the guys at BrightGauge and use even the most basic of reporting to get you by.

    Mark