Thursday, February 14, 2013

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:


To create the view, run the following SQL commands against the NPC server:

Wednesday, February 13, 2013

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.

Time1 Minute Data15 Minute Average
0:003751.6666666666667
0:0174
0:0249
0:0321
0:0459
0:0515
0:0632
0:0769
0:0840
0:0926
0:1087
0:1138
0:1296
0:1379
0:1453
0:156025.8
0:1635
0:177
0:1824
0:1946
0:203
0:2122
0:221
0:2339
0:249
0:2534
0:260
0:2742
0:2834
0:2931
0:30433.8
0:315
0:3281
0:3336
0:3435
0:359
0:3636
0:3741
0:3876
0:3993
0:4021
0:4112
0:4210
0:4321
0:4427
0:45548.8
0:4642
0:4783
0:4848
0:4918
0:5079
0:5124
0:5278
0:5319
0:545
0:5552
0:56100
0:5739
0:5870
0:5970
1:001444.0666666666667
1:0145
1:0282
1:0334
1:0474
1:0590
1:0633
1:0710
1:0843
1:0924
1:1096
1:1112
1:1217
1:1362
1:1425
1:159654.7333333333333
1:1619
1:1791
1:1858
1:1976
1:2024
1:2186
1:2235
1:2348
1:2431
1:2567
1:2672
1:279
1:2845
1:2964
1:307940.4
1:3168
1:320
1:3310
1:3430
1:3523
1:3665
1:3774
1:3853
1:3978
1:404
1:413
1:4238
1:4378
1:443
1:459147.6
1:4685
1:4744
1:484
1:496
1:5013
1:5139
1:5293
1:5352
1:5435
1:5549
1:5669
1:5767
1:5864
1:593
2:001156.8666666666667
2:0172
2:0254
2:0345
2:0416
2:0517
2:0675
2:0796
2:0896
2:0962
2:1080
2:1155
2:1296
2:1334
2:1444
2:157346.6666666666667
2:1657
2:1721
2:1861
2:1928
2:2011
2:2116
2:221
2:2386
2:2423
2:2564
2:2689
2:2746
2:2838
2:2986
2:304245.8
2:3115
2:327
2:333
2:3440
2:3541
2:368
2:3766
2:381
2:3985
2:4059
2:4189
2:4294
2:4342
2:4495
2:456457.6
2:4658
2:476
2:4814
2:4917
2:50100
2:5185
2:5272
2:5393
2:5458
2:55100
2:5632
2:5753
2:5836
2:5976
3:00438.7333333333333
3:0119
3:0232
3:0314
3:0484
3:0526
3:0617
3:0765
3:0820
3:0915
3:1026
3:1152
3:1279
3:1331
3:1497
3:15855.6666666666667
3:1647
3:1787
3:1866
3:1923
3:2088
3:2128
3:2253
3:2387
3:2466
3:2532
3:2671
3:2787
3:2810
3:2982
3:308454.9333333333333
3:3181
3:3254
3:3388
3:3477
3:3598
3:362
3:3715
3:3892
3:3972
3:4016
3:4145
3:424
3:4360
3:4436
3:458463.4666666666667
3:4697
3:4787
3:4829
3:4953
3:5011
3:5184
3:5283
3:5376
3:5464
3:5550
3:5636
3:5783
3:5838
3:5977
4:00364.1333333333333
4:0179
4:0290
4:0391
4:0446
4:0553
4:0626
4:0777
4:0846
4:0962
4:1060
4:1193
4:1278
4:1392
4:1466
4:153537.1333333333333
4:1681
4:1742
4:1850
4:1914
4:2026
4:2172
4:224
4:2315
4:2465
4:2514
4:269
4:2710
4:2838
4:2982
4:301451
4:3148
4:3262
4:3336
4:3489
4:353
4:3653
4:3758
4:3886
4:3971
4:4049
4:4167
4:4220
4:4396
4:4413
4:45047.3333333333333
4:4691
4:4764
4:4828
4:4996
4:5023
4:5137
4:5237
4:5377
4:5415
4:5513
4:5698
4:577
4:5858
4:5966
5:004848.6
5:0122
5:025
5:0376
5:0496
5:055
5:0610
5:073
5:0834
5:0956
5:1082
5:1196
5:1258
5:1380
5:1458
5:154848.1333333333333
5:1630
5:1750
5:1871
5:1912
5:2012
5:2198
5:2224
5:2341
5:2421
5:2566
5:2624
5:2799
5:2873
5:2953
5:30837.3333333333333
5:3167
5:3217
5:330
5:340
5:3537
5:3651
5:3733
5:3828
5:3958
5:403
5:4174
5:4279
5:4390
5:4415
5:455655.7333333333333
5:4682
5:4766
5:4864
5:4969
5:5094
5:5181
5:5223
5:5322
5:5441
5:5516
5:5689
5:5747
5:5874
5:5912
6:007049.2666666666667
6:0172
6:0276
6:0328
6:0462
6:0528
6:0646
6:0762
6:0861
6:0934
6:1052
6:1169
6:1212
6:1337
6:1430
6:157951.8666666666667
6:1635
6:1774
6:1871
6:1912
6:2052
6:2172
6:226
6:2389
6:2416
6:2517
6:2630
6:2764
6:2876
6:2985
6:304742.8666666666667
6:3144
6:3246
6:3346
6:342
6:3583
6:3612
6:3724
6:3847
6:3994
6:4032
6:4170
6:4270
6:4324
6:442
6:457754.2
6:461
6:4727
6:4847
6:4918
6:5075
6:5146
6:5283
6:5358
6:5424
6:5558
6:5681
6:5765
6:5857
6:5996
7:003466.8666666666667
7:0199
7:0279
7:0375
7:0489
7:0592
7:0663
7:0747
7:0849
7:0993
7:1045
7:1140
7:1297
7:1341
7:1460
7:155652.0666666666667
7:1671
7:1792
7:1831
7:1968
7:201
7:213
7:2264
7:2321
7:247
7:2582
7:268
7:2799
7:2896
7:2982
7:309751.4
7:315
7:321
7:3382
7:3467
7:3530
7:3629
7:3789
7:3853
7:3980
7:4014
7:4188
7:4273
7:4336
7:4427
7:455347.4666666666667
7:4647
7:4755
7:484
7:4946
7:5065
7:5147
7:5221
7:5325
7:5451
7:5587
7:5624
7:5746
7:5875
7:5966
8:006341.5333333333333
8:0146
8:0269
8:0358
8:0414
8:0521
8:0683
8:0775
8:0823
8:0912
8:1060
8:1163
8:126
8:139
8:1421
8:155340.6
8:1642
8:1722
8:180
8:1965
8:206
8:2166
8:2216
8:2385
8:248
8:2578
8:2644
8:2786
8:2830
8:298
8:309868.3333333333333
8:3181
8:3293
8:3366
8:3460
8:3527
8:3684
8:3762
8:3881
8:3976
8:4094
8:4119
8:4260
8:4338
8:4486
8:459247.7333333333333
8:4639
8:4718
8:4849
8:4936
8:5057
8:5159
8:5284
8:538
8:5436
8:5514
8:5648
8:5751
8:5867
8:5958
9:007344.7333333333333
9:0190
9:0259
9:032
9:041
9:052
9:0633
9:0751
9:0833
9:0981
9:102
9:1193
9:1279
9:1318
9:1454
9:158748.8666666666667
9:1675
9:1733
9:1819
9:1924
9:2014
9:2145
9:2213
9:2393
9:2454
9:2569
9:2612
9:272
9:2899
9:2994
9:304244.4666666666667
9:3170
9:3283
9:3320
9:3466
9:3510
9:3635
9:3774
9:3818
9:3935
9:407
9:4164
9:4267
9:4355
9:4421
9:454853.4
9:4691
9:4788
9:483
9:4911
9:5045
9:518
9:5220
9:5396
9:5417
9:5576
9:5678
9:5754
9:5899
9:5967
10:001853.5333333333333
10:0152
10:0233
10:0319
10:0465
10:0588
10:0656
10:0799
10:0818
10:0998
10:1050
10:1146
10:1242
10:1353
10:1466
10:156158.5333333333333
10:1684
10:1766
10:1844
10:1943
10:208
10:2126
10:2277
10:2355
10:2485
10:256
10:2691
10:2766
10:2866
10:29100
10:307446.3333333333333
10:3170
10:3210
10:3372
10:3435
10:3519
10:3686
10:3772
10:387
10:3931
10:4095
10:4135
10:4246
10:432
10:4441
10:454546.3333333333333
10:4685
10:473
10:4811
10:4948
10:5090
10:5159
10:5240
10:5326
10:549
10:5594
10:567
10:5770
10:5831
10:5977
11:006747
11:0178
11:0259
11:030
11:0470
11:0577
11:0671
11:0749
11:0851
11:0963
11:1021
11:1121
11:1250
11:1311
11:1417
11:15446.9333333333333
11:1654
11:179
11:1849
11:1930
11:2082
11:2141
11:2222
11:2333
11:2429
11:2578
11:2688
11:2737
11:2884
11:2964
11:302347.4666666666667
11:3150
11:328
11:3350
11:3424
11:3584
11:3653
11:3778
11:3880
11:392
11:4061
11:4189
11:4277
11:4316
11:4417
11:451936.2666666666667
11:4664
11:4785
11:489
11:4930
11:5024
11:515
11:5210
11:5359
11:547
11:5542
11:5621
11:5769
11:5855
11:5945
12:003748.0666666666667
12:0160
12:0285
12:0344
12:0428
12:0589
12:0635
12:0718
12:0848
12:0964
12:103
12:1138
12:1296
12:135
12:1471
12:157146.4
12:1693
12:1737
12:187
12:1938
12:2085
12:2128
12:2286
12:2312
12:2465
12:2538
12:266
12:2766
12:2824
12:2940
12:302642
12:3198
12:3274
12:3313
12:3446
12:3552
12:3639
12:3714
12:3838
12:3925
12:4028
12:4130
12:4244
12:4363
12:4440
12:459641.0666666666667
12:4613
12:4750
12:488
12:4943
12:5027
12:5138
12:5297
12:530
12:5411
12:5584
12:5673
12:5711
12:5820
12:5945
13:003960.1333333333333
13:0194
13:0295
13:0382
13:0439
13:0526
13:0680
13:0770
13:0819
13:0979
13:1074
13:1178
13:1216
13:1320
13:1491
13:155846
13:1667
13:1752
13:1855
13:1947
13:2025
13:218
13:2278
13:2353
13:2455
13:2543
13:269
13:2728
13:2856
13:2956
13:302627.7333333333333
13:315
13:3217
13:332
13:3453
13:359
13:365
13:3763
13:3891
13:3946
13:407
13:4123
13:4225
13:433
13:4441
13:459449.0666666666667
13:4670
13:4749
13:4860
13:4959
13:5051
13:5171
13:5216
13:5337
13:5431
13:5513
13:5664
13:5776
13:5830
13:5915
14:00032.5333333333333
14:016
14:027
14:0313
14:0452
14:0525
14:0653
14:0715
14:0865
14:0970
14:1072
14:1112
14:1223
14:1323
14:1452
14:158750.9333333333333
14:1698
14:1724
14:1860
14:1932
14:2031
14:2110
14:2263
14:2328
14:2420
14:2542
14:2690
14:2743
14:2881
14:2955
14:306053.7333333333333
14:3145
14:3298
14:3396
14:3411
14:3514
14:363
14:3797
14:3863
14:3929
14:4023
14:4196
14:4235
14:4389
14:4447
14:456758.4666666666667
14:4641
14:4766
14:4870
14:4998
14:5047
14:5137
14:5268
14:5361
14:5416
14:5570
14:5619
14:5746
14:58100
14:5971
15:009756.7333333333333
15:0165
15:0299
15:0360
15:0454
15:053
15:0660
15:0779
15:0830
15:0950
15:1037
15:1154
15:1251
15:1363
15:1449
15:152743.0666666666667
15:1610
15:1777
15:1882
15:1915
15:2055
15:2168
15:2274
15:2311
15:240
15:2572
15:2649
15:2762
15:2841
15:293
15:305464.8666666666667
15:3186
15:3295
15:3331
15:3445
15:3571
15:36100
15:3759
15:3843
15:3915
15:4088
15:413
15:4297
15:4388
15:4498
15:457649.7333333333333
15:4617
15:4782
15:4834
15:4964
15:5085
15:5152
15:5229
15:5360
15:5476
15:5516
15:5612
15:5764
15:5852
15:5927
16:008442.8
16:0110
16:0211
16:0313
16:0499
16:0529
16:0664
16:0729
16:0820
16:0964
16:1036
16:1114
16:1238
16:1369
16:1462
16:159652.0666666666667
16:1639
16:1710
16:1880
16:1988
16:2014
16:212
16:224
16:2354
16:2425
16:2578
16:2688
16:2799
16:2889
16:2915
16:307356
16:3168
16:3229
16:3343
16:3426
16:3554
16:3613
16:37100
16:3889
16:3933
16:4048
16:4135
16:4292
16:4356
16:4481
16:453050.1333333333333
16:4699
16:474
16:48100
16:495
16:5010
16:5137
16:5212
16:5392
16:5499
16:5595
16:5669
16:5720
16:5852
16:5928
17:004544.3333333333333
17:0127
17:0220
17:0335
17:0457
17:0588
17:0691
17:077
17:0866
17:0941
17:1020
17:1132
17:1277
17:138
17:1451
17:153853.6
17:1636
17:1739
17:1840
17:1915
17:2091
17:2145
17:2237
17:2399
17:2498
17:2554
17:2664
17:2797
17:2848
17:293
17:305550.6
17:3133
17:3271
17:3340
17:3465
17:3580
17:3646
17:3757
17:3843
17:393
17:4070
17:4130
17:4273
17:4363
17:4430
17:457967.4
17:4667
17:4749
17:4875
17:4998
17:5078
17:5183
17:5283
17:5354
17:5477
17:5546
17:5652
17:5766
17:5864
17:5940
18:009558.8666666666667
18:01100
18:0283
18:0361
18:049
18:0511
18:0635
18:0740
18:0875
18:0980
18:1014
18:1182
18:1237
18:1372
18:1489
18:154845.6666666666667
18:1640
18:1790
18:1827
18:1910
18:2065
18:2152
18:224
18:2329
18:2423
18:2529
18:2690
18:2792
18:281
18:2985
18:306263.2
18:3146
18:3299
18:3350
18:3461
18:3574
18:3690
18:3745
18:3891
18:39100
18:4093
18:4167
18:4219
18:4316
18:4435
18:458658.9333333333333
18:4664
18:4799
18:4826
18:4999
18:5019
18:5131
18:5293
18:5311
18:5474
18:5588
18:5636
18:5756
18:5868
18:5934
19:001869.3333333333333
19:0197
19:0226
19:0384
19:0498
19:0553
19:0679
19:0795
19:0872
19:0992
19:1024
19:1195
19:1286
19:1396
19:1425
19:155058.8
19:1688
19:1795
19:1820
19:1985
19:2041
19:2139
19:2245
19:2367
19:2439
19:2563
19:2698
19:272
19:2861
19:2989
19:309057.8666666666667
19:3174
19:3284
19:3398
19:3483
19:3525
19:3692
19:3772
19:3826
19:3934
19:4048
19:412
19:4270
19:437
19:4463
19:453853.7333333333333
19:4612
19:4744
19:4823
19:490
19:5080
19:5188
19:5288
19:5393
19:5434
19:5569
19:5615
19:5795
19:5885
19:5942
20:003735
20:01100
20:023
20:0317
20:045
20:0544
20:0699
20:0729
20:08100
20:092
20:105
20:1111
20:1225
20:1324
20:1424
20:156340.7333333333333
20:1681
20:1790
20:1844
20:1961
20:2018
20:2137
20:226
20:2385
20:241
20:2553
20:263
20:2754
20:285
20:2910
20:30954.4666666666667
20:3168
20:3289
20:3369
20:3474
20:3550
20:3684
20:3715
20:3858
20:3980
20:4030
20:4169
20:4286
20:4311
20:4425
20:45045.4666666666667
20:4649
20:4729
20:4865
20:4926
20:5062
20:5141
20:5265
20:5327
20:5478
20:5570
20:5617
20:5790
20:585
20:5958
21:002449.4
21:0195
21:0223
21:0316
21:040
21:0525
21:0670
21:0770
21:0885
21:0981
21:1081
21:1190
21:1244
21:139
21:1428
21:156753.1333333333333
21:1689
21:1754
21:1811
21:1917
21:2063
21:2197
21:2288
21:2395
21:2463
21:2565
21:2656
21:2713
21:283
21:2916
21:301455.8
21:3183
21:322
21:3373
21:3481
21:3570
21:365
21:3784
21:3863
21:3988
21:4033
21:4149
21:4290
21:4375
21:4427
21:454353.7333333333333
21:4667
21:4761
21:4817
21:4949
21:5032
21:51100
21:5293
21:5393
21:5453
21:5523
21:5616
21:574
21:5876
21:5979
22:004739.6
22:0146
22:029
22:0392
22:0467
22:052
22:0639
22:0738
22:0852
22:091
22:1015
22:1117
22:1278
22:1314
22:1477
22:157043.8666666666667
22:1658
22:1754
22:1871
22:190
22:2068
22:2110
22:2279
22:2350
22:2457
22:2549
22:260
22:2750
22:285
22:2937
22:305046.8
22:3127
22:3254
22:3339
22:341
22:3588
22:3623
22:3781
22:3870
22:3954
22:4039
22:4152
22:4233
22:432
22:4489
22:45558.9333333333333
22:4678
22:4739
22:4882
22:4979
22:5073
22:5136
22:5275
22:5381
22:5471
22:5540
22:5633
22:5790
22:5838
22:5964
23:001451.2666666666667
23:0127
23:0293
23:0380
23:0412
23:0598
23:069
23:0767
23:0887
23:0910
23:1015
23:1169
23:1260
23:1360
23:1468
23:152159.4666666666667
23:1686
23:1776
23:1821
23:1981
23:2031
23:2171
23:2268
23:2390
23:2463
23:258
23:2685
23:2795
23:2829
23:2967
23:304546.2666666666667
23:3171
23:3273
23:3358
23:3423
23:3516
23:3616
23:3779
23:3884
23:39100
23:404
23:4123
23:4255
23:4325
23:4422
23:45845.4
23:4627
23:4749
23:4822
23:4984
23:5046
23:5124
23:5219
23:5353
23:5432
23:5572
23:5669
23:5796
23:5820
23:5960
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̄ = (a1+a2+a3+...+an)/n

If you had multiple sets (i.e. 15 minute averages):
x̄ = {(a1+a2+a3+...+an)/na + (b1+b2+b3+...+bn)/nb + (c1+c2+c3+...+cn)/nc}/3

Since in this case na=nb=nc, then substitue n and factor out:
x̄ = {(a1+a2+a3+...+an) + (b1+b2+b3+...+bn) + (c1+c2+c3+...+cn)}/3n

Associative property of addition:
x̄ = (a1+a2+a3+...+an+b1+b2+b3+...+bn+c1+c2+c3+...+cn)/3n

Since in this case 3n = n + n + n and na=nb=nc=n, then substitue for n:
x̄ = (a1+a2+a3+...+an+b1+b2+b3+...+bn+c1+c2+c3+...+cn)/(n + n + n)
x̄ = (a1+a2+a3+...+an+b1+b2+b3+...+bn+c1+c2+c3+...+cn)/(na + nb + nc)

Knowing that na + nb + nc 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.