Hello, I hope all is well...
I am trying to understand how to create mash-up type reports in APG that utilize data from multiple data sources "multiple databases". Specifically, how to create a report that utilizes data from both a EMC SMARTS and SQL Collector Data Feed. The data in this example will be focused on server information.
We have a two server setup with a frontend and backend running APG v6.2 u4 on a Widows environment. We have a data feed to APG from EMC SMARTS that sends performance / "time series" metrics on the servers we monitor in our environment. Then we have another external Microsoft SQL databases that contains other server attribute information that the EMC SMARTS feed does not have. We call this external database the CMDB and it contains a server attribute we need call "server owner".
Our goal is to understand how we can produce a report that pulls attributes from multiple data sources by utilizing a unique primary key. The primary key we will be using is the device name "server name".
We have the SQL collector configured on the frontend server and the EMC SMARTS collector is configured on the backend server. We would like to add certain attributes from the external CMDB database to the incoming data from the EMC SMARTS collector. This would allow us to create server reports by server owner.
This sounds like a Property-Tagging-Filter needs to be used on the EMC SMARTS data feed but that has to read from a .csv file. How do you tag incoming data with already collected data from the SQL Collector?
Thank you very much for any help that can be provided.
Depending upon how you have created your report, you may unintentionally be filtered to Data in only one DB. There is a concept called a Complex Expansion that will allow you to "join" data from another M&R DB on a specific property.
Page 5 of the Frontend-Admin.Guide
Page 74 of the Frontend-Users Guide introduces the Complex node expansion
page 169 of the Frontend-Users Guide explains the Filter syntax
Mixing in another DB :
The filters starting with ‘#’ help to narrow data at the data base layer.
#APG:ALL means everything from apg databases.
#RPE2:ALL means everything from generic events databases.
The names are taken from the mapping defined in /opt/APG/Web-Servers/Tomcat/Default/conf/Catalina/localhost/APG.xml
complex expansion - Join: This will allow you to GET properties from both DB's based on the same selected "property"
device - we are joining APG-DB.device to RPE2-DB.elementInstance (see property)
properties=elementInstance <- identifies the property to use for the join
level-up=4 <- turns off the previous 4 filters, such that this NODE is able to pull in the data from the NEWLY added DB. You may have to remove more or less depending on your report structure.
I hope I understood your question. I'll do my best to answer but please feel free to correct any assumptions I've made.
First, although there may be multiple databases, the front-end consolidates these and provides a federated view. For example, if you expand on "ServerOwer" and this property is contained in two separate databases, you'd never know it in the front-end.
Here's what I understand:
- FE and BE server do not share a common W4N database, each server has it's own.
- Smarts Collector on BE
- SQL Collector on FE
- You would like to tag the incoming Smarts feed to be tagged with "Server Owner"
- My assumption is you will tag "Server Owner" based on the key: "Server Name"
- Another assumption, the CMDB contains a table that has both "Server Name" and "Server Owner" as fields
- You would like to create reports based on "Server Name"
- You would like to create reports based on "Server Owner".
I would suggest that you use a property-tagging-filter, specifically I would use the SQL function of the property-tagging-filter to query the CMDB. This PTF would have to be configured in the collecting chain on the BE server, in the Collector-Manager that contains the Smarts Collector. It would make a query for the "ServerName" and "ServerOwner"
(ie select servername, serverowner from someTable where serverowner is not NULL).
This will create a CSV file containing the key "ServerName" and new property "ServerOwner". The CSV file will be used to enrich the Smarts Data with the correct "ServerOwner" for each metric that contains "ServerName"
Complex expansions are interesting but not likely required in this case. Usually these are used to Switch context. The best example I can give is:
- You're collecting metrics specific to CELLs
- You're collecting metrics specific to RNCs
- Attached to these CELL metrics are several properties. One of which is "RNC" (the name of the RNC the CELL is connected to)
- Attached to the RNC metrics are several properties. One of which is "device" (the name of the RNC)
- There are no common property names between the metrics and so you cannot create reports that mix metrics from Cells and RNCs. (Except if you use a type of complex expansion!)
Since the "RNC" property (attached to cell metrics) contains the same value as the "device" property (attached to RNC metrics) we can "Switch Contexts" from Cell to RNC based on the common value of the property. For example, you expand through the Cell properties to create reports for Cells and then create a complex expansion to switch contexts from Cell to RNC so you can then create reports based on RNCs.
Hello Aaron, thank you so much for the replies. We implemented the PTF on our old system and that worked. Now we are implementing a new system with many more solution packs. Meaning many more data sources and we will have to have this same multi tenant capability. I believe we are going to try to use the PTF again.