The Law of Averages

The Law of Averages is not actually a law, but really just a lay term for some mathematical principles either completely unfounded in any mathematical law or borrowed from the Law of Large Numbers.  For more information, see this Wikipedia article.  However, this post is titled 'The Law of Averages' because I wanted to go into some detail about averaging averages.

In particular, I was asked this question: "If I have a data point for every minute of the data, I also have an average of 15 data points for every 15 minute period of the day, and I wanted to calculate the daily average, it would be better to use the 1 minute data versus the 15 minute averages (aka rollup data).  The average of the 15 minute averages would really be an average of averages and is therefore not accurate."

The reality is actually contrary to that thinking.  The average of averages of data is exactly the same as the average of the same source data.  To illustrate this, let's look at some data.

Below, we have random numbers between 0 and 100 to represent our data.  We have one data point for each minute of the day.  Every 15 minutes, an average is calculated.  For example, at 0:30 an average is calculated averaging the points from 0:30-0:44.

Time	1 Minute Data	15 Minute Average
0:00	37	51.6666666666667
0:01	74
0:02	49
0:03	21
0:04	59
0:05	15
0:06	32
0:07	69
0:08	40
0:09	26
0:10	87
0:11	38
0:12	96
0:13	79
0:14	53
0:15	60	25.8
0:16	35
0:17	7
0:18	24
0:19	46
0:20	3
0:21	22
0:22	1
0:23	39
0:24	9
0:25	34
0:26	0
0:27	42
0:28	34
0:29	31
0:30	4	33.8
0:31	5
0:32	81
0:33	36
0:34	35
0:35	9
0:36	36
0:37	41
0:38	76
0:39	93
0:40	21
0:41	12
0:42	10
0:43	21
0:44	27
0:45	5	48.8
0:46	42
0:47	83
0:48	48
0:49	18
0:50	79
0:51	24
0:52	78
0:53	19
0:54	5
0:55	52
0:56	100
0:57	39
0:58	70
0:59	70
1:00	14	44.0666666666667
1:01	45
1:02	82
1:03	34
1:04	74
1:05	90
1:06	33
1:07	10
1:08	43
1:09	24
1:10	96
1:11	12
1:12	17
1:13	62
1:14	25
1:15	96	54.7333333333333
1:16	19
1:17	91
1:18	58
1:19	76
1:20	24
1:21	86
1:22	35
1:23	48
1:24	31
1:25	67
1:26	72
1:27	9
1:28	45
1:29	64
1:30	79	40.4
1:31	68
1:32	0
1:33	10
1:34	30
1:35	23
1:36	65
1:37	74
1:38	53
1:39	78
1:40	4
1:41	3
1:42	38
1:43	78
1:44	3
1:45	91	47.6
1:46	85
1:47	44
1:48	4
1:49	6
1:50	13
1:51	39
1:52	93
1:53	52
1:54	35
1:55	49
1:56	69
1:57	67
1:58	64
1:59	3
2:00	11	56.8666666666667
2:01	72
2:02	54
2:03	45
2:04	16
2:05	17
2:06	75
2:07	96
2:08	96
2:09	62
2:10	80
2:11	55
2:12	96
2:13	34
2:14	44
2:15	73	46.6666666666667
2:16	57
2:17	21
2:18	61
2:19	28
2:20	11
2:21	16
2:22	1
2:23	86
2:24	23
2:25	64
2:26	89
2:27	46
2:28	38
2:29	86
2:30	42	45.8
2:31	15
2:32	7
2:33	3
2:34	40
2:35	41
2:36	8
2:37	66
2:38	1
2:39	85
2:40	59
2:41	89
2:42	94
2:43	42
2:44	95
2:45	64	57.6
2:46	58
2:47	6
2:48	14
2:49	17
2:50	100
2:51	85
2:52	72
2:53	93
2:54	58
2:55	100
2:56	32
2:57	53
2:58	36
2:59	76
3:00	4	38.7333333333333
3:01	19
3:02	32
3:03	14
3:04	84
3:05	26
3:06	17
3:07	65
3:08	20
3:09	15
3:10	26
3:11	52
3:12	79
3:13	31
3:14	97
3:15	8	55.6666666666667
3:16	47
3:17	87
3:18	66
3:19	23
3:20	88
3:21	28
3:22	53
3:23	87
3:24	66
3:25	32
3:26	71
3:27	87
3:28	10
3:29	82
3:30	84	54.9333333333333
3:31	81
3:32	54
3:33	88
3:34	77
3:35	98
3:36	2
3:37	15
3:38	92
3:39	72
3:40	16
3:41	45
3:42	4
3:43	60
3:44	36
3:45	84	63.4666666666667
3:46	97
3:47	87
3:48	29
3:49	53
3:50	11
3:51	84
3:52	83
3:53	76
3:54	64
3:55	50
3:56	36
3:57	83
3:58	38
3:59	77
4:00	3	64.1333333333333
4:01	79
4:02	90
4:03	91
4:04	46
4:05	53
4:06	26
4:07	77
4:08	46
4:09	62
4:10	60
4:11	93
4:12	78
4:13	92
4:14	66
4:15	35	37.1333333333333
4:16	81
4:17	42
4:18	50
4:19	14
4:20	26
4:21	72
4:22	4
4:23	15
4:24	65
4:25	14
4:26	9
4:27	10
4:28	38
4:29	82
4:30	14	51
4:31	48
4:32	62
4:33	36
4:34	89
4:35	3
4:36	53
4:37	58
4:38	86
4:39	71
4:40	49
4:41	67
4:42	20
4:43	96
4:44	13
4:45	0	47.3333333333333
4:46	91
4:47	64
4:48	28
4:49	96
4:50	23
4:51	37
4:52	37
4:53	77
4:54	15
4:55	13
4:56	98
4:57	7
4:58	58
4:59	66
5:00	48	48.6
5:01	22
5:02	5
5:03	76
5:04	96
5:05	5
5:06	10
5:07	3
5:08	34
5:09	56
5:10	82
5:11	96
5:12	58
5:13	80
5:14	58
5:15	48	48.1333333333333
5:16	30
5:17	50
5:18	71
5:19	12
5:20	12
5:21	98
5:22	24
5:23	41
5:24	21
5:25	66
5:26	24
5:27	99
5:28	73
5:29	53
5:30	8	37.3333333333333
5:31	67
5:32	17
5:33	0
5:34	0
5:35	37
5:36	51
5:37	33
5:38	28
5:39	58
5:40	3
5:41	74
5:42	79
5:43	90
5:44	15
5:45	56	55.7333333333333
5:46	82
5:47	66
5:48	64
5:49	69
5:50	94
5:51	81
5:52	23
5:53	22
5:54	41
5:55	16
5:56	89
5:57	47
5:58	74
5:59	12
6:00	70	49.2666666666667
6:01	72
6:02	76
6:03	28
6:04	62
6:05	28
6:06	46
6:07	62
6:08	61
6:09	34
6:10	52
6:11	69
6:12	12
6:13	37
6:14	30
6:15	79	51.8666666666667
6:16	35
6:17	74
6:18	71
6:19	12
6:20	52
6:21	72
6:22	6
6:23	89
6:24	16
6:25	17
6:26	30
6:27	64
6:28	76
6:29	85
6:30	47	42.8666666666667
6:31	44
6:32	46
6:33	46
6:34	2
6:35	83
6:36	12
6:37	24
6:38	47
6:39	94
6:40	32
6:41	70
6:42	70
6:43	24
6:44	2
6:45	77	54.2
6:46	1
6:47	27
6:48	47
6:49	18
6:50	75
6:51	46
6:52	83
6:53	58
6:54	24
6:55	58
6:56	81
6:57	65
6:58	57
6:59	96
7:00	34	66.8666666666667
7:01	99
7:02	79
7:03	75
7:04	89
7:05	92
7:06	63
7:07	47
7:08	49
7:09	93
7:10	45
7:11	40
7:12	97
7:13	41
7:14	60
7:15	56	52.0666666666667
7:16	71
7:17	92
7:18	31
7:19	68
7:20	1
7:21	3
7:22	64
7:23	21
7:24	7
7:25	82
7:26	8
7:27	99
7:28	96
7:29	82
7:30	97	51.4
7:31	5
7:32	1
7:33	82
7:34	67
7:35	30
7:36	29
7:37	89
7:38	53
7:39	80
7:40	14
7:41	88
7:42	73
7:43	36
7:44	27
7:45	53	47.4666666666667
7:46	47
7:47	55
7:48	4
7:49	46
7:50	65
7:51	47
7:52	21
7:53	25
7:54	51
7:55	87
7:56	24
7:57	46
7:58	75
7:59	66
8:00	63	41.5333333333333
8:01	46
8:02	69
8:03	58
8:04	14
8:05	21
8:06	83
8:07	75
8:08	23
8:09	12
8:10	60
8:11	63
8:12	6
8:13	9
8:14	21
8:15	53	40.6
8:16	42
8:17	22
8:18	0
8:19	65
8:20	6
8:21	66
8:22	16
8:23	85
8:24	8
8:25	78
8:26	44
8:27	86
8:28	30
8:29	8
8:30	98	68.3333333333333
8:31	81
8:32	93
8:33	66
8:34	60
8:35	27
8:36	84
8:37	62
8:38	81
8:39	76
8:40	94
8:41	19
8:42	60
8:43	38
8:44	86
8:45	92	47.7333333333333
8:46	39
8:47	18
8:48	49
8:49	36
8:50	57
8:51	59
8:52	84
8:53	8
8:54	36
8:55	14
8:56	48
8:57	51
8:58	67
8:59	58
9:00	73	44.7333333333333
9:01	90
9:02	59
9:03	2
9:04	1
9:05	2
9:06	33
9:07	51
9:08	33
9:09	81
9:10	2
9:11	93
9:12	79
9:13	18
9:14	54
9:15	87	48.8666666666667
9:16	75
9:17	33
9:18	19
9:19	24
9:20	14
9:21	45
9:22	13
9:23	93
9:24	54
9:25	69
9:26	12
9:27	2
9:28	99
9:29	94
9:30	42	44.4666666666667
9:31	70
9:32	83
9:33	20
9:34	66
9:35	10
9:36	35
9:37	74
9:38	18
9:39	35
9:40	7
9:41	64
9:42	67
9:43	55
9:44	21
9:45	48	53.4
9:46	91
9:47	88
9:48	3
9:49	11
9:50	45
9:51	8
9:52	20
9:53	96
9:54	17
9:55	76
9:56	78
9:57	54
9:58	99
9:59	67
10:00	18	53.5333333333333
10:01	52
10:02	33
10:03	19
10:04	65
10:05	88
10:06	56
10:07	99
10:08	18
10:09	98
10:10	50
10:11	46
10:12	42
10:13	53
10:14	66
10:15	61	58.5333333333333
10:16	84
10:17	66
10:18	44
10:19	43
10:20	8
10:21	26
10:22	77
10:23	55
10:24	85
10:25	6
10:26	91
10:27	66
10:28	66
10:29	100
10:30	74	46.3333333333333
10:31	70
10:32	10
10:33	72
10:34	35
10:35	19
10:36	86
10:37	72
10:38	7
10:39	31
10:40	95
10:41	35
10:42	46
10:43	2
10:44	41
10:45	45	46.3333333333333
10:46	85
10:47	3
10:48	11
10:49	48
10:50	90
10:51	59
10:52	40
10:53	26
10:54	9
10:55	94
10:56	7
10:57	70
10:58	31
10:59	77
11:00	67	47
11:01	78
11:02	59
11:03	0
11:04	70
11:05	77
11:06	71
11:07	49
11:08	51
11:09	63
11:10	21
11:11	21
11:12	50
11:13	11
11:14	17
11:15	4	46.9333333333333
11:16	54
11:17	9
11:18	49
11:19	30
11:20	82
11:21	41
11:22	22
11:23	33
11:24	29
11:25	78
11:26	88
11:27	37
11:28	84
11:29	64
11:30	23	47.4666666666667
11:31	50
11:32	8
11:33	50
11:34	24
11:35	84
11:36	53
11:37	78
11:38	80
11:39	2
11:40	61
11:41	89
11:42	77
11:43	16
11:44	17
11:45	19	36.2666666666667
11:46	64
11:47	85
11:48	9
11:49	30
11:50	24
11:51	5
11:52	10
11:53	59
11:54	7
11:55	42
11:56	21
11:57	69
11:58	55
11:59	45
12:00	37	48.0666666666667
12:01	60
12:02	85
12:03	44
12:04	28
12:05	89
12:06	35
12:07	18
12:08	48
12:09	64
12:10	3
12:11	38
12:12	96
12:13	5
12:14	71
12:15	71	46.4
12:16	93
12:17	37
12:18	7
12:19	38
12:20	85
12:21	28
12:22	86
12:23	12
12:24	65
12:25	38
12:26	6
12:27	66
12:28	24
12:29	40
12:30	26	42
12:31	98
12:32	74
12:33	13
12:34	46
12:35	52
12:36	39
12:37	14
12:38	38
12:39	25
12:40	28
12:41	30
12:42	44
12:43	63
12:44	40
12:45	96	41.0666666666667
12:46	13
12:47	50
12:48	8
12:49	43
12:50	27
12:51	38
12:52	97
12:53	0
12:54	11
12:55	84
12:56	73
12:57	11
12:58	20
12:59	45
13:00	39	60.1333333333333
13:01	94
13:02	95
13:03	82
13:04	39
13:05	26
13:06	80
13:07	70
13:08	19
13:09	79
13:10	74
13:11	78
13:12	16
13:13	20
13:14	91
13:15	58	46
13:16	67
13:17	52
13:18	55
13:19	47
13:20	25
13:21	8
13:22	78
13:23	53
13:24	55
13:25	43
13:26	9
13:27	28
13:28	56
13:29	56
13:30	26	27.7333333333333
13:31	5
13:32	17
13:33	2
13:34	53
13:35	9
13:36	5
13:37	63
13:38	91
13:39	46
13:40	7
13:41	23
13:42	25
13:43	3
13:44	41
13:45	94	49.0666666666667
13:46	70
13:47	49
13:48	60
13:49	59
13:50	51
13:51	71
13:52	16
13:53	37
13:54	31
13:55	13
13:56	64
13:57	76
13:58	30
13:59	15
14:00	0	32.5333333333333
14:01	6
14:02	7
14:03	13
14:04	52
14:05	25
14:06	53
14:07	15
14:08	65
14:09	70
14:10	72
14:11	12
14:12	23
14:13	23
14:14	52
14:15	87	50.9333333333333
14:16	98
14:17	24
14:18	60
14:19	32
14:20	31
14:21	10
14:22	63
14:23	28
14:24	20
14:25	42
14:26	90
14:27	43
14:28	81
14:29	55
14:30	60	53.7333333333333
14:31	45
14:32	98
14:33	96
14:34	11
14:35	14
14:36	3
14:37	97
14:38	63
14:39	29
14:40	23
14:41	96
14:42	35
14:43	89
14:44	47
14:45	67	58.4666666666667
14:46	41
14:47	66
14:48	70
14:49	98
14:50	47
14:51	37
14:52	68
14:53	61
14:54	16
14:55	70
14:56	19
14:57	46
14:58	100
14:59	71
15:00	97	56.7333333333333
15:01	65
15:02	99
15:03	60
15:04	54
15:05	3
15:06	60
15:07	79
15:08	30
15:09	50
15:10	37
15:11	54
15:12	51
15:13	63
15:14	49
15:15	27	43.0666666666667
15:16	10
15:17	77
15:18	82
15:19	15
15:20	55
15:21	68
15:22	74
15:23	11
15:24	0
15:25	72
15:26	49
15:27	62
15:28	41
15:29	3
15:30	54	64.8666666666667
15:31	86
15:32	95
15:33	31
15:34	45
15:35	71
15:36	100
15:37	59
15:38	43
15:39	15
15:40	88
15:41	3
15:42	97
15:43	88
15:44	98
15:45	76	49.7333333333333
15:46	17
15:47	82
15:48	34
15:49	64
15:50	85
15:51	52
15:52	29
15:53	60
15:54	76
15:55	16
15:56	12
15:57	64
15:58	52
15:59	27
16:00	84	42.8
16:01	10
16:02	11
16:03	13
16:04	99
16:05	29
16:06	64
16:07	29
16:08	20
16:09	64
16:10	36
16:11	14
16:12	38
16:13	69
16:14	62
16:15	96	52.0666666666667
16:16	39
16:17	10
16:18	80
16:19	88
16:20	14
16:21	2
16:22	4
16:23	54
16:24	25
16:25	78
16:26	88
16:27	99
16:28	89
16:29	15
16:30	73	56
16:31	68
16:32	29
16:33	43
16:34	26
16:35	54
16:36	13
16:37	100
16:38	89
16:39	33
16:40	48
16:41	35
16:42	92
16:43	56
16:44	81
16:45	30	50.1333333333333
16:46	99
16:47	4
16:48	100
16:49	5
16:50	10
16:51	37
16:52	12
16:53	92
16:54	99
16:55	95
16:56	69
16:57	20
16:58	52
16:59	28
17:00	45	44.3333333333333
17:01	27
17:02	20
17:03	35
17:04	57
17:05	88
17:06	91
17:07	7
17:08	66
17:09	41
17:10	20
17:11	32
17:12	77
17:13	8
17:14	51
17:15	38	53.6
17:16	36
17:17	39
17:18	40
17:19	15
17:20	91
17:21	45
17:22	37
17:23	99
17:24	98
17:25	54
17:26	64
17:27	97
17:28	48
17:29	3
17:30	55	50.6
17:31	33
17:32	71
17:33	40
17:34	65
17:35	80
17:36	46
17:37	57
17:38	43
17:39	3
17:40	70
17:41	30
17:42	73
17:43	63
17:44	30
17:45	79	67.4
17:46	67
17:47	49
17:48	75
17:49	98
17:50	78
17:51	83
17:52	83
17:53	54
17:54	77
17:55	46
17:56	52
17:57	66
17:58	64
17:59	40
18:00	95	58.8666666666667
18:01	100
18:02	83
18:03	61
18:04	9
18:05	11
18:06	35
18:07	40
18:08	75
18:09	80
18:10	14
18:11	82
18:12	37
18:13	72
18:14	89
18:15	48	45.6666666666667
18:16	40
18:17	90
18:18	27
18:19	10
18:20	65
18:21	52
18:22	4
18:23	29
18:24	23
18:25	29
18:26	90
18:27	92
18:28	1
18:29	85
18:30	62	63.2
18:31	46
18:32	99
18:33	50
18:34	61
18:35	74
18:36	90
18:37	45
18:38	91
18:39	100
18:40	93
18:41	67
18:42	19
18:43	16
18:44	35
18:45	86	58.9333333333333
18:46	64
18:47	99
18:48	26
18:49	99
18:50	19
18:51	31
18:52	93
18:53	11
18:54	74
18:55	88
18:56	36
18:57	56
18:58	68
18:59	34
19:00	18	69.3333333333333
19:01	97
19:02	26
19:03	84
19:04	98
19:05	53
19:06	79
19:07	95
19:08	72
19:09	92
19:10	24
19:11	95
19:12	86
19:13	96
19:14	25
19:15	50	58.8
19:16	88
19:17	95
19:18	20
19:19	85
19:20	41
19:21	39
19:22	45
19:23	67
19:24	39
19:25	63
19:26	98
19:27	2
19:28	61
19:29	89
19:30	90	57.8666666666667
19:31	74
19:32	84
19:33	98
19:34	83
19:35	25
19:36	92
19:37	72
19:38	26
19:39	34
19:40	48
19:41	2
19:42	70
19:43	7
19:44	63
19:45	38	53.7333333333333
19:46	12
19:47	44
19:48	23
19:49	0
19:50	80
19:51	88
19:52	88
19:53	93
19:54	34
19:55	69
19:56	15
19:57	95
19:58	85
19:59	42
20:00	37	35
20:01	100
20:02	3
20:03	17
20:04	5
20:05	44
20:06	99
20:07	29
20:08	100
20:09	2
20:10	5
20:11	11
20:12	25
20:13	24
20:14	24
20:15	63	40.7333333333333
20:16	81
20:17	90
20:18	44
20:19	61
20:20	18
20:21	37
20:22	6
20:23	85
20:24	1
20:25	53
20:26	3
20:27	54
20:28	5
20:29	10
20:30	9	54.4666666666667
20:31	68
20:32	89
20:33	69
20:34	74
20:35	50
20:36	84
20:37	15
20:38	58
20:39	80
20:40	30
20:41	69
20:42	86
20:43	11
20:44	25
20:45	0	45.4666666666667
20:46	49
20:47	29
20:48	65
20:49	26
20:50	62
20:51	41
20:52	65
20:53	27
20:54	78
20:55	70
20:56	17
20:57	90
20:58	5
20:59	58
21:00	24	49.4
21:01	95
21:02	23
21:03	16
21:04	0
21:05	25
21:06	70
21:07	70
21:08	85
21:09	81
21:10	81
21:11	90
21:12	44
21:13	9
21:14	28
21:15	67	53.1333333333333
21:16	89
21:17	54
21:18	11
21:19	17
21:20	63
21:21	97
21:22	88
21:23	95
21:24	63
21:25	65
21:26	56
21:27	13
21:28	3
21:29	16
21:30	14	55.8
21:31	83
21:32	2
21:33	73
21:34	81
21:35	70
21:36	5
21:37	84
21:38	63
21:39	88
21:40	33
21:41	49
21:42	90
21:43	75
21:44	27
21:45	43	53.7333333333333
21:46	67
21:47	61
21:48	17
21:49	49
21:50	32
21:51	100
21:52	93
21:53	93
21:54	53
21:55	23
21:56	16
21:57	4
21:58	76
21:59	79
22:00	47	39.6
22:01	46
22:02	9
22:03	92
22:04	67
22:05	2
22:06	39
22:07	38
22:08	52
22:09	1
22:10	15
22:11	17
22:12	78
22:13	14
22:14	77
22:15	70	43.8666666666667
22:16	58
22:17	54
22:18	71
22:19	0
22:20	68
22:21	10
22:22	79
22:23	50
22:24	57
22:25	49
22:26	0
22:27	50
22:28	5
22:29	37
22:30	50	46.8
22:31	27
22:32	54
22:33	39
22:34	1
22:35	88
22:36	23
22:37	81
22:38	70
22:39	54
22:40	39
22:41	52
22:42	33
22:43	2
22:44	89
22:45	5	58.9333333333333
22:46	78
22:47	39
22:48	82
22:49	79
22:50	73
22:51	36
22:52	75
22:53	81
22:54	71
22:55	40
22:56	33
22:57	90
22:58	38
22:59	64
23:00	14	51.2666666666667
23:01	27
23:02	93
23:03	80
23:04	12
23:05	98
23:06	9
23:07	67
23:08	87
23:09	10
23:10	15
23:11	69
23:12	60
23:13	60
23:14	68
23:15	21	59.4666666666667
23:16	86
23:17	76
23:18	21
23:19	81
23:20	31
23:21	71
23:22	68
23:23	90
23:24	63
23:25	8
23:26	85
23:27	95
23:28	29
23:29	67
23:30	45	46.2666666666667
23:31	71
23:32	73
23:33	58
23:34	23
23:35	16
23:36	16
23:37	79
23:38	84
23:39	100
23:40	4
23:41	23
23:42	55
23:43	25
23:44	22
23:45	8	45.4
23:46	27
23:47	49
23:48	22
23:49	84
23:50	46
23:51	24
23:52	19
23:53	53
23:54	32
23:55	72
23:56	69
23:57	96
23:58	20
23:59	60

