Newb Question

[Jesse_Munos]

Ladies and Gentlemen

So here is the issue I am facing. My company manages the networks of several other companies. We are constantly adding more companies to our client list. We use a number of different tools to monitor the health of the networks we monitor, Each of these tools reports to consoles located one a server system we place on our client networks. We collect data from antivirus, active directory, windows update services, a web based monitoring tool, and a disk health tool.
Each of these tools categorizes the data it collects based on the computer name which simplifies the data aggregation when done by hand. Each of these tools also exports to a .csv or .xls format allowing for relatively easy importation.

I have been assigned the task of building a database to automate the aggregation that is needed and thus drastically reducing the time needed inspect for problems on the client network. However I have never designed a database such as this before and I am finding it exceedingly difficult to discover the proper layout for tables and relationships in order to ensure we have the data we need and that it is being properly imported to ensure that all problems on the client network are identified.

Each tool exports as follows:

AD:
Computer name
modified date
discription

AV/AS (we use several different AV utilities but they all have these common fields):
Computer Name
Scan date
av def #
as def #
last report date
and several other fields

WSUS:
computer name
number of updates needed
number of updates failed
last report date

a web report agent: (my boss would like this to be the "primary index" of all computers we manage, as all units regardless of company report to this tool)


company name
computer name
agent version number
and several other fields

disk status tool:
computer name
report date
tool version
drive letter
(each computer name typically has more than one drive letter, and each drive letter has several fields associated with it when this data is imported all the previous fields are properly enumerated.)

Now there are two problems with the various tools. At any given time one tool may contain data on a computer none of the others know of. This happens for many reasons but primarily because when old systems are retired, they may not always be removed properly, and/or the data may not be cleared for whatever reason. Secondly each tool can have gaps in its data, IE a date field may be left blank or only have a "-" in it if the unit has never reported in.

Now I can get the data to import, and I have found various ways to link the tables together. The issue that I am having is how exactly I can import the .csv files given the amount of data replication it creates, and maintain referential integrity.

I would greatly welcome any advise or help anyone can offer me.

[June7]

Are you just importing new records or also having to update existing records? Why is referential integrity a concern? If the tables all have the computer name, there is a common key. What is the replication? Do you want to provide data sample, a workbook would serve.

[Jesse_Munos]

With each set of new data we will be importing new sets of data, updating previous records will not suffice as my boss wants to have the records going back at least one year.
As to why referential integrity is a concern, perhaps it isn't, my understanding was that in order to ensure that all pertinent data is collected and available to query and report on using referential integrity was essentially the only way to go. As to the replication, is not the computer name field itself replication? That is primarily what I was talking about. As for sample see the attached workbook. There are different variations of AV (I assume each would have its own table). Also most sections have more data than is listed in the sample however these pieces are the ones that we have chosen as metrics to date. My boss would like ALL of the data stored, but it is not necessary, just a preference.

example datagather data.xlsx.zip

[June7]

Your sample shows all data on one worksheet with multi-row headers. This does show the computer name field replicated in multiple columns but they are the same names. I thought you said earlier this would not always be the case.

Is this how you receive it? How are you accomplishing the import?

Did you say none of this data will be updates of records already in db, will be all new data and there will be no duplication of records?

Why would different AV need separate tables? Is the data actually different?

Clients can add and remove computers from their network without advance notifying you? So new computers will become known to you only after reported by one of these tools? Will the Web Report Agent always show a computer that is reported by any other tool? This is the main complication I see for relational integrity. Somewhere you need a 'master' table of computers that will have a unique record for each computer. Can each computer have multiple agent version numbers? Is there a chance that computer names could be the same for different companies?

[Jesse_Munos]

I will try to answer your questions in order.

The computer names will always be the same across each row, IF each tool has data for a computer, if the tool is not installed on a unit there may not be any data from that tool, as such there would simply not be an entry to import in the csv file. There will never be a case where two tools have different names for the same unit all computer names are grabbed via AD and as such remain constant across all tools. Example: if the AV portion of one of those two units is broken there may be no data (including computer name) for that unit. Or another example might be that a computer has been retired thus no data exists within AD (or at least no data that we currently query, we do not currently pull data from the retired renamed OU though there is no reason we could not do so), but there may still be a record for that computer in the AV console.

