I usually a lot of logic with script/code, but I'm having to rely on access-sql a lot more, and i'm trying to do a couple of operations properly in sql, instead of hacking around it in VBA or Powershell

I'm trying to collect information from multiple sources on servers, I can't use a clearly set of attributes, and I need to be able to add attributes on the fly.

I've got the database setup, and the structure, but reporting off of it is a pain, I'm trying to automate a couple of things in order to keep all the data, but have usable data for some reporting/other operations


Table Structure
Table Structure

Table 1 Servers
ID (AutoNumber)
DisplayName (String)
Description (String)


Table 2 Attributes
ID (AutoNumber)
Displayname (String) # Usually something like IP
OWNERID (FK link to ID in Servers Table)
ATVALUE (String)


1)

How to find the most common result of attributes

I may have data like this (combined from 2 different tables)

SERVER1 IP_SCOM 192.168.1.1
SERVER1 IP_SCCM 192.168.1.1
SERVER1 IP_OTHR 192.168.1.1
SERVER1 IP_OLDE 192.168.1.2

SERVER2 IP_SCOM 192.168.1.2
SERVER2 IP_SCCM 192.168.1.3
SERVER2 IP_OTHR 192.168.1.3
SERVER2 IP_OLDE 192.168.1.3

In this case I'm looking for

SERVER1 IP_COMMON 192.168.1.1
SERVER2 IP_COMMON 192.168.1.3

I have this so far, that works to give me the occurrence of IP addresses for a given server, but I'm having a problem getting it to only return the one with the highest count value.

SELECT DISTINCT SERVER.DISPLAYNAME, SERVER.ID, Count(ATTRIBUTE.ATVALUE) AS CountOfATVALUE, ATTRIBUTE.ATVALUE
FROM SERVER INNER JOIN ATTRIBUTE ON SERVER.ID = ATTRIBUTE.OWNERID
WHERE (((ATTRIBUTE.DISPLAYNAME) Like "IP_*"))
GROUP BY SERVER.DISPLAYNAME, SERVER.ID, ATTRIBUTE.ATVALUE
ORDER BY SERVER.DISPLAYNAME, Count(ATTRIBUTE.ATVALUE) DESC;




2) Pivot the structure with text values

there currently is a list of about 20 different ATTRIBUTE Display Names.

I want to Pivot off of the ATTRIBUTE.DISPLAY Name and then use the attribute.value for the value

Referencing above assume the following data (between the SERVER and ATTRIBUTE Table)

SERVER1 IP_SCOM 192.168.1.1
SERVER1 IP_SCCM 192.168.1.1
SERVER1 IP_OTHR 192.168.1.1
SERVER1 IP_OLDE 192.168.1.2
SERVER1 OS WINDOWS
SERVER1 SN 12345

SERVER2 IP_SCOM 192.168.1.2
SERVER2 IP_SCCM 192.168.1.3
SERVER2 IP_OTHR 192.168.1.3
SERVER2 IP_OLDE 192.168.1.3
SERVER2 OS LINUX

Output
SERVERNAME IP_SCOM IP_SCCM IP_OTHR IP_OLDE OS SN
SERVER1 192.168.1.1 192.168.1.1 192.168.1.1 192.168.1.2 WINDOWS 12345
SERVER2 192.168.1.2 192.168.1.3 192.168.1.3 192.168.1.3 LINUX null

I won't actually have an idea of what all the column names will need to be, I'm thinking all of the distinct values of the ATTRIBUTE.DISPLAYNAME.

5/12/2014 6:52:31 PM

Dude

http://stackoverflow.com/

Not saying people here can't be of assistance, but you'll get a lot more action there.

5/12/2014 9:05:07 PM

1) Use a HAVING clause; it's basically a WHERE on a summary. You could also do an ORDER BY DESC and use TOP 1, but that would not work if there were multiple display values whose number of entries are the same and are the maximum...

2) If you don't know all the column names, then you can't use a conventional PIVOT, at least not one that I'm aware of. It's a limitation of PIVOTs. You could use dynamic SQL to build the PIVOT statement, but at that point, you might as well just hack it out in Access or Crystal Reports.

5/12/2014 10:34:44 PM

I'm having trouble reading your English, but I think you fucked up designing your tables.

5/13/2014 10:35:13 AM

What would you change in the table design?
I'm certainly open to reccommendations

5/13/2014 2:54:39 PM

I still don't understand why you don't put the server attributes in the servers table.

That would solve this reporting problem.

5/13/2014 2:57:55 PM

Okay, I need you to explain this:

Quote :

"I can't use a clearly set of attributes, and I need to be able to add attributes on the fly."

I assume you mean you can't use a clearly defined set of attributes, i.e. that the attributes are subject to change and that you have no control over them. Is that right?

The scenario I'm imagining is that you have some data source dumping name-value pairs of arbitrary attributes related to a server record, and you have identified certain attributes that you want to report on.

I don't know how frequently these attributes or your reporting requirements change, but I would do kinda like synapse said, only instead of storing the attributes on the same table as the server records, I'd probably create a new table keyed to the server table with a column for each attribute meaningful to me. Then, I'd schedule a job to populate that table from the attributes table you have no control over.

For problem one, you should create a temporary table or table variable to keep track of the counts of each individual value for each attribute, then select off that table ordering by the maximum count value per attribute.

5/13/2014 3:40:12 PM

^thats kind of my plan right now, is to have a Reporting Table for each object type, i was hoping to have that be a dynamically generated view, but I guess i'll need to use a table.

Its not just servers, its actually a lot of stuff.

Items could be servers, routers switches et cetra.

I'm basically using the 2 tables to store classes of objects.

Server table has an object type (I.E. Server, router, switch) and a name
Attribute table contains things that describe it (I.E. IP Address).

I'm pulling in data from about 10 different sources, which overlap objects I.E. 2 different services SCOM and Solarwinds might both have data about a device.

I also need compliance reporting, I.E. a Windows Server must exist in these 5 systems, but I also need functional data.

I do have a Class type structure built into the database. I.E.

Servers have IP, Serial, and OS
a Virtual Server has everthing a Server has and then adds Hypervisor

A week or so later I get a new report, which provides a new attribute say Hypervisor Host

so now a Virtual Server has IP, Serial, OS, Hypervisor and Hypervisor host

5/13/2014 4:30:11 PM