NetVoyant Device Inventory 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 export a list of all devices polled by NetVoyant to CSV.  This can usually be done by going to Inventory >> Devices and exporting to CSV from there.  However, that control has a limit of 5000 devices and we're monitoring more than that.  Luckily ODBC doesn't have that limitation.  Here's the SelectCommand and OdbcConnection String to put in the configuration.xml:

<selectcommand Name="DeviceInventory" Property="False" SupportedViewTypes="Table"> <![CDATA[ select convert(concat( '<a href=\'http://npc/npc/default.aspx?pg=', if(itemsubtypename='router','r',if(itemsubtypename='switch','sw',if(itemsubtypename='server','s','d'))), '&Deviceid=', itemid, '\'>', itemname, '</a>' ) using utf8) as dev_name, convert( v6_ntoa(address) using utf8) as address, convert( if(snmpversion=0,'Not SNMP Capable',snmpversion) using utf8) as snmpcapable, itemsubtypename, if( char_length(itemdesc)=0, null, if(char_length(itemdesc)<100,itemdesc, concat(left(itemdesc,100),'...')) ) as description from dst_device where sourceid=10; ]]> </SelectCommand> <odbcconnectionstring Name="NPC">Driver={MySQL ODBC 5.1 Driver};Server=192.168.10.10;Port=3308;Option=3;Database=netqosportal;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), 'NetVoyant Devices in NPC', '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 Devices in NPC'), (-1, 'N', 'DisplayColumns', 'dev_name;address;snmpcapable;itemsubtypename;itemdesc'), (-1, 'N', 'DisplayFormats', 'string,string,string,string,string'), (-1, 'N', 'DisplayNames', 'Device Name,Address,SNMP Version,Type,Description'), (-1, 'N', 'ColumnWidths', ',,,,'), (-1, 'N', 'HorizontalAlignments', 'Left,Left,Left,Left,Left'), (-1, 'N', 'SortField', 'dev_name'), (-1, 'N', 'SortDirection', 'Asc'), (-1, 'N', 'Integration.Parameter.SelectCommandName', 'DeviceInventory'), (-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','NPC'); UPDATE control_properties SET ControlID = (select min(id) from controls where datasourcetype=0x200000) WHERE ControlID = -1;

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