Each set of data is received by one of two methods. Either we export the data from the console in the form of a .CSV file, then open that csv in excel and cut and paste the data into a pre-built spreadsheet, or we cut and paste directly from the console. In my experiments I have been able to import .csv files with all the data into access without issue. As for the cut and paste data, I am undecided as to the best method to import this data into the database, it may be best to create forms to do the job, or we may have to paste into excel and then import the excel sheet. My preference would be forms as I really hate doing things in round about ways, doing so IMO invites user error.

I had planned to give each AV its own table because they do not all do reporting in the same way, nor do all of them have the same type of data being collected. EX: AV1 has only scan dates and computer name, no virus def numbers, AV2: has scan date, and def numbers for both AV and AS as well as a scan date

Many of our customers have their own IT staff and hire our company to supplement their own. As such we often have customers that add and remove units without informing us. There are times when new computers do not become known to us until our tools start reporting on them. As for a master list the web base monitoring tool is the most comprehensive list we have. When new units are added to the domain the group policies automatically push the install of this web based client to the unit. This install is not 100% more like 75 or 80% successful. The web agent typically, 90% of the time, shows computers if they are being reported by other tools. Sometimes this listing is not up to date as we annually prune back stale records, however once the data is imported to the database there is little to no reason to remove it from the imported data, it would be simple enough to add a check box to indicate active. There is never a case when a unit has more than one reported web agent version.

Sense we have multiple different customers and each customer likes to name their own computers via their own scheme, or we inherited the network with the names already configured, we sometimes run across two units at different companies that have the same name which poses yet another difficulty.

[June7]

A very thorough response. You confirm the possibility of duplicate computer names across companies so the name alone cannot be depended on as unique identifier. Unless you can assign a unique ID that each tool will use in common to recognize each computer reported, I can only see that the pairing of client name/id and computer name will have to serve to control relational integrity. I normally do anything to avoid working with compound pk/fk but appears your situation might demand it. However, I don't see the company name or ID in each tool's output. Will this data download and import be for a single client at a time?

Does that address your confusion? Do you have specific questions about the mechanics of importing data and maintaining the 'master' table of computers?

[Jesse_Munos]

each set of data will be imported separately except for the web probe data. The web probe data will be imported all at once. The web probe data does have attached company information. However none of the other imports will have this.
This has lead to one piece of my confusion. When importing this data, given that there is no company associated with the data to start with, how will I import the data in order to get the best results and maintain referential integrity? Can I build forms where I can select the company and have the form automatically associate the data with that company? If I take the route of using a form, will I be forced to cut and paste each record individually or can I use the form to import hundreds of records at one time?

[June7]

If the import will be for one company then my suggestion is to accomplish the import as you are already doing successfully. Then immediately after, select the company from form (or possibly automate determining company name from the imported web probe data) and UPDATE those records with that name. The WHERE criteria for the UPDATE would be: company Is Null.

[Jesse_Munos]

The way you have recommended seems like it would work just fine for a single user. However, this database will need to be accessible to at least 6 users at one time. As such is it not likely that with more than one person dong imports the data would become assigned to the wrong company by virtue of having been imported at the same time as someone else is importing data?

[June7]

Yes, that would be an issue if allowed.

The alternative would be import coding that adds the company name to each record as it is read from the csv file and then save to table. Actually, I can now see possible use of a single field as unique pk/fk. The code would use the combination of computer and company names to lookup the associated primary key in the 'master' table and then save that value as foreign key in the imported records. If not yet in the 'master', then new record would be created.

[Jesse_Munos]

Can you direct me to a resource on how to build import coding of this type?

[June7]

Using VBA to import text file is a common topic. Search forum or google. Here is a start: https://www.accessforums.net/showthr...ht=import+text