Best approach for a server configuration data collection and reporting solution from a legacy script?

Question

I have a project which involves collecting config data from windows servers in our (very large) server estate. My manager wants me to collect over 150 data items across all configuration areas including network config data, disks, registry values, BIOS version, dll versions, you name it. The server support team currently use a nasty vbScript script which is massive, messy and has been passed around and grown organically over time. The script is currently used to collect the data and produce a very basic flat HTML report with a column for each of the 150+ items it collects data for. My job is to tranform it into a proper software solution with new bells and whistles, a website, reports etc.

The idea is a sys admin will go to an intranet website, puts the server name in and presses the go button, the solution collects the data and pushes it into the DB for reporting later.

My manager wants the solution to collect the data, some of it referencing known desired values (e.g. RAID card drivers for ABC model servers is at v1.2.5.1) to see if they are correct or up to date, store it in a SQL database, and then use the front end web site for producing reports. In the reports, he wants it to be able to compare two datasets; for example, one for server ABC123 and one for server DEF987 in one report, highlighting any differences between the two.

It gets messy pretty quick as some data will be collected for on some servers but not others (domain controllers wont have data collected about exchange services running status). There are many miscellanous items which don't fit into a data model area and don't really have a home ... it's a mess.

I'm ok with collecting the data (using .Net), but I'm at a loss as to where the best place is to do such things as store and lookup/compare items to good known values (XML, DB?). Should I do it as part of the data collection phase and then push it into the DB. Or should I marry it all up as part of the reporting data?

I'm going with a data warehouse (de-normalised) schema for the DB. There will probably be about 20 tables which the data will need to be pulled from, so I have no real idea what the best way to compare the dataset from one server data collection, with another, with such a massive set of fields to compare.

What do you guys think my best options are for approaching this project and the architecure of the solution?

Answer 1

With the information you have provided , I would recommend using MS SQL server to store and compare the data. No XML or intermediate representations. Collect, transform and dump everything directly to the DB. If right now it is running on VBscript and MS SQL Server I am assuming it is not super critical.

The reads from the de-normalised schema could be fast, that will serve the reports faster when requested. Better than running join queries with a complex but leaner data model. Again a lot of depends on the sampling interval, and volume of data. I don't think it can grow more than a few million rows if we are talking about few days of data with 10 second sampling for some 100 servers. With required indexes in place it should be fast enough in MS SQL Server. You might need to define an archival strategy; or aggregate older statistics in a coarser granularity and push to some aggregate tables.

Answer 2

I'm ok with collecting the data (using .Net), but I'm at a loss as to where the best place is to do such things as store and lookup/compare items to good known values (XML, DB?). Should I do it as part of the data collection phase and then push it into the DB. Or should I marry it all up as part of the reporting data?

I would recommend it to do this way.

Make a CollectorService, which will run on client computers and collect data. It should store collected data locally in XML, YAML, JSON or whatever (personally I tend to use JSON these days).

CollectorService will query hardware, software or whatever, store data locally on client machine and then connect to central data directory and send data there. After receipt has been confirmed, local data can be deleted. Data should be transfered in the same format it was collected in (e.g. JSON).

Make ProcessorService, which will receive data from clients and store it in the central database. I would recommend Postgres for storage (more on that in a moment). ProcessorService will split data received from clients into two groups using the following rule: data, that is probably be queried often goes to separate schema/table/column. Data, which is going to be queried rarely, goes into JSONB column fields / subfields.

Now why Postgres. 9.4 version of this wonderful DB server can now handle JSON types in columns and what is more - it can define arbitrary indexes on JSON fields of any depth. So, you doesn't loose much storing data in JSONB column comparing to storing it in a separate one. So now Postgres speaks NoSQL too ;)

Now make AnalysisService. This one will incorporate all about data querying. This is what enduser will work with.