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
}
JS
  • factType - available values can be found in our Analysis Model Reference under "Facts" section (e.gGenericMetric, 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"
    }]
}
JS

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
}
JS

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
            }
        },
        ..........
    ]
}

JS

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
}
JS

Data results:

{
    "descriptor": {
        "resultCount": 1,
        "error": null,
        "id": "e0472f25-fca9-4d9d-90f2-b7fad520f618",
        "resolution": "60"
    },
    "rowIndex": 0,
    "rowCount": 1,
    "data": [{
        "count(id)": 2347472
    }]
}
JS

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"
}
JS

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"
    }, ....
  ]
}
JS

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
}
JS

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"
    }]
}
JS

Feature Availability: 2022.1 or later