Here's the funny part.  The question that was posited essentially asked if the average of the 1 minute data would be more accurate than the average of the 15 minute averages.  Here are the results:

Average of all the 1 minute data points: 49.76319444
Average of all the 15 minute averages: 49.76319444

They're exactly the same!  The reason for this comes from the associative property of addition.  Think about the formula for an average:
x̄ = (a₁+a₂+a₃+...+a_n)/n

If you had multiple sets (i.e. 15 minute averages):
x̄ = {(a₁+a₂+a₃+...+a_n)/n_a + (b₁+b₂+b₃+...+b_n)/n_b + (c₁+c₂+c₃+...+c_n)/n_c}/3

Since in this case n_a=n_b=n_c, then substitue n and factor out:
x̄ = {(a₁+a₂+a₃+...+a_n) + (b₁+b₂+b₃+...+b_n) + (c₁+c₂+c₃+...+c_n)}/3n

Associative property of addition:
x̄ = (a₁+a₂+a₃+...+a_n+b₁+b₂+b₃+...+b_n+c₁+c₂+c₃+...+c_n)/3n

Since in this case 3n = n + n + n and n_a=n_b=n_c=n, then substitue for n:
x̄ = (a₁+a₂+a₃+...+a_n+b₁+b₂+b₃+...+b_n+c₁+c₂+c₃+...+c_n)/(n + n + n)
x̄ = (a₁+a₂+a₃+...+a_n+b₁+b₂+b₃+...+b_n+c₁+c₂+c₃+...+c_n)/(n_a + n_b + n_c)

