Mac address count data

For the first data set, we queried MAC address counts from the AANTS database for the last 30 days. There were approximately 32,000 subnet records in the AANTS database at the time we ran the model. To be useful in this analysis, MAC address counts had to be mapped to a UDDS code; historically this has been a challenge for the UW since AANTS does not tie a subnet record to UDDS. Each subnet is, however, tied back to the staff members who administer it. And from these staff members we can query UW’s WhoIs database to glean the UDDS code for each of those staff members. This isn’t always reliable, as the staff member isn’t necessarily a member of the UDDS the subnet belongs to. For example, SMPH network staff are listed as administrative and technical contacts for almost every SMPH subnet; but those staff members have a UDDS code corresponding to SMPH Administration rather than the subnet's actual SMPH department. Likewise, DoIT Departmental Support (DS) administers many departments’ endpoints on behalf of those departments, erroneously resulting in a UDDS code of A06.

Subnet-to-UDDS correlation

To overcome these challenges, we combined the contact-based UDDS predictions with a natural language processing (NLP) model based on document similarity. The subnet names and descriptions were quantitatively compared with terms scraped from websites mapped back to a particular UDDS. To calculate the “best” UDDS match, the UDDS with the smallest Jaccard distance was weighted more heavily than those with larger distances. Additional institutional knowledge was also used to weight some UDDS predictions more heavily than others. The resulting data included a set of records for each subnet-UDDS combination ordered by the record having the UDDS with the highest probability of being correct.

FTE counts

With the aim of correlating server counts directly to FTE counts, we also collected March 2022 FTE counts from UW’s Tableau instance. This data was a simple listing of seven-digit UDDS code along with FTE count rounded to one decimal place.

Qualys and Cisco Secure Endpoint counts

Counts of Qualys and Cisco Secure Endpoint deployments for each campus unit were obtained by querying each application's API.

Units with the most complete data

In order to estimate device counts for units that have incomplete or no data, we gathered a list of units that have full or relatively full deployments of either Cisco Secure Endpoint or Qualys; there were forty-seven such units, a sufficiently large sample size with which to make further predictions. We correlated device counts from those departments to MAC address and FTE counts to evaluate whether a statistically significant relationship exists. We then fit a linear model to the data and, using the coefficients of the model, estimate device counts for the remaining campus units for which we have no data or incomplete data.

Data wrangling

The data needed to be grouped by division code and aggregated. The linear model attempts to model endpoint counts based on real counts, but the real counts available to us consisted of separate Qualys and Cisco Secure Endpoint counts. A single number was needed for the “real” count. For this, we used either the Qualys count or the Cisco Secure Endpoint count, whichever was higher. We called this single number the “endpoint count.”

Plots

Scatterplots were generated to visually compare MAC address counts and FTE counts to endpoint counts. As shown, endpoint counts are generally proportional to both MAC address and FTE counts.

A boxplot of endpoint counts against the "large_unit" classification illustrates good correlation, with only a few outliers at the high end of each plot.

Linear modeling

For a first pass, we tried fitting linear models to various combinations of predictors without doing any mathematical transformations. Endpoint counts were fitted against the following predictors (a plus sign indicates an additive relationship, while a colon indicates a multiplicative interaction between predictor pairs):

mac_ct
fte_ct
fte_ct + mac_ct
fte_ct + mac_ct + fte_ct:mac_ct
mac_ct + large_unit
fte_ct + large_unit
large_unit + fte_ct + mac_ct
large_unit + fte_ct + mac_ct + fte_ct:mac_ct
fte_ct + large_unit + fte_ct:large_unit
fte_ct:large_unit
fte_ct + large_unit + doit_unit
fte_ct + large_unit:fte_ct + doit_unit:fte_ct
fte_ct + large_unit:fte_ct + doit_unit
fte_ct + large_unit + doit_unit:fte_ct
fte_ct + large_unit:doit_unit

Using commonly accepted statistical approaches, we analyzed the best-performing models and looked for leverage points (outliers), i.e., points that have a strong influence over the resulting model. The following summarizes the model results:

When evaluating a model's performance, a number of factors must be considered. First, only predictors that are statistically significant should be included in the model as coefficients. Next, the model's residuals (how far away the data deviate from the line of best fit) must be homoscedastic and normally distributed. That is, they should vary more or less constantly and should not exhibit any patterns. If the residuals are heteroscedastic, this typically indicates the model isn't valid and that a mathematical transformation should be performed. Residuals that are not normally distributed could indicate an invalid model but are generally less of a concern when there are at least ten observations per variable (which is true of our data set).

After one or more models have been validated, the best model can be selected by either maximizing the adjusted R-squared value or minimizing the mean squared error (MSE). However, as shown in the table, none of the models yielded homoscedastic residuals. This can be visually illustrated by an example residual plot from the first model run:

As shown on the plot of residuals vs fitted values, there is noticeable heteroscedasticity, which invalidates the model. This is confirmed quantitatively by the Breusch-Pagan test, which generated high test statistics for the models as well as a low p-value. For a model to be considered homoscedastic, a low test statistic and p-values greater than or equal to 0.05 are required.

Transformations

Because the models thus far resulted in heteroscedastic residuals, a common approach to correct for this is to mathematically transform one or more of the variables. We tried several transformations on the response variable (endpoint count), including log, square-root, and cubed-root. We also used a commonly accepted approach to estimate powers to which a predictor could be raised to maximize best fit. We used this approach (the Box-Cox method) on some of the better-performing models to evaluate whether any improvement could be made.

The following summarizes the model runs that exhibited homoscedastic, normally distributed residuals, ordered by the highest adjusted R-squared score:

Predict endpoint counts

Using the selected model, we predicted endpoint counts based on the predictor variables that exhibited the best correlation (FTE counts, large_unit, and doit_unit).

Deployment counts of Qualys and Cisco Secure Endpoint were also estimated per departmental UDDS code, but to preserve anonymity these itemized counts are not shown here.

Conclusion

In conclusion, there is good statistical evidence to support a very strong linear model correlating endpoint count to FTE count, with additional categorical variables of large_unit and doit_unit. Commonly used techniques to evaluate the model’s residuals indicate that the model is valid. However, the 90% prediction interval includes a wide range of values, resulting in a total predicted endpoint point estimate of 86,100, with lower and upper bounds of 43,600 and 169,000, respectively. Based on current deployment totals, we estimate the campus to have deployed Qualys and AMP to approximately 40,900 computers (47.5%).