Transaction info Database

Hi ,

We have a BSM 9.26 IP2 installed. We have another instance in SaaS and HP use a FTP to drop a file with info that we use to fill another application and we’d like to know what is the query HP use in order to extract the info from the database and fill the *.csv file you drop in the FTP. In a previous version, before to have it in SaaS, we used :

SELECT EVENT_METER.EM_DATE_TIME ,TRANSACTIONS.TRANSACTION_NAME ,TRANSACTIONS.RANK_1_FROM ,TRANSACTIONS.RANK_1_TO ,EVENT_METER.EM_STATUS_ID ,EM_RESULT_VALUE.EVENT_METER ,EM_RESULT_VALUE.DBDATE FROM EVENT_METER ,TRANSACTIONS WHERE EVENT_METER_1.EM_TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID AND EVENT_METER_1.EM_LOCATION_ID = LOCATIONS.LOCATION_ID

But as I checked in the current database, this query is not valid anymore. Attached you can find the file with the info.

Anyone know where that info is stored now ? I need to update the query in order to be able to extract the info.

Thanks in advance

  • Hi Adr,

    the Query is for pre-BSM 9 times (BAC 8 and before) and yes, it will no longer work in BSM 9,
    as the profile database table structures have changed.

    if HP provided the query to you, I would expect that you also can get an updated version from them?

    You can check KCS document
      KM1165162 - BSM 9.x Database Schema changes
    to review some of the changes.

    In any case, here is a list of obvious changes:
    EVENT_METER is now BPM_TRANS, most of the field names didn't change, some have been added, some other might have been deleted
    [SAMPLETIME]
    ,[TUID]
    ,[DBDATE]
    ,[CUSTOMER_ID]
    ,[INTERNAL_TRANSACTION_ID]
    ,[INTERNAL_LOCATION_ID]
    ,[SCRIPT_ID]
    ,[BPM_AGENT_ID]
    ,[INTERNAL_SUBNET_ID]
    ,[SERVER_IP]
    ,[SERVER_NAME]
    ,[STATUS_ID]
    ,[EM_ID]
    ,[EM_RESULT_VALUE]
    ,[EM_PAGE_CBD_COUNT]
    ,[EM_DOWNLOAD_DATA_SIZE]
    ,[EM_W_CONNECTION_TIME]
    ,[EM_W_DNS_TIME]
    ,[EM_W_SSL_TIME]
    ,[EM_W_NETWORK_TIME]
    ,[EM_W_NETWORK_FB_TIME]
    ,[EM_W_SERVER_FB_TIME]
    ,[EM_W_DOWNLOAD_TIME]
    ,[EM_W_RETRY_TIME]
    ,[EM_S_CONNECTION_TIME]
    ,[EM_S_DNS_TIME]
    ,[EM_S_SSL_TIME]
    ,[EM_S_NETWORK_TIME]
    ,[EM_S_NETWORK_FB_TIME]
    ,[EM_S_SERVER_FB_TIME]
    ,[EM_S_DOWNLOAD_TIME]
    ,[EM_S_RETRY_TIME]
    ,[BASELINE_RESP_TIME_MEAN]
    ,[BASELINE_RESP_TIME_STD]
    ,[BASELINE_RESP_TIME_LOC_MEAN]
    ,[BASELINE_RESP_TIME_LOC_STD]
    ,[BASELINE_DOWNLOAD_TIME_MEAN]
    ,[BASELINE_DOWNLOAD_TIME_STD]
    ,[BASELINE_SERVER_TIME_MEAN]
    ,[BASELINE_SERVER_TIME_STD]
    ,[BASELINE_FIRSTBUF_TIME_MEAN]
    ,[BASELINE_FIRSTBUF_TIME_STD]
    ,[BASELINE_NETWORK_TIME_MEAN]
    ,[BASELINE_NETWORK_TIME_STD]
    ,[TOT_OK_HITS]
    ,[TOT_MINOR_HITS]
    ,[TOT_CRITICAL_HITS]
    ,[DOWNTIME_STATE]
    ,[ERROR_COUNT]
    ,[AVAILABILITY_STATUS]

    Hoever, with BSM 9 the concept and the way of storing TRANSACTION and LOCATION information has changed dramatically. The tables don't exist anymore, but have been replaced by multiple others, and the way accessing the data has changed dramatically, but lucky enoguh we do have the *_DIM tables.

    I checked the sample data you attached:
    you can get to the transaction name via
    <profile_db>.BPM_TRANS.INTERNAL_TRANSACTION_ID = <profile_db>.TRANSACTION_DIM.INTERNAL_TRANSACTION_ID
    field name holding the transaction name is TRANSACTION_DIM.TRANSACTION_NAME

    you can get to the location name via
    <profile_db>.BPM_TRANS.INTERNAL_LOCATION_ID = <profile_db>.LOCATIONS_DIM.INTERNAL_LOCATION_ID
    field name holding the location name LOCATIONS_DIM.LOCATION_NAME

    Overall I guess your query should work

    SELECT BPM_TRANS.SAMPLETIME ,TRANSACTIONS_DIM.TRANSACTION_NAME ,BPM_TRANS.STATUS_ID ,BPM_TRANS.EM_RESULT_VALUE ,BPM_TRANS.DBDATE
    FROM BPM_TRANS,TRANSACTIONS_DIM,LOCATIONS_DIM
    WHERE BPM_TRANS.INTERNAL_TRANSACTION_ID = TRANSACTIONS_DIM.INTERNAL_TRANSACTION_ID AND BPM_TRANS.INTERNAL_LOCATION_ID = LOCATIONS_DIM.INTERNAL_LOCATION_ID

    That's from one of my test systems (I only retrieved top 10 results)

    SAMPLETIME TRANSACTION_NAME STATUS_ID EM_RESULT_VALUE DBDATE

    1472116260.000000 Step 0 - Google 1 1418 2016-08-25 11:12:29.787
    1472116294.000000 Step 0 - Start JPetStore 0 2526 2016-08-25 11:12:29.787
    1472116268.000000 Green 0 5000 2016-08-25 11:12:29.787
    1472116278.000000 Yellow 0 10007 2016-08-25 11:12:29.787
    1472116296.000000 Step 1 - Login 0 1913 2016-08-25 11:12:29.787
    1472116296.000000 Step 2 - Order a bird 1 8 2016-08-25 11:12:29.787
    1472111267.000000 tx Rand Availability 0 2984 2016-08-25 09:48:39.097
    1472116317.000000 Step 0 - Start JPetStore_SOE 0 2554 2016-08-25 11:13:19.803
    1472116319.000000 Step 1 - Login_SOE 0 1944 2016-08-25 11:13:19.803
    1472116319.000000 Step 2 - Order a bird_SOE 1 5 2016-08-25 11:13:19.803

    BTW, I have no idea where to find
    TRANSACTIONS.RANK_1_FROM ,TRANSACTIONS.RANK_1_TO

    Hope it gives you a start anyway
    Siggi

  • another approach can be to use Custom Query Report > BPM transaction samples. then select which ever fields you need and filter the data as you want, then publish the report to CSV. you can use Publish Report option which will generate a URL/HTML for you, and the result will be in CSV format, so each time you will run the URL it will give you recent data.

  • Hi, 

    Thank you both. 

    I'm checking the option given by Asaf.

    Regarding to the "RANK_1_FROM, RANK_1_TO" fields are in a table in the management schema, but I'm not sure if we can create a relation between different tables from different schemas, I mean in the point of view of your data architecture,  would it be possible ?

     

    Regards

  • BTW, RANK_1_FROM, and RANK_1_TO are the values of the threshold established for every transaction. do you know if this values are in another table into the profile schema ?

  • Hi ADR,

    it seems that this informarion moved to the BSM Management database table EUMBPM_TRANS
    (so not the profile database!):
    ..
    RANK_1_FROM INT NOT NULL,
    RANK_1_TO INT NOT NULL,
    ..
    RANK_1_FROM_ROLL INT NULL,
    RANK_2_FROM_ROLL INT NULL,
    BASEL_RANK_UPPER_LIMIT NUMERIC(24,6) NULL,
    ..
    AVAIL_THRESHOLD INT DEFAULT 90000 NOT NULL,
    ..
    BASEL_RANK_LOWER_LIMIT NUMERIC(24,6) NULL);
    ..

    Greetings
    Siggi