Knowing that n_a + n_b + n_c is actually the total number of original points gets us back to the original equation for calculating the average of the 1 minute data.  So the average of all the data points is the same as the average of evenly spaced averages of all the data points.  If you're wondering if this works, go ahead and mock it up in Excel.  I did.

NetVoyant Duplicates through ODBC

Continuing my effort to document the various ways I've used the ODBC connector for the NetQoS products, here's my next query and controls I've built and that I use in production.  Today's query comes from a need to view duplicate devices and make it easy to eliminate the duplicates.  I had built a fairly extensive method to do this using Perl script and batch files.  It suffered from all the problems that most NPC browser views suffer from.  So, ODBC is a better way to accomplish this and I'm officially retiring that script.  Here's the SelectCommand and OdbcConnection String to put in the configuration.xml:

<selectcommand Name="NVDuplicates" Property="False" SupportedViewTypes="Table"> <![CDATA[ select convert(concat( '<a target=\'_blank\' href=\'http://NVMC/nv/default.aspx?pg=', if(a.class_id=30000,1,if(a.class_id=4,20004,if(a.class_id=7,30104,11004))), '&Deviceid=', a.device_id, '\'>', a.dev_alias, '</a>' ) using utf8) as Alias, a.dev_alias as aliassort, if(a.expiration_started=0,'-',from_unixtime(a.expiration_started, '%Y/%m/%d %H:%i')) as 'ExpiringSince', if(a.unresponsive_started=0,'-',from_unixtime(a.unresponsive_started, '%Y/%m/%d %H:%i')) as 'UnresponsiveSince', b.smgr_host as Poller, concat( '<a target=\'_blank\' href=\'//', b.smgr_host, '/pollerwebservice/TopologyService.asmx/DeleteDevice?deviceNameOrIpAddress=', a.dev_name, '\'><img border=0 src=\'images/deleteX.gif\' /></a>' ) as deletethis from service_managers b, devices a inner join ( select dev_alias as dupon from devices where model_id<>0 group by dev_alias having (count(dev_alias)>1) ) dup on a.dev_alias = dup.dupon where b.smgr_id = a.dev_owner order by a.dev_alias; ]]> </SelectCommand> <odbcconnectionstring Name="NetVoyant">Driver={MySQL ODBC 5.1 Driver};Server=192.168.20.21;Port=3308;Option=3;Database=nms2;User=nmsuper;Password=redpoint;</OdbcConnectionString>
To create the view, run the following SQL commands against the NPC server:

