Ever had these thoughts....
How many open/closed incidents did I have last week, month?
How can I trend on the Quantity of something?
What has been my trend of loaner computers in the past months?
Recently I came across a scenario where a customer wanted to report on how much hardware they had day to day, and week to week. My first thought was to utilize the metrics plugin to accomplish this. I thought it was weird that it did not have some sort of "Count" capability already in it. I decided to build it in. I added a metric definition type of "Count". I wanted it to work and integrate very closely to the existing metric definition and instances. About 1/2 of the way into the development of this functionality I realized a big block - the Database View! Each metric instance has an id that points to the task, incident, problem, etc., sys_id that it was created from. When running a report with the database view ONLY records that share similar fields to each other (based on the database view definition) are returned. Since my "Count" instance is not related to any one record, I could not populate this field properly. Thus no report, and not useful.
As a result, I was notified of another way to accomplish this task, thanks to Deborah, and Jessi for pointing this out to me. There are two "Trend" modules under the "System Scheduler" application. The first being "Trend Jobs", and the second "Trend Data". You will have to activate these and possibly modify the roles so an admin can see them.
I found it easiest to "insert" those modules in the "Metrics" application to keep all of this type of functionality in the same place.
Trend Jobs:
Create new jobs from within this form. It's really easy, give it a title, specify a table, identify the delay (hourly or daily), and give it a filter. You will notice the filter is our standard filter used in many other places.
If you want to count all open incidents, specify the incident table, and choose active = true in the filter as shown below.
Trend Data:
Similar to Metric Instances, Trend Data is the data collected from the trend job. It gives you the name, when it was collected, collected dom (Day of Month), collected dow (Day of Week), collected month, collected year, and the value. The value if the count of whatever you specified in the job table/filter.
One caveat I found in using the Trend Data (sys_trend) table in reports, is I could not find this table in the list. If someone knows how to add it in there by default, let me know. In order to get around this, click on the "Trend Data" module to list the records. Right-click on a column and select bar chart or pie chart. By doing so you can then edit the report and save it.
With this information you can then create trend reports and get your "snapshot", "point-in-time", type count reports. You could then report on sums, averages for a time period, etc.
If anyone has any clean ideas how to merge this functionality back into metrics, let me know. I didn't want to just insert any random id to "make this work". I like to keep it clean where possible.
Enjoy.
<script></script>
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-22945975-1']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
17 Comments
