Compass queries
Compass queries can be used to extract OneView data from Data Fabric. Refer to Compass for more information. You can start Compass in the Data Fabric application in these ways:
- Click the Query the Data Lake link on the Home page.
- Copy the query from below and paste it in the Query field.
- Click .
This table shows the list of queries equivalent to OneView Search:
| Filter | Selections | Query |
|---|---|---|
| Date and Time Range | Example: | Select * from "ION_OneViewEvent" WHERE "ION_OneViewEvent"."event_date">='2020-10-02T20:38:08.981Z' |
| Last 1 hour data | Select * from "ION_OneViewEvent" WHERE event_date >= DATEADD(hour, -1, GETDATE()) | |
| Date and time range | Select * from "ION_OneViewEvent" WHERE "ION_OneViewEvent"."event_date">='2020-10-02T20:38:08.981Z' and "ION_OneViewEvent"."event_date"<='2023-10-03T20:38:08.981Z' | |
| Last 24 hours | SELECT * FROM ION_OneViewEvent WHERE event_date >= DATEADD(DAY, -1, GETDATE()) | |
| Last 7 days | SELECT * FROM ION_OneViewEvent WHERE event_date >= DATEADD(DAY, -7, GETDATE()) | |
| Message ID | Message ID equals lid://infor.jdbc.jdbcapp:133530684644091771:164776 | SELECT * FROM "ION_OneViewEvent" where "ION_OneViewEvent"."message_id"= '62:-ION-:8488e318d8dd4cf98896be8b7211d80a' |
| Message ID starts with ‘62:-ION-:8488e318d8dd4cf98896be8b7211d80a’ | SELECT * FROM "ION_OneViewEvent" where "ION_OneViewEvent"."message_id" like '62:-ION-:8488e318d8dd4cf98896be8b7211d80a%' | |
| Message ID contains ‘62:-ION-:8488e318d8dd4cf98896be8b7211d80a’ | SELECT * FROM "ION_OneViewEvent" where "ION_OneViewEvent". "message_id" like '%62:-ION-:8488e318d8dd4cf98896be8b7211d80a%' | |
| Document Name | Single document name search Document type - Sync.ItemMaster | SELECT * from "ION_OneViewEvent" WHERE document_type ='Sync.ItemMaster' |
| Multiple document name search Document type - Sync.ItemMaster or Sync.SalesOrder | SELECT * from "ION_OneViewEvent" WHERE document_type = 'Sync.ItemMaster' OR document_type = 'Sync.SalesOrder' | |
| Logical ID | SELECT * FROM "ION_OneViewEvent" WHERE "ION_OneViewEvent"."source_name" = 'infor.ims_ec.ub_imsviaec_1w' | |
| Message Resubmitted (Date and time) | Example | SELECT * FROM "ION_OneViewEvent" WHERE "ION_OneViewEvent"."event_type" = 'MESSAGE_RESUBMITTED' and event_date <= '2024-05-08T14:48:17.480' |
| Last 1 hour data | SELECT * FROM "ION_OneViewEvent" WHERE "ION_OneViewEvent"."event_type" = 'MESSAGE_RESUBMITTED' and event_date >= DATEADD(hour, -1, GETDATE()) | |
| Date and time range | SELECT * FROM "ION_OneViewEvent" WHERE "ION_OneViewEvent"."event_type" = 'MESSAGE_RESUBMITTED' and "ION_OneViewEvent"."event_date">='2020-10-02T20:38:08.981Z' and "ION_OneViewEvent"."event_date"<='2023-10-03T20:38:08.981Z' | |
| Last 24 hours | SELECT * FROM "ION_OneViewEvent" WHERE "ION_OneViewEvent"."event_type" = 'MESSAGE_RESUBMITTED' and event_date >= DATEADD(DAY, -1, GETDATE()) | |
| Last 7 days | SELECT * FROM "ION_OneViewEvent" WHERE "ION_OneViewEvent"."event_type" = 'MESSAGE_RESUBMITTED' and event_date >= DATEADD(DAY, -7, GETDATE()) | |
| Message Size | Message Size >=’10’ |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id"= "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'decompressed_size' AND "ION_OneViewEventProperties"."value">='10' |
| Message Size <=’6’ |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id"= "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'decompressed_size' AND "ION_OneViewEventProperties"."value" <='6' |
|
| Processing exceptions | Error | SELECT * from "ION_OneViewEvent" where "ION_OneViewEvent"."event_type" = 'CONFIRMBOD_GENERATED' |
| Filtered out | SELECT * from "ION_OneViewEvent" where "ION_OneViewEvent"."event_type" = 'BOD_FILTERED' | |
| No route | SELECT * from "ION_OneViewEvent" where "ION_OneViewEvent"."event_type" = 'NO_ROUTE_FOR_MESSAGE' | |
| Warning | SELECT * from "ION_OneViewEvent" where "ION_OneViewEvent"."event_type" = 'DELIVERY_FAILED_STARTING_RETRY' | |
| Size Over Limit (Blocked) | SELECT * from "ION_OneViewEvent" where "ION_OneViewEvent"."event_type" = 'DOCUMENT_BLOCKED_TOO_LARGE' | |
| Accounting Entity | Starts with ‘438’ | Select * from "ION_OneViewEvent" where "ION_OneViewEvent"."accounting_entity" LIKE '438%' |
| Equals to ‘AE_21’ | Select * from "ION_OneViewEvent" where "ION_OneViewEvent"."accounting_entity" = 'AE_21' | |
| Contains ‘438’ | Select * from "ION_OneViewEvent" where "ION_OneViewEvent"."accounting_entity" LIKE '%438%' | |
| Batch Id | Equals '95d7cbcdf827448fbb6e35b61344210a' |
Select "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" from "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'BatchId' AND "ION_OneViewEventProperties". "value"='95d7cbcdf827448fbb6e35b61344210a' |
| Starts with '95d7cbcdf827448fbb6’ |
Select "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" from "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'BatchId' AND "ION_OneViewEventProperties". "value" LIKE'95d7cbcdf827448fbb6%' |
|
| Contains ‘95d7cbcdf827448fb’ |
Select "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" from "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'BatchId' AND "ION_OneViewEventProperties". "value" LIKE'%95d7cbcdf827448fb%' |
|
| Document Id | Starts with ‘header’ |
Select ION_OneViewEvent.document_id, ION_OneViewEvent.message_id FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'DocumentId' AND "ION_OneViewEventProperties"."value" LIKE 'header%' |
| Equals ‘header’ |
Select ION_OneViewEvent.document_id, ION_OneViewEvent.message_id FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'DocumentId' AND "ION_OneViewEventProperties"."value" = 'header' |
|
| Contains ‘header’ |
Select ION_OneViewEvent.document_id, ION_OneViewEvent.message_id FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'DocumentId' AND "ION_OneViewEventProperties"."value" LIKE '%header%' |
|
| Location | Starts with ‘loc_header’ | Select * from "ION_OneViewEvent" where "ION_OneViewEvent"."location" LIKE 'loc_header%' |
| Equals ‘loc_header’ | Select * from "ION_OneViewEvent" where "ION_OneViewEvent"."location" = 'loc_header' | |
| Contains ‘loc_header’ | Select * from "ION_OneViewEvent" where "ION_OneViewEvent"."location" LIKE '%loc_header%' | |
| Source | Starts with 'infor.ims’ | SELECT * FROM "ION_OneViewEvent" WHERE "ION_OneViewEvent"."source_name" LIKE 'infor.ims%' |
| Equals 'infor.ims’ | SELECT * FROM "ION_OneViewEvent" WHERE "ION_OneViewEvent"."source_name" = 'infor.ims_ec.ub_imsviaec_1w' | |
| Contains 'infor.ims’ | SELECT * FROM "ION_OneViewEvent" WHERE "ION_OneViewEvent"."source_name" LIKE '%ims%' | |
| Retrieved from Datalake |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" WHERE "ION_OneViewEvent"."document_sent_from"= 'infor.datalake.datalake' AND "ION_OneViewEvent"."event_date" <= '2024-05-08T14:48:17.480' |
|
| Handled In Activation Policy | Handled In- Activation Policy Activation Policy = 'AP_Suite_4' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'activation_policy_name' AND "ION_OneViewEventProperties"."value" = 'AP_Suite_4' |
| Handled Not- Activation Policy Activation Policy = 'AP_Suite_4' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'activation_policy_name' AND "ION_OneViewEventProperties"."value" <> 'AP_Suite_4' |
|
| Handled In- Alarm Template | Handled In- Alarm template Alarm Template - AT |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'alarm_template_name' AND "ION_OneViewEventProperties"."value" = 'AT' |
| Handled Not- Alarm template Alarm Template - AT |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'alarm_template_name' AND "ION_OneViewEventProperties"."value" <> 'AT' |
|
| Handled In- Alarm | Handled In- Alarm Alarm - 'la_test1' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'alarm_owner' AND "ION_OneViewEventProperties"."value"= 'la_test1' |
| Handled Not- Alarm Alarm - 'la_test1' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'alarm_owner' AND "ION_OneViewEventProperties"."value" <> 'la_test1' |
|
| Handled In- Connection point Need to apply filter on both ION_OneViewEvent schema field source_name and ION_OneViewEventProperties schema key destination with OR operator |
Handled In -Connection point Connection Point- 'infor.ims.iamec_durationtests_imscp_xyz' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id"= "ION_OneViewEvent"."id" WHERE "ION_OneViewEvent"."source_name" = 'splitter1_65878_DF_DS_All_component' OR "ION_OneViewEventProperties"."key" = 'destination' AND "ION_OneViewEventProperties"."value" = 'infor.ims.iamec_durationtests_imscp_xyz' |
|
Handled Not- Connection point Connection Point- 'infor.ims.iamec_durationtests_imscp_xyz' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id"= "ION_OneViewEvent"."id" WHERE "ION_OneViewEvent"."source_name" <> 'splitter1_65878_DF_DS_All_component' OR "ION_OneViewEventProperties"."key" = 'destination' AND "ION_OneViewEventProperties"."value" <> 'infor.ims.iamec_durationtests_imscp_xyz' |
|
| Handled In- Document Split | Handled In – Document Split Splitter- 'Any_Size' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'splitter_name' AND "ION_OneViewEventProperties".value = 'Any_Size' |
| Handled Not- Document Split Splitter-'Any_Size' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'splitter_name' AND "ION_OneViewEventProperties".value <> 'Any_Size' |
|
| Handled IN- Mapping | Handled IN- Mapper Mapper - 'DS_MAp' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'mapper_name' AND "ION_OneViewEventProperties"."value" = 'DS_MAp' |
| Handled Not- Mapping Mapper - 'DS_MAp' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'mapper_name' AND "ION_OneViewEventProperties"."value" <> 'DS_MAp' |
|
| Handled IN- Merge | Handled In- Merge Merge_name= Merge1 |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'merge_name' AND "ION_OneViewEventProperties"."value" = 'Merge1' |
| Handled Not- Merge Merge_name= Merge1 |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'merge_name' AND "ION_OneViewEventProperties"."value" <> 'Merge1' |
|
| Handled IN- Monitor | Handled In- Monitor Monitor - 'CI_MON' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'monitor_name' AND "ION_OneViewEventProperties"."value" = 'CI_MON' |
| Handled Not- Monitor Monitor - 'CI_MON' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'monitor_name' AND "ION_OneViewEventProperties"."value" <> 'CI_MON' |
|
| Handled IN- Script | Handled In- script Script - 'Mtst_AdvMerge_Simple' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'script_name' AND "ION_OneViewEventProperties"."value" = 'Mtst_AdvMerge_Simple' |
| Handled Not – Script Script - 'Mtst_AdvMerge_Simple' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'script_name' AND "ION_OneViewEventProperties"."value" <> 'DA_TEST_TSDEV' |
|
| Handled IN- Workflow | Handled In- Workflow Workflow - 'WF_OV' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'workflow_name' AND "ION_OneViewEventProperties"."value" = 'WF_OV' |
| Handled NOT- Workflow Workflow - 'WF_OV' |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" INNER JOIN "ION_OneViewEventProperties" ON "ION_OneViewEventProperties"."id" = "ION_OneViewEvent"."id" WHERE "ION_OneViewEventProperties"."key" = 'workflow_name' AND "ION_OneViewEventProperties"."value" <> 'WF_OV' |
|
| Sort by document ID |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" order by "document_id" |
|
|
Sort by Document Name |
SELECT "ION_OneViewEvent"."id", "ION_OneViewEvent"."message_id", "ION_OneViewEvent"."document_type", "ION_OneViewEvent"."document_sent_from", "ION_OneViewEvent"."document_id", "ION_OneViewEvent"."accounting_entity", "ION_OneViewEvent"."location", "ION_OneViewEvent"."source_name", "ION_OneViewEvent"."source_type", "ION_OneViewEvent"."event_date", "ION_OneViewEvent"."event_type", "ION_OneViewEvent"."enterprise_location_name" FROM "ION_OneViewEvent" order by "document_name" |
|
| Group by Document ID | Select "ION_OneViewEvent"."document_id", COUNT(*) as document_id_count from ION_OneViewEvent GROUP BY document_id ORDER BY document_id_count ASC | |
| Group by Sender |
SELECT document_type, document_sent_from, COUNT(*) as total_documents FROM ION_OneViewEvent GROUP BY document_type, document_sent_from |
|
| Group by Date |
SELECT document_type, event_date, COUNT(*) as total_count from ION_OneViewEvent GROUP BY event_date, document_type |