INSERT INTO controls SELECT if(min(id)-1>0,min(id)-1,29999), 'NetVoyant Duplicates', 'Graph', 'NetVoyant-ODBC', '/nqWidgets/Integration/wicTable.ascx', 1, 0x200000, 0, 0, 0, 1, '|nc|g|', '', 'Y' FROM controls WHERE datasourcetype=0x200000; INSERT INTO control_properties (ControlID, Editable, PropertyName, PropertyValue) VALUES (-1, 'N', 'Title', 'NetVoyant Duplicates'), (-1, 'N', 'DisplayColumns', 'deletethis;Alias;ExpiringSince;UnresponsiveSince;Poller'), (-1, 'N', 'DisplayFormats', 'string,string,string,string,string'), (-1, 'N', 'DisplayNames', 'Action,Device Alias,Expiring Since,Unresponsive Since, Poller'), (-1, 'N', 'ColumnWidths', ',,,,'), (-1, 'N', 'HorizontalAlignments', 'Center,Left,Left,Left,Left'), (-1, 'N', 'SortField', 'aliassort'), (-1, 'N', 'SortDirection', 'Desc'), (-1, 'N', 'Integration.Parameter.SelectCommandName', 'NVDuplicates'), (-1, 'N', 'Integration.DataType', 'SelectCommand'), (-1, 'N', 'Integration.Parameter.DateTimeFormat', 'yyyy-MM-dd HH:mm'), (-1, 'N', 'Integration.Parameter.TopN', '200'), (-1, 'N', 'Integration.Parameter.OdbcConnectionStringName','NetVoyant'); UPDATE control_properties SET ControlID = ( select min(id) from controls where datasourcetype=0x200000) WHERE ControlID = -1;

Automating NFA Parser Reports

UPDATE: CA Support has endorsed the NAST tool as the replacement for the NFAParser.  I haven't tested it, but if it's like the other updated tools it will run faster.  The nice thing is that the syntax for running the NAST tool silently is the same as the NFAParser.  So, it doesn't take much to update this tool to use the new tool.

---

A while back I was tasked with making it possible to view NFA Parser output inside NPC.  It was actually easier than I thought.  I came up with something that isn't as optimal as I would like it (I'll explain why later), but it works for now.

The first thing you have to do is to download the NFA Parser which is part of CA's Support Tools 6 and copy it to each harvester. If you don't want to use all the tools, you can just download the parser and put it on each harvester.  The output of the parser is an HTML file which is ready to be published to a web service so you can link to it from NPC.  The easiest way to do this is to call the parser with a working directory of C:\inetpub\wwwroot on the harvester.  That way the output will be put in that directory, ready to be viewed in a browser.  However, every time you run the parser, the output file's name contains a date/time stamp, so that makes it a little difficult to link to.  The solution is to wrap it all in a batch file that clears the old output, calls the new output, then renames the new output to some static name.  Here's what that batch script would look like:
"D:\SupportTools6\NetQoS Utilities\ReporterAnalyzer\NFAParserv9.exe" batch %1 del nfaout.htm ren nfasum*.htm nfaout.htm

This could be tweaked a bit to keep the last X files using the following batch script:
del /q nfaout%2.htm for /L %%A in (1 1 %2) do ( ren nfaout%%A.htm nfaoutC.htm ren nfaoutB.htm nfaout%%A.htm ren nfaoutC.htm nfaoutB.htm ) "D:\SupportTools6\NetQoS Utilities\ReporterAnalyzer\NFAParserv9.exe" batch %1 ren nfasum*.htm nfaout1.htm

This second option moves the existing files up in a queue by renaming them with a higher name, except for the highest one that gets deleted.  So, if I created a scheduled task like this: C:\inetpub\wwwroot\nfa.bat 1 5, I would eventually end up with 5 files, each one representing one of the last 5 runs with nfaout1.htm being the most recent, each spanning 1 minute.  This second method is the option I'm using in production and it seems to work just fine.  In order to easily give access to the files, I create an HTML table with a column for the servers then a column for each of the retained reports.  Then I put in a row for each harvester.  I put that HTML in my custom content directory and load it into a browser view.

<head> <script> function whendiditrun(dayoffset,prepost){ var d = new Date(); d.setHours(prepost,0,0,0); d.setDate(d.getDate()-dayoffset); var seconds = new Date(); seconds = (seconds - d) / 1000; interval = Math.floor(seconds / 8640) / 10; if (interval >= 1) {return interval + " days ago";} interval = Math.floor(seconds / 360) / 10; if (interval >= 1) {return interval + " hours ago";} interval = Math.floor(seconds / 6) / 10; if (interval >= 1) {return interval + " minutes ago";} return Math.floor(seconds) + " seconds ago"; } </script> </head> The NFA parser interprets the raw NetFlow data coming into the harvester, generates a report, and runs every 12 hours on each harvester (local time to the harvester). The resulting report for each harvester shows what flows have been received in the 1 minute preceeding the execution of the script. The output of the last 5 reports can be displayed using the links below. <table border="1"> <tr><td rowspan="2"><center> Server</center> </td> <td colspan="5"><center> Last Run</center> </td></tr> <tr> <td><script>document.write(whendiditrun(0,0));</script></td> <td><script>document.write(whendiditrun(1,12));</script></td> <td><script>document.write(whendiditrun(1,0));</script></td> <td><script>document.write(whendiditrun(2,12));</script></td> <td><script>document.write(whendiditrun(2,0));</script></td></tr> <tr> <td>Harvester 1</td> <td><a href="http://harvester1/nfaout1.htm">View</a> </td> <td><a href="http://harvester1/nfaout2.htm">View</a> </td> <td><a href="http://harvester1/nfaout3.htm">View</a> </td> <td><a href="http://harvester1/nfaout4.htm">View</a> </td> <td><a href="http://harvester1/nfaout5.htm">View</a> </td> </tr> </table>
Obviously running the report more frequently and with a longer timespan will increase load on the harvester, so don't turn it on to run for 1 minute every minute.

Giving Existing Users Access to New Data Sources

UPDATE: Turns out I had released a more limited set of commands prevously.  Think of this method as version 2.  It's more complete and replaces the previous method.

---

There is a limitation in NPC that is a little annoying.  If you have a slew of users created in NPC (either using LDAP integration or just local product authentication) and you add a new data source, only nqadmin and nquser get access to the new data source.  By default, all other users don't get any access to the data source.  This doesn't mean they don't get access to the data, it just means that they can't log into the web GUI for that data source.  The 'proper' action would be to edit every single user and grant them either user, power user, or admin rights to the new data source.  With the advent of SSO and LDAP integration, this just won't work (especially if you have more than a couple dozen users).  And if you've made it a habit to only use the nqadmin account for root level tasks and you are using your own account (setup as an administrator), you would be able to add the data source but not be able to access it until you edit your own user account and give yourself access.  The silly thing is that any new users based on the nqadmin or nquser account would have access to the data source.  The problem is with existing users.

