aggregate daily data by hour over a 15 or 30 day period

JonT shared this question 2 months ago
Answered

Hi, I am trying to create a report for a server metric that is aggregated by hour (0-24) for a period of 30 days. When I aggregate the attribute value in the chart being created, it seems inaccurate. It shows the same number for each server across the 24 hour period. I am guessing that I have it configured wrong either in the query or the chart or both and am looking for help as to how to create the chart in order to see the data as expected.


Application Version:7.4
Build:20180226


/5f670ee0954d1f281eb511e225fd07e3


It is entirely possible the data is correct, but it is hard to imagine that every hour has the same aggregated value for the period (15 days for this one). We have another reporting tool that paints a very different picture where one hour is a value of 3, next hour is 10, next hour is 0 and so on for 24 hours. This would suggest the server has been running a particular process for x number of days for the entire day. The metric being collected is a configuration that sets a 1 for process running and 0 where it is not. So the collection of 1s over the course of a day broken down by hour would be where we want to aggregate the values for the 15 or 30 day period.

I tried just dropping the metric in the Color section, but it yields the same result as the analysis. Does it make sense what I am asking? Am I doing it incorrectly? The SQL of the report is below. I don't know the way to extract just the hour in an SQL statement, so the hour designation is set in the UI by formatting the attribute to display 24 hours. Any help is appreciated.


SELECT DISTINCT

"VW_CONFIGDATA"."ATTRUNIQUENAME",

"VW_CONFIGDATA"."DEVICENAME",

SUM("HOURLY_DATA"."VALUESUM") / SUM("HOURLY_DATA"."VALUECOUNT"),

MAX("HOURLY_DATA"."VALUEMAX"),

MIN("HOURLY_DATA"."VALUEMIN"),

"HOURLY_DATA"."TIMERECORDED"


FROM "HOURLY_DATA"


INNER JOIN "VW_CONFIGDATA"

ON(

"VW_CONFIGDATA"."SERVER_ID" = "HOURLY_DATA"."SERVER_ID"

AND "VW_CONFIGDATA"."MOTYPEID" = "HOURLY_DATA"."MOTYPEID"

AND "VW_CONFIGDATA"."ATTRID" = "HOURLY_DATA"."ATTRIBUTEID"

AND "VW_CONFIGDATA"."ITEMID" = "HOURLY_DATA"."MOINSTID"

)

WHERE (

"VW_CONFIGDATA"."DEVICENAME" LIKE '%2.qa%'

AND "HOURLY_DATA"."TIMERECORDED" >= TRUNC(sysdate)-15

AND "VW_CONFIGDATA"."ATTRUNIQUENAME" LIKE 'PROCCOUNT'

)

GROUP BY

"VW_CONFIGDATA"."ATTRUNIQUENAME",

"HOURLY_DATA"."TIMERECORDED",

"VW_CONFIGDATA"."DEVICENAME"


ORDER BY

"HOURLY_DATA"."TIMERECORDED"

Comments (7)

photo
1

Hi Jon,


Thanks for your message.


Firstly, I would advise against using an 'Auto Chart' to create a Chart. This feature is more useful as a Data Exploration step, rather than the final step in creating a Chart.


Following on from that, I'm a little unclear on what you are trying to measure in the Chart you have depicted. Typically, when dealing with Time Series analysis I would expect to see a Line Chart or Stacked Bar Chart, with the horizontal axis depicting time, and the vertical axis depicting a metric. You would then separate the series (i.e. Age Range, Sex, Demographic) using a separate aesthetic, generally another line in a Line Chart, or another colour in a Stacked Bar Chart. I would reference this excellent article to explore different ways of depicting your data.


In your example, you are using a categorical variable 'Device Name' on the Vertical Axis which I believe to be incorrect. I believe you want the 'SUM(...' to be on the Vertical Axis, and the Device Name to be an additional aesthetic like colour.


Let me know if this gets the results that you are after.


Kind regards,

Simon

photo
1

Hi Simon,

I ran it the way you mentioned and it shows up like this

/5b93c883ff33af297f43a57a4fd6db03


I think the chart is how it should be. The idea is that we have 10 servers listed on the vertical axis and time on the horizontal 0-23 hour.

This is the current report we would like to pull from this reporting tool. As it shows, it is capturing activity by hour over a 30 day period. We added monitoring of the service to capture running as a 1 and not running as a 0. If we had a server that had this service run at 6AM for 20 days and at 4PM for 4 days, we would expect to see it have a block on the cart with a value of 20 and a value of 4 in the chart. Is it possible to run a report like this with Yellowfin?

/28cd439d7cbbab391d73bdb179fb8531

photo
1

Hi Jon,


You are still using Auto Chart, which isn't going to give you much control with the Chart. You can go ahead and change it by clicking the little arrow next to its name.


I suggest changing the Chart to a Heat Grid and swapping the Vertical Axis to your server field, and changing the colour to your metric (no. of records). I do not believe there is any need to perform set analysis of your metric.


Let me know if this gives you the result you are after.


Kind regards,

Simon


/62630abb44e789251d5fe01992ae9e95

photo
1

Alright, well it looks like it was handling it ok, I got the same output. :) Guessing it's working as intended? If so, thank you for the help and I appreciate the patience. If there is nothing else to try, I will go with this and see if the data changes with more servers added when we get there.


/8b30433edd615946ff71cb6b7c0176f5

photo
1

Hi Jon,


Yes, I would say that it's working as intended and this is what your data looks like!


Let me know if you have any further queries that I can help answer, otherwise, I will go ahead and mark this question as closed.


Kind regards,

Simon

photo
1

Hi Jon,


I'm just messaging to let you know that I will be marking this as answered for the moment.


Please feel free to respond here if you have any further questions and I will get back to you shortly.


Kind regards,

Simon

photo
1

Hey Simon,


Thank you. Had internet issues from the weather last week, was struggling. We can mark it answered.