Data Query API
Feature
Data Query API is available to help you fetch data from within Germain Datamart. The following gives an overview of the different use cases of the Germain data-services API. Each example lists the URL of the query, and a visual representation of the result returned by the service method. REST endpoint is available under http(s)://{GermainQueryServiceHostname}:{GermainQueryServicePort}/query/query where:
GermainQueryServiceHostname= Germain Query Service server deployment (e.g. localhost, IP address, server hostname)
GermainQueryServicePort= Germain Query Service port deployment (e.g. default value is 8080)
Please contact us if you are not sure about your query service hostname/port.
Usage
The following request JSON object body must be sent to the http(s)://{GermainQueryServiceHostname}:{GermainQueryServicePort}/query/query URL via a POST method:
{
"factType":FACT_TYPE_VALUE,
"measures":[MEASUREA, MEASUREB, ...],
"pivots":[PIVOTA, PIVOTB, ...],
"orders":[ORDERA, ORDERB, ...],
"filter":FILTER_VALUE,
"limit": LIMIT_VALUE,
"timeRange":[START_TIME, END_TIME],
"timeOffset": TIME_OFFSET
}factType - available values can be found in our Analysis Model Reference under "Facts" section (e.g. GenericMetric, GenericTransaction, SiebelWorkflowTransaction, ProcessMetric , ...)
Please check the KPI in-context configuration to find out which factType to use (e.g. for Siebel User Txns KPI you should use SiebelUserTransaction factType)
measures - available values can be found in our Analysis Model Reference under "Analysis Model Reference" section (e.g. system.name, time.month, category, avg(duration), count(id), ...)
Supported aggregation functions: count, sum, avg, min, max,
pivots - available values can be found in our Analysis Model Reference under "Analysis Model Reference" section (e.g. system.name, time.month, category, ...)
When specifying a pivot, make sure to include it in the list of measures as well.
orders - value from measure + order (e.g duration asc, startTime desc, ...)
Orders available: asc, desc
filter - see Analysis Model Reference under "Defining a Portlet Constraint" section (e.g. ((user.name == 'jsmith') || (user.name == 'bjones')) && (application.name == 'Siebel'))
limit - limit number of results (integer)
Default value = null (no limits)
timeRange - start and end timestamps to constraint time range (e.g. ["2020-05-01T00:00:00+02:00", "2020-07-31T00:00:00+02:00"])
timeOffset - timestamps offset (e.g. "+02:00")
Result of your data fetching query should look like this:
{
"descriptor": {
"resultCount": 2,
"error": null,
"factType": "UxSession"
"id": "6f0235e7-f1ac-485b-8396-91f5a8eab9bb",
"resolution": "60"
},
"rowIndex": 0,
"rowCount": 2,
"data": [{
"color": "ORANGE",
"count(id)": 1042,
"status": "200"
}, {
"color": "GREEN",
"count(id)": 1741,
"status": "200"
}]
}Supported Queries
Trend Query
Example - Average duration of Siebel User Transactions grouped by hour on 2020-07-28.
Query Request:
{
"factType": "SiebelUserTransaction",
"measures": ["time.year", "time.month", "time.day", "time.hour", "avg(duration)"],
"pivots": ["time.year", "time.month", "time.day", "time.hour"],
"orders": ["time.year asc", "time.month asc", "time.day asc", "time.hour asc"],
"filter": "type == 'Siebel:User'",
"timeRange": ["2020-07-28T00:00:00+00:00", "2020-07-29T00:00:00+00:00"],
"timeOffset": "+00:00",
"limit": null
}
Data results:
{
"descriptor": {
"resultCount": 20,
"error": null,
"id": "1c4ac7ec-cbbc-4106-b330-8ad1561d782f",
"resolution": "60"
},
"rowIndex": 0,
"rowCount": 20,
"data": [{
"avg(duration)": 1.1043123419,
"time": {
"month": 7,
"hour": 0,
"year": 2018,
"day": 28
}
}, {
"avg(duration)": 1.4718860759,
"time": {
"month": 7,
"hour": 1,
"year": 2018,
"day": 28
}
}, {
"avg(duration)": 1.1045165365,
"time": {
"month": 7,
"hour": 2,
"year": 2018,
"day": 28
}
},
..........
]
}
Aggregate Query
Example - Count of all Siebel Errors on 2020-07-28.
Query Request:
{
"factType": "SiebelErrorEvent",
"measures": ["count(id)"],
"pivots": [],
"orders": [],
"filter": "type == 'Siebel:Error'",
"timeRange": ["2020-07-28T00:00:00+00:00", "2020-07-29T00:00:00+00:00"],
"timeOffset": "+00:00",
"limit": null
}Data results:
{
"descriptor": {
"resultCount": 1,
"error": null,
"id": "e0472f25-fca9-4d9d-90f2-b7fad520f618",
"resolution": "60"
},
"rowIndex": 0,
"rowCount": 1,
"data": [{
"count(id)": 2347472
}]
}Drill-Through Query
Example - List of 500 raw results for CPU Usage KPI on 2020-07-28 (return multiple measures/dimensions)
Query Request:
{
"factType": "GenericMetric",
"measures": ["color", "id", "system.name", "system.hostname", "system.type", "system.environment", "system.location.continent", "system.location.continentCode", "system.location.country", "system.location.countryCode", "system.location.region", "system.location.city", "database.name", "database.flavor", "application.name", "application.component", "sessionId", "pid", "tid", "timestamp", "value", "unit", "type", "name"],
"pivots": [],
"orders": ["timestamp desc"],
"filter": "type == 'System:CPU Usage'",
"timeRange": ["2020-07-28T00:00:00+00:00", "2020-07-29T00:00:00+00:00"],
"timeOffset": "+00:00",
"limit": "0,500"
}Data results:
"descriptor": {
"resultCount": 500,
"error": null,
"id": "c58acf4b-b56a-4bd6-a8ef-a6d3077ffb8f",
"resolution": null
},
"rowIndex": 0,
"rowCount": 100,
"data": [{
"color": "GREEN",
"pid": null,
"sessionId": null,
"type": "System:CPU Usage",
"tid": null,
"database": {
"flavor": null,
"name": null
},
"unit": null,
"system": {
"hostname": "app01p",
"environment": "appprd",
"name": "app01p",
"location": {
"continent": null,
"country": null,
"city": null,
"countryCode": null,
"region": null,
"continentCode": null
},
"type": "Siebel App"
},
"application": {
"component": null,
"name": "Siebel"
},
"name": "app01p",
"id": "5b5cd110eaae5077f162d9d8",
"value": 25.000000,
"timestamp": "2020-07-28T20:24:48"
}, ....
]
}Data Point Query
Example - Get one particular JS error of id == '5b5cd1ec222b85440c5277bc' (return multiple measures/dimensions)
Query Request:
{
"factType": "GenericUxEvent",
"measures": ["time.year", "time.minute", "system.location.continent", "application.component", "color", "system.environment", "userAgent.browser", "domId", "user.name", "user.department", "type", "userAgent.family", "user.location.continent", "time.hour", "user.location.countryCode", "userAgent.os", "domName", "system.type", "user.group", "details", "id", "time.month", "user.location.region", "timestamp", "system.name", "user.location.country", "time.day", "system.location.region", "user.location.city", "ip", "displayedName", "system.location.country", "sessionId", "userAgent.device", "url", "system.location.countryCode", "user.role", "sequence", "domLabel", "businessObject", "user.location.continentCode", "user.type", "name", "system.location.continentCode", "system.location.city", "system.hostname", "application.name"],
"pivots": [],
"orders": [],
"filter": "id == '5b5cd1ec222b85440c5277bc'",
"timeRange": [],
"limit": null
}Data results:
{
"descriptor": {
"resultCount": 1,
"error": null,
"id": "3f62e2e3-7f02-4c9c-99b4-8e8f17e6f5ff",
"resolution": null
},
"rowIndex": 0,
"rowCount": 1,
"data": [{
"color": "RED",
"ip": null,
"displayedName": null,
"domId": null,
"userAgent": {
"os": "Windows 7",
"browser": "Chrome 67.0.3396.99",
"family": "Chrome",
"device": "Desktop"
},
"sessionId": "MO9OlD-SmoBEdM8sZEBnBd1aqa49RHaDHaADejcxQqZKUdLyJZfdRObD5wZKol-eq8IUEHrXfhVNhoBo",
"type": "HTTP:JS",
"url": "/oui_enu/start.swe",
"sequence": null,
"system": {
"environment": "appprd",
"hostname": "localhost",
"name": "localhost",
"location": {
"continent": null,
"country": null,
"city": null,
"countryCode": null,
"region": null,
"continentCode": null
},
"type": "Siebel Web"
},
"domName": "TypeError: Cannot read property",
"application": {
"component": null,
"name": "Siebel"
},
"domLabel": null,
"businessObject": null,
"name": "Console Error",
"details": "TypeError: Cannot read property 'InvokeMethod' of null\n at RemoteRefresh (eval at <anonymous> (https://localhost/oui_enu/23030/scripts/3rdParty/jquery.js?_scb=:603:22), <anonymous>:30:26)\n at <anonymous>:1:1",
"time": {
"hour": 20,
"month": 7,
"year": 2018,
"day": 28,
"minute": 28
},
"id": "5b5cd1ec222b85440c5277bc",
"user": {
"role": null,
"name": "testuser",
"location": {
"continent": "North America",
"country": "United States",
"city": "San Rafael",
"countryCode": "US",
"region": "California",
"continentCode": "NA"
},
"department": null,
"type": null,
"group": null
},
"timestamp": "2020-07-28T20:28:26"
}]
}
Feature Availability: 2022.1 or later