This is a difficult nut to crack; There are a couple features that could be built into NPC that would prevent the data source adder from having to touch every single user:

1. Give all users no access.  This is what happens now.
2. Give all users 'user' access to the data source.  This means that you would still have to log edit your own account and grant yourself admin access, not to mention any other accounts that need to be administrators on the new data source.
3. Give all users except the data source adder 'user' access.  This would allow you immediate admin access, but any other admins would still have to be edited manually.
4. Give all users the same access to the new data source as they have in NPC.  By this I mean that if a user is an administrator in NPC, they become an administrator for the new data source as well.  If they're a user in NPC, they become a user in NPC.  
5. Give all cloned users the same access as the account they were cloned from.  So, if a user was cloned from the nquser account (which inherits access to the new data source by default), then that user would also inherit access to the new data source.  
6. Setup product privilege sets and assign them to users.  I'd create three: all admin, all power user, and all user.  Then I could go to one place to make the change and all the users would be affected.

None of these features are in NPC or is there any indication they will be built into CAPC.  So, in the mean time, here's how you fix it.  The following query will essentially do option #4 above, giving all users the same access to all data sources as they have in NPC.  So, if they're a user in NPC, they'll have user access to all the data sources.  If they're an admin in NPC, they'll have admin access to all the data sources.

replace into data_source_rights select a.userid, b.sourceid, a.userlevel from user_definitions a, data_sources2 b where b.sourceid in (select distinct sourceid from data_source_rights) and a.removable='Y';

There are two ways that this query can be run: 1) scheduled task or 2) manual batch file after adding a data source.  Either way should be sufficient.  Option 2 would be more efficient since this only needs to be done after a new data source is added to NPC.

RA Router List through ODBC

Continuing my effort to document the various ways I've used the ODBC connector for the NetQoS products, here's my next query and controls I've built and that I use in production.  Today's query comes from a need to view the router status for all routers monitored by ReporterAnalyzer (NFA).  The goal is to show a list of the routers along with the address from which RA is receiving Netflow, along with which harvester it's assigned to, how many interfaces have been discovered, how many are enabled, when the router last rebooted, when the last refresh was, and when it was last discovered.  Here's the SelectCommand and OdbcConnection String to put in the configuration.xml:

<selectcommand Name="RARouters" Property="False" SupportedViewTypes="Table"> <![CDATA[ SELECT SQL_CALC_FOUND_ROWS convert(concat( '<a target=\'_blank\' href=\'../npc/default.aspx?pg=98019&itemtypeid=3&itemsubtypeid=0&mn=4&search=', v.routerSysName, '\'>', v.routerSysName, '</a>' ) using utf8) as Router, v.ID, v.routerAddress, v.routerSysName, v.harvester, v.snmpId, v.snmpName, v.snmpVersion, v.snmpPort, v.lastPoll, tv.totalInterfaces, ev.enabledInterfaces, from_unixtime(v.lastReboot,'%Y/%m/%d %H:%i') as Last_Reboot, from_unixtime(v.lastRefresh,'%Y/%m/%d %H:%i') as Last_Refresh, from_unixtime(v.lastDiscovery,'%Y/%m/%d %H:%i') as Last_Discovery FROM routers_all_view v INNER JOIN routers_interfaces_allcount_view tv on v.ID = tv.ID LEFT JOIN routers_interfaces_enabledcount_view ev on v.ID = ev.ID LIMIT 200; ]]> </SelectCommand> <odbcconnectionstring Name="RAMC">Driver={MySQL ODBC 5.1 Driver};Server=192.168.20.20;Port=3308;Option=3;Database=reporter;User=netqos;Password=netqos;</OdbcConnectionString>
To create the view, run the following SQL commands against the NPC server:

INSERT INTO controls SELECT if(min(id)-1>0,min(id)-1,29999), 'ReporterAnalyzer Router Status List', 'Graph', 'ReporterAnalyzer-ODBC', '/nqWidgets/Integration/wicTable.ascx', 1, 0x200000, 0, 0, 0, 1, '|nc|g|', '', 'Y' FROM controls WHERE datasourcetype=0x200000; INSERT INTO control_properties (ControlID, Editable, PropertyName, PropertyValue) VALUES (-1, 'N', 'Title', 'ReporterAnalyzer Router Status List'), (-1, 'N', 'DisplayColumns', 'routerAddress;Router;harvester;totalInterfaces;enabledInterfaces;Last_Reboot;Last_Refresh;Last_Discovery'), (-1, 'N', 'DisplayFormats', 'string,string,string,string,string,string,string,string'), (-1, 'N', 'DisplayNames', 'Address,sysName,Harvester,Total Interfaces,Enabled Interfaces,Last Reboot,Last Refresh,Last Discovery'), (-1, 'N', 'ColumnWidths', ',,,,,,,'), (-1, 'N', 'HorizontalAlignments', 'Left,Left,Left,Left,Left,Left,Left,Left'), (-1, 'N', 'SortField', 'routersysName'), (-1, 'N', 'SortDirection', 'Asc'), (-1, 'N', 'Integration.Parameter.SelectCommandName', 'RARouters'), (-1, 'N', 'Integration.DataType', 'SelectCommand'), (-1, 'N', 'Integration.Parameter.DateTimeFormat', 'yyyy-MM-dd HH:mm'), (-1, 'N', 'Integration.Parameter.TopN', '200'), (-1, 'N', 'Integration.Parameter.OdbcConnectionStringName','RAMC'); UPDATE control_properties SET ControlID = ( select min(id) from controls where datasourcetype=0x200000) WHERE ControlID = -1;

iOS App Review: Remote

Apple
Summary - This app gets a ton of usage at our house.  In the last few years, Apple has been improving on their AirPlay feature and Remote reaps the benefits.  This app on my iPhone can connect to any of the iTunes libraries and AppleTVs on my network and control them.  We use this mainly as an additional remote control for our AppleTV.  I can swipe and tap to navigate around the AppleTV UI as well as browse through shared content from my desktop without interrupting the current content on the AppleTV.  When I find a movie, TV show, or music playlist I want to play, the Remote app starts it streaming to the AppleTV.  I can also use this to control music playing on my desktop.  So, if I walk into the other room, I can easily stop the music from playing without going back to my office.  We also use it from time to time to control the iTunes music playing in the nursery (since that PC is a headless PC; e.g. no monitor attached).

Pros - This one is definitely easy to setup.  Just enable the home sharing option by signing in with your Apple ID and you get full control of all the iTunes libraries and devices joined to the home share.  Apple's recent update also added a 'stay-connected' feature that keeps me connected for a few minutes after using the remote.  This means faster resume time if I was just using it a few minutes ago.  The app also supports AirPlay.  I discovered that from the app I can start music playing in my office, then also extend it to the speakers connected to my AppleTV.

Cons - I haven't really found any downsides to the app.  It's well designed and does exactly what I want it to do.

iOS App Review: Latitude

Google Latitude - Google, Inc.
Summary - This handy little app scares people who are paranoid about Google being their big brother.  I look at Google in this way: "If Google wanted any of my information, they'd get it.  So why go to the effort of hiding it?"  Latitude tracks my current GPS position and uploads it regularly to the Google cloud.  Within the app, I have a few defined friends that also use Latitude.  I choose to share my current location with them.  This means I can always check where my wife, my Dad, my brother, and my best friend are.  Hopefully the service won't be turned off without the functionality being moved into Google+.

Pros - Easy to setup, easy to add friends, quick to launch, and I always know if my parents are on their way over for a surprise visit to see the grandkids.  I can also set an option (that's disabled by default) to keep a log of my locations (instead of just my current location).  This could be useful for parents trying to keep an eye on their kids or for catching the crooks that steal your phone.

Cons - Google knows where you've been.  Not many people use it.  It's not yet integrated with Google+ (maybe someday).

iOS App Review: Grabatron

Grabatron - Future Games of London

Summary - This game has kept itself on my first page of apps for several months now.  It's a simple game where you fly a UFO by tilting the iPhone.  Tap the screen and a giant claw descends.  The goal is to grab stuff with the claw and destroy it.  Simple gamification techniques make the game easy to put down and resume later while still feeling like I've made progress.  The whole thing has a fun 1950s Aliens Attack! feel to the graphics and theme.

Pros

• Easy to learn the mechanics of the game
• In app upgrades can be bought with real money or crystals earned in the game
• Crystals can also be earned by watching neatly tucked away ads
• The object of the game is destruction!

Cons

• Loads slowly on my iPhone4.  Once loaded, performance is fine
• Music and sound effects were annoying at first.  Easy to disable though

iOS App Review: Tower Madness

TowerMadness - Limbic Software

Summary - One of the most fun games I've played.  It's not that much different than other tower defense games, but consistent new maps and tower upgrades keep me coming back and playing the same parts of the game over and over.

Pros

• New maps released somehow right after I get tired of the existing maps
• New weapon upgrades released somehow right after I get tired of the existing weapon upgrades
• Good graphics
• Easy to learn
• Endless mode allows for longer play with more money and more aliens to kill
• Pinch/spread zoom in/out seamlessly integrates with 3D environment
• 1x/2x/4x play modes so you can play faster

Cons

• No 'upgrade all the way' or 'upgrade as much as I can afford' option (instead you have to go through each upgrade manually
• No option in endless mode to send 10/50/100 waves at once (instead you have to tap the single button 10/50/100 times)
• More maps can be bought as in app purchases - that's how they make money
• More weapons can be bought as in app purchases
• 8x play mode would be nice
• My iPhone 4 slows down when more than 40 waves are present on the playing field and in 4x mode.  Doesn't seem to affect score
• One tower has an upgrade that turns it into a Tesla Coil.  While it's a great weapon, it doesn't work against airborne enemies.  Since it's all science fiction anyway, I would prefer that this upgrade gave the tower the ability to engage airborne enemies, much like the famed Tesla Death Ray

SA Collector Feed List through ODBC

Continuing my effort to document the various ways I've used the ODBC connector for the NetQoS products, I'm going to post a couple of the queries and controls I've built and that I use in production.  Today's query comes from a need to review all the collector feeds for all the collectors on two different master consoles.  Collector feeds are configured for each collection NIC on standard collectors.  I haven't run this query on a MC with a MTP connected, but it should result in the same thing.
The query comes from three different tables and results in a list of feeds and options to edit each collector.  Unfortunately, the functionality to directly modify the feed properties within the GUI isn't present yet, but something like it should be coming soon.  Here's the SelectCommand and OdbcConnection String to put in the configuration.xml:

<selectcommand name="SACollectors" property="False" supportedviewtypes="Table"> <![CDATA[ select convert(concat( '<a target=\'_blank\' href=\'//', c.PropValue, '/SuperAgent/Admin/', 'editAdminCollectors.aspx?CollID=', b.probe_id, '\'>', a.probe_desc, '</a>' ) using utf8) as Collector_Name, b.probe_id as Collector_ID, b.feed_index as Feed_Index, b.probe_id * b.feed_index as Sorter, convert(inet_ntoa(a.address) using utf8)  as Mgmt, convert(inet_ntoa(a.monitor_address)  using utf8) as Monitor1, convert(inet_ntoa(a.monitor_address2)  using utf8) as Monitor2, a.ip_addresses as IP_Address_List, b.feed_name as Feed_Name, convert(inet_ntoa(b.listen_ip) using utf8)  as Listen_IP, b.options as Options from probes a, probe_feeds b, performance_center_properties c where b.inactive=0 and a.probe_id=b.probe_id and c.propname='DS.ConsoleHost'; ]]> </SelectCommand> <odbcconnectionstring Name="SAMC">Driver={MySQL ODBC 5.1 Driver};Server=192.168.23.67;Port=3308;Option=3;Database=super;User=super;Password=nqsuper;</OdbcConnectionString>
To create the view, run the following SQL commands against the NPC server:

INSERT INTO controls SELECT if(min(id)-1>0,min(id)-1,29999), 'SuperAgent Collector Feeds', 'Graph', 'NetVoyant-ODBC', '/nqWidgets/Integration/wicTable.ascx', 1, 0x200000, 0, 0, 0, 1, '|nc|g|', '', 'Y' FROM controls WHERE datasourcetype=0x200000; INSERT INTO control_properties (ControlID, Editable, PropertyName, PropertyValue) VALUES (-1, 'N', 'Title', 'SuperAgent Collector Feeds'), (-1, 'N', 'DisplayColumns', 'Collector_Name;Collector_ID;Feed_Index;Mgmt;Monitor1;Monitor2;IP_Address_List;Feed_Name;Listen_IP;Options'), (-1, 'N', 'DisplayFormats', 'string,string,string,string,string,string,string,string,string,string'), (-1, 'N', 'DisplayNames', 'Collector Name,Collector ID,Feed Index,Management IP,Monitor 1 IP,Monitor 2 IP,IP Address List,Feed Name,Listen IP,Options'), (-1, 'N', 'ColumnWidths', ',,,,,,,,,'), (-1, 'N', 'HorizontalAlignments', 'Left,Left,Left,Left,Left,Left,Left,Left,Left,Left'), (-1, 'N', 'SortField', 'Sorter'), (-1, 'N', 'SortDirection', 'Asc'), (-1, 'N', 'Integration.Parameter.SelectCommandName', 'SACollectors'), (-1, 'N', 'Integration.DataType', 'SelectCommand'), (-1, 'N', 'Integration.Parameter.DateTimeFormat', 'yyyy-MM-dd HH:mm'), (-1, 'N', 'Integration.Parameter.TopN', '200'), (-1, 'N', 'Integration.Parameter.OdbcConnectionStringName','SAMC'); UPDATE control_properties SET ControlID = ( select min(id) from controls where datasourcetype=0x200000) WHERE ControlID = -1;

ODBC Connector for NPC

Most of the built in reporting for NPC is great and can be modified by the administrator to get things to look exactly the way they need to be. However, sometimes the built in reports just don't do it. Show my side note.

Software should come out of the box with the best settings enabled but be customizable to the point that it can field even the strangest requests.  NPC is close to that.  CAPC promises to lock down the customizability in an effort to ensure that people are looking at things the right way.  The problem is that people don't usually want to change the way they look at things.  CAPC will fail at this unless it becomes much more flexible.  Ok, stepping off my soap box and returning to topic.

Specifically, the data that we need to pull is not necessarily available either because the views themselves are limited or because the queries are not the kind of reports that are normally run in NPC or even in the data source. In this case, we have to turn to the ODBC Connector. Not much is known about this, but I was recently introduced to it as a way of bringing external data into NPC. Turns out it's very flexibly and fairly easy to work with.

The ODBC connector allows you to source a table view in NPC with any MySQL query on any server your  connector server has MySQL access to.  So, if you can run the query manually, you can use the query as the source of a view on an NPC page.  Pretty cool huh?  I've built a couple of queries and views into my own NPC.  Now instead of having scheduled tasks that run queries nightly and display that (sometimes stale) output in a browser view, we have dynamic live data in paginated form.  Here's how you do it.

Standard warnings apply: you'll break your stuff, I won't help you fix it, neither will CA support.  Don't do this.  For more details look at the footer of this page.  Make a backup first.

Install the ODBC connector.  Usually this is done on the NPC server, but there's no reason it couldn't be done on one of the data sources or even on a standalone server.  Warning: all NetQoS services on the target server will be stopped as part of the installation, so you'll need to do a reboot at the end of the installation.  Don't worry, you're prompted to do it.

After you get it installed, you'll need to hack NPC a little so you can add it as a data source.  Run the following command on the NPC server.
mysql -P 3308 -D netqosportal -e "REPLACE INTO data_source_types2 (SourceType, RelatedSourceTypes, SourceName, ServiceURL, RequiredVersion, MaxInstances, pollinterfacetype) VALUES (0x200000, 0x200000, 'ODBC Connector','{DS.Protocol}://{DS.Host}:{DS.Port}/Odbc/OdbcService.asmx',16777216, 10, 2);"

Now add the ODBC connector as a data source in NPC just like you'd add any other data source.  So far, so good.  Now we need to design our query, build the view that will show the data, and put that view on the page.

I use HeidiSQL because it's easy to look around at things and involves less typing than the command line.  Use whatever you want. Either way, design your query such that you can run it and the output looks the way you would want it to look in NPC.  Don't use spaces in the field names, we will replace the field names later with more readable names.  For example, here's an easy query: select 'Hello World!' as title, 'Nice' as Column2, '1' as Column3;.  This results in one row with three columns.  This query needs to be added to the configuration XML in D:\netqos\portal\integration\ODBC\configuration.xml.  You add it between the <SelectCommands> tags and using a <SelectCommand> tag like this (the tags are probably case sensitive, do it like the ones already there):
<selectcommands> <selectcommand Name="HelloWorld" Property="False" SupportedViewTypes="Table"> <![CDATA[      select 'Hello World!' as title, 'Nice' as Column2, '1' as Column3; ]]> </SelectCommand> </SelectCommands>
Once that's in there, you'll also need to specify a portion of the configuration that will tell the ODBC connector which server to connect to and against which to run the query.  You add it between the <OdbcConnectionStrings> tags with an <OdbcConnectionString> tag liek this:
<odbcconnectionstring Name="NetVoyantMC">Driver={MySQL ODBC 5.1 Driver};Server=192.168.1.67;Port=3308;Database=nms2;User=nmsuper;Password=redpoint;</OdbcConnectionString>
This should all be self explanatory.  Save the XML configuration and we should be ready to move on to the next step: building the view.

Even though we might be pulling data from the NetVoyant database, we can't use the NetVoyant view wizard to create the views for the ODBC connector.  Luckily, it only takes a few database inserts to create a new view.  Be careful, as removing a view is a bit more complicated than adding a view.  To add a view, simply use the following SQL script.  The comments in the script should be descriptive enough.  Each comment is about the line that follows it.  They key is to make sure the Integration.Parameter.SelectCommandName is the same as the name specified in the ODBC configuration.xml (in this case 'HelloWorld') and that the Integration.Parameter.OdbcConnectionStringName is set to the name specified in the config.xml (in this case 'NetVoyantMC').

INSERT INTO controls
SELECT
-- sets the controlid to the highest unused id below 30000
if(min(id)-1>0,min(id)-1,29999), 
'Hello World', -- name of the view
'Graph',
'ODBC Connector Views', -- category of the view
'/nqWidgets/Integration/wicTable.ascx', 
1, 0x200000, 0, 0, 0, 1, '|nc|g|', '', 'Y'
FROM controls
WHERE datasourcetype=0x200000;

INSERT INTO control_properties
(ControlID, Editable, PropertyName, PropertyValue)
VALUES
-- name of the view
(-1, 'N', 'Title', 'Hello World'), 
-- sql column names separated by semicolon
(-1, 'N', 'DisplayColumns', 'title;column2;column3'), 
-- format of the columns (normally string)
(-1, 'N', 'DisplayFormats', 'string,string,string'), 
-- nice column names
(-1, 'N', 'DisplayNames', 'Message','Column 1','Column 2'),
-- column widths (not sure on units)
(-1, 'N', 'ColumnWidths', ',,'), 
-- alignment of columns
(-1, 'N', 'HorizontalAlignments', 'Left,Center,Right'), 
-- sql column name to sort by
(-1, 'N', 'SortField', 'title'), 
-- desc or asc
(-1, 'N', 'SortDirection', 'Desc'), 
-- name of selectcommand in config.xml (which query to run)
(-1, 'N', 'Integration.Parameter.SelectCommandName', 'HelloWorld'), 
-- don't change this
(-1, 'N', 'Integration.DataType', 'SelectCommand'), 
-- don't change this
(-1, 'N', 'Integration.Parameter.DateTimeFormat', 'yyyy-MM-dd HH:mm'), 
-- max 200
(-1, 'N', 'Integration.Parameter.TopN', '200'), 
-- name of odbcconnectionstring (which server to query)
(-1, 'N', 'Integration.Parameter.OdbcConnectionStringName','NetVoyantMC'); 

UPDATE control_properties 
SET ControlID = (select min(id) from controls where datasourcetype=0x200000)
WHERE ControlID = -1;

You can have selectcommands and odbcconnectionstrings in yours configuration.xml even if they're not used.  

After using the SQL commands above to create the view, simply edit a page and look in the corresponding view category for your new view.  Add it to the page and it should run the query when the page is refreshed, showing updated data.

If you've added the view but need to modify the select query in the config.xml, you'll need to update the corresponding properties to display them.  Also, you can use the 'concat()' and 'convert() using utf8' functions in a query to put advanced html into a particular column (like a link to a certain item's details page). Something like this would give you a row for each collector with links to edit each collector (I'll post the full configuration in my next post):
select convert(concat('<a href=\'http://superagentmc/SuperAgent/Admin/editAdminCollectors.aspx?CollId=',probe_id,'\'>Edit</a>') using utf8) as EditCollectorLink from probes;

In the next few posts, I'll post some queries (like the one above) and views that I've built to do some basic reporting in NPC from SuperAgent and NetVoyant.

iOS App Review: Flashlight by Rik

I've been asked before what apps I have installed on my iPhone.  I figured I could detail which ones I use and why through my blog.  This will give me room to put my reasons for/against a certain app.  I'll try to include my overview, pros, and cons.  Here goes nothing...

Flashlight by Rik - Henri Asseily
Summary - This turns the iPhone's camera's flash into a flashlight.  Instead of flashing on when taking a picture, this app turns on the flash continuously.
Pros - Tons of options including an option to darken the screen while in use.  When in darkened mode it shows your battery usage so you can keep an eye on things.  It includes standard flashlight functions like auto SOS message through Morse code, manual Morse code, and strobe (more just for fun).  There's also an option to control the intensity, so it can be turned down to make your battery last longer.  It's also free.
Cons - Like any other flashlight app, it will consume battery while it's on.  You can't use other apps and keep the flashlight on.  This is not unique to this flashlight app and has to do more with iOS' so called 'multitasking'.

Automating iTunes

In order to train my kids to go to sleep with ambient noise and without I wanted to setup a way to have music play in their room on some nights and no music playing on other nights.  Given that the holiday season is here, I decided to go ahead and document how I did this.

First of all, I put a small form factor PC in their room on one of the bookshelves behind the books.  I hooked up some cheap speakers to it and installed iTunes.  This was enough to get started.  Since iTunes on that PC was joined to my home share, it was easy to control the music using my iPhone.  There were several problems with this: 1) I had to remember to turn it off or it would be going all night, 2) I had to carve out a section of my iTunes library that would not wake the boys up, and 3) I sometimes had to turn on the music while I was holding a sleeping baby (not easy).  So, I decided to automate things.  A quick Google search led me to Maximize Software's collection of Windows iTunes scripts.  After unpacking the scripts and familiarizing myself with the various options, I started building a batch file that would automate things.

Let's define the objectives:

1. I want the music to come on and turn off by itself - this should be pretty easy to do using a scheduled task.
2. I don't want the music to start off at full blast in case we've put the boys down early.    
3. I want the music to play whenever we put the boys to sleep, which could be as early as 7pm but as late as 9pm.
4. I want to be able to rotate playlists so it's not the same thing every night.
5. I want to be able to kick off the music any time.
6. I want to be able to manually override either the current song or the current volume level.

So, the first thing I did was copy all the music I wanted to play into that computer's iTunes library.  I wanted to be able to use my computer independently of the nursery music, so that meant all the music needed to be local.  The next thing I did was setup a couple of playlists.  The first is called AllMusic and is a smart list with the following criteria: Media Kind = Music & Playlist is not 'Waves' & Playlist is not 'Christmas' & Location = 'on this computer'.  Waves and Christmas are the other two playlists.  Christmas is also a smart play list with the following criteria: Media Kind = Music & Genre = Christmas & Location = 'on this computer'.  Waves is a manual list that contains a single 1 hour 8 minute MP3 of waves crashing on a beach.  The idea behind the playlists is that each playlist gets a night and there would also be one quiet night, setting up a rotation of 4 nights.

The next thing to do was to setup the batch file to play any given playlist, ramp the music up, and ramp the music down.  Here's the batch file:

@echo off
SetVolume.vbs 0
PlayPlaylist.vbs "%1"
for /L %%A IN (0,1,100) DO (
 echo.Increasing volume by 1/sec.
 call:WAITER 1
 SetVolume.vbs +1
)
call:WAITER 7200
for /L %%A IN (100,-1,0) DO (
 echo.Decreasing volume by 1/10 sec.
 call:WAITER 10
 SetVolume.vbs -1
)
Stop.vbs
SetVolume.vbs 100
GOTO:EOF

:WAITER
echo.Waiting for %~1 second(s).
choice /C x /N /T %~1 /D x > NUL
goto:eof

Let me break it down:

@echo off
SetVolume.vbs 0
PlayPlaylist.vbs "%1"

This section starts things off by turning off screen echo, setting the starting volume to 0, then starting to play the playlist provided as the first parameter when calling the batch file.

for /L %%A IN (0,1,100) DO (
 echo.Increasing volume by 1/sec.
 call:WAITER 1
 SetVolume.vbs +1
)

This section ramps up the volume over 100 seconds.  It increases the volume by 1% every 1 second.  This section makes the first use of the WAITER function shown next.  I chose to increment the volume instead of setting the volume to a specific level so that I could manually override the volume.  This means I could use my phone to increase the volume to 100 immediately instead of waiting 100 seconds.  If the script tries to increase the volume to something past 100, the volume just stays at 100.  If this for loop were in its 20th iteration and I had set it to increase to a specific level each time, then if I immediately pushed it up to 100, the next second would push it back to 21.  Not desirable, especially when ramping down the volume.

:WAITER
echo.Waiting for %~1 second(s).
choice /C x /N /T %~1 /D x > NUL
goto:eof

The waiter function simply waits a predetermined number of seconds.  So, if I wanted to wait X seconds, I would just call waiter like this: call:WAITER X.

call:WAITER 7200

This section waits for 7200 seconds which is 2 hours.  This is my 2 hour window; if I start the music at 6:45, it'll still be playing even if we put the boys to bed late.

for /L %%A IN (100,-1,0) DO (
 echo.Decreasing volume by 1/10 sec.
 call:WAITER 10
 SetVolume.vbs -1
)

This section uses the waiter function again to ramp down the volume over 1000 seconds (or 16 minutes and 40 seconds).

Stop.vbs
SetVolume.vbs 100
GOTO:EOF

This section cleans up after everything is over, making the whole thing ready to manually play music during the day if I wanted to.

I put this patch file into the same directory as the scripts and setup 3 scheduled tasks, each starting on a different day and repeating every 4 days:

1. sleepitunes.bat AllMusic
2. sleepitunes.bat Christmas
3. sleepitunes.bat Waves

This has worked pretty well for us.  I've toyed around with doing something like this as an alarm clock, but since the boys were born, I haven't really needed an alarm clock.  However, it wouldn't be very difficult.  The ramp up rate could be changed simply by changing the first call to the waiter function from 'call:WAITER 1' to 'call:WAITER 10'.  

NPC and the footer.text Property

Often when displaying report data, it is handy to have a little explanation about the data being presented.  In the case of NPC, this is accomplished by means of the footer.text property.  As it turns out, this footer.text is HTML based and can use some fairly advanced HTML features.

I say this can be done in NPC, but it can't; at least unless you jailbreak NPC.  At this point, CA has decided that CAPC will replace NPC.  Currently, there's no upgrade path and many of the features that some customers rely upon haven't been rebuilt into CAPC (remember CAPC is a rebuild from the ground up, so just because it was in NPC doesn't mean it will be in CAPC). I've already posted on the community about some of the features that I require before I will upgrade myself or anyone else to CAPC (browser view for example).

However, since NPC now has one foot in the grave (CA is evangelizing IM2.0 which requires CAPC more than Jim Jones' kool-aid), I see no reason why anyone wouldn't want to start experimenting with some of the hidden features in NPC.  So, if you haven't jailbroken NPC yet, you might consider it.  However, if you don't have NV as a data source, don't worry about it.  The NV view wizard is all you really get.  While that opens tons of doors to customization you never thought possible, it doesn't do anything for non-NV customers.  If you're interested, email me.  Once CA puts the last nail in NPC's coffin, I'll publish the steps here.  Until then, I need deniability.

So, on to the topic of this post.  When using the view wizard to create/edit NV views in NV (or NPC if you've jailbroken it) it turns out you can put some fairly complex html in the footer field.  Obviously, you can use {Resolution} and {Samples} in there to pull the current resolution and data point count (whether it's sampled data or not).  However just today I tried and was able to put a fully formed html table in the footer. I then tried to take it a step further and embed a YouTube video.  That worked fine as well.

In case you don't see it, this can be useful when trying to explain to new users what certain views mean.  For example, I could put together a complete page with live data and YouTube videos or text explaining what each one means and how to use it.  For example, I could put some text or an image in the footer that could expand a DIV section containing a YouTube video explaining how to interpret the data.  The nice part about this is that the footer is tied to the view.  Previously, I would put a separate browser view embedding the help information.  This is good for situations where you want to put the data on the left and explanation on the right.  However, putting the explanation in the footer ensures that the explanation stays with the data even through moves and copies.

So in order to put a hidden YouTube video in the footer of a view:

1. First check to make sure the view supports a footer (most do, this is just a sanity check).
2. Install HeidiSQL and connect to your NPC server.
3. Go to the control_properties table and filter it to only show your view.  Get the controlid from the NPC web gui by looking at the status bar when you open the view menu and mouseover the edit option.  Then set a filter for controlid=X, pageid in (0, Y), and propertiesid in (0, Z).
4. Find the footer.text property.  
5. Edit the propertyvalue column and insert your html.  
6. Save the record and you're good to go.

If you change the footer.text where pageid=0, propertiesid=0, and userid=0, you are modifying the default definition of the view and any existing views tied to the default settings and any new views will have this modified setting.

Here's the code you would insert if you wanted to embed my SA video.  You'll need to take out the carriage returns to make it one long line:

<script language="javascript"> function toggle(showHideDiv, helpimg){ var ele=document.getElementById(showHideDiv); var img=document.getElementById(helpimg); if(ele.style.display=="block"){ ele.style.display="none"; img.src='../npc/images/expand.gif' }else{ ele.style.display="block"; img.src='../npc/images/contract.gif' } }</script> <a href="javascript:toggle('hiddendiv','helpicon');"><img border="0" id="helpicon" src="../npc/images/expand.gif" /> Show help</a> <div id="hiddendiv" style="display: none;"> <iframe allowfullscreen="allowfullscreen" frameborder="0" height="315" src="http://www.youtube.com/embed/9vPZRViW43c" width="420"></iframe></div>

Windows 8 Highlights

I've been watching (mostly passively) the development and announcements around Windows 8.  Microsoft's new flagship desktop operating system will be unleashed sometime later this month.  I say unleashed, but I really should say released again since there was a developer's preview version of the operating system, a consumer preview version, and a release preview version that were released previously.  In my case, the multiple previews actually cemented a prejudice I have against MS' radical new ideas in their first iteration.  I actually downloaded and installed the developer preview and it told me everything I needed to know about Windows 8: it's the next Windows ME, or Windows Vista and I won't be rushing to it any time soon; Microsoft has tried to do something radically different, but they never get it right on the first try.  It's always the iteration afterward that hits pay-dirt (right after ME was XP and right after Vista was 7).
Either way, Michael Mace put together a great introductory video explaining what changes with this version of the operating system.  Here it is.

NQSync

Here's another one of my tools.  I found myself constantly needing to make sure the supportcig.exe is up to date on all my servers.  I also spend a lot of time tweaking my nqbackup.bat script, and every time there's a change, I need to copy it out to all my servers.  I also ended up trying to organize the mess that is the huge quantity of supportcig files distributed across all my servers.  So, of course, I scripted it.



NQSync.bat does several things I'm proud of.  First of all, it finds two unused drive letters on the system on which it's being run, starting with Z.  It does this so that the script can temporarily map network drives out to all the target systems and to the master system.  I should explain: the script can be run from anywhere as long as the credentials used to run the script have admin rights to all the servers.  First it maps a drive to the master server's D: drive.  Then it maps another network drive out to each of the target servers in turn (this is why we need two unused drive letters).  Then, depending on the options will do three actions:

• Update the supportCIG.exe file on the remote server - the script will copy the supportCIG.exe file from the master's D:\SupportCIG\ directory to the target server(s) D:\SupportCIG\ directory.
  To enable this option, include the switch /u anywhere on the line when calling the script.
• Gather the supportCIG output files onto the master server - the script will copy any zip files found in D:\SupportCIG\ to the master server into the D:\SupportCIG\[Server]\ directory, where [Server] is the server's name or IP address.
  To enable this option, include the switch /g anywhere on the line when calling the script.
• Synchronize tools to the remote server - the script will the entire contents of the D:\DBTools3 and D:\SupportTools6 directories out to the remote server in the same location.  This is how I get a copy of my updated nqbackup.bat file out to all the servers.
  To enable this option, include the witch /s anywhere on the line when calling the script.

The script itself will need to be modified to identify the master server.  Just go to line 2 and put the name or IP address of the master server.  Also, you'll need to provide a text file containing the name or IP address of each target server (one per line).  Obviously, it doesn't need to contain the master server's IP address.  Make the path to this text file one of the arguments.  For example:

nqsync.bat /u /g D:\lists\allservers.txt

This would update the CIG and gather all the outputs for all the servers in the text file.  Obviously, if the text file is in the same directory as nqsync.bat, you can exclude the full path and just put the name of the file.

NQSync.bat uses some fun code to find the first and second available drive letters.  I've tested this by using this script.  It identifies which drive letters are in use and which are available.