OSQLSCRIPTS - Open SQL Scripts Extractor
Data Description
The OSQLSCRIPTS event is used in SAP to view the results of SQL scripts created to extract data and send it to Splunk. The OSQLSCRIPTS extractor is part of the PowerConnect application’s extensible framework and allows customers to extract datasets not currently available in the PowerConnect application to meet custom use cases.
Potential Use Cases
This event could be used in the following scenarios:
Extraction of business data from the SAP system from a custom Z table or standard table that isn’t extracted by the PowerConnect application by default
Aggregation of table records prior to the data being sent to Splunk
Metric Filters
Important: The metric filters need to be applied to extract data for the OSQLSCRIPTS event.
Execute the /n/bnwvs/main transaction in the client where the PowerConnect application is installed. Go to Administrator → Metric Filters → SQL Script Metrics → OpenSQL Queries.
![](../__attachments/3358097409/image-20230626-185745.png?inst-v=ac5eec74-b94c-4536-9f14-31cf6d61e6ba)
Click on the “Insert Row” button and add a filter name to the newly added row. Input an Interval if the desired collection interval differs from the Global Interval set up under Administrator → Setup Group Def in the PowerConnect main screen. Please note that the Interval under the OpenSQL Queries filter cannot be less than the Global Interval set up under Setup Group Def. Double-click on the newly created row.
![](../__attachments/3358097409/image-20230626-191045.png?inst-v=ac5eec74-b94c-4536-9f14-31cf6d61e6ba)
Paste in the desired SQL script into the blank space, and click on the Check Script button.
![](../__attachments/3358097409/image-20230626-191251.png?inst-v=ac5eec74-b94c-4536-9f14-31cf6d61e6ba)
Confirm the script was written successfully based on the message displayed. If an error is displayed, please edit the SQL query.
![](../__attachments/3358097409/image-20230626-191341.png?inst-v=ac5eec74-b94c-4536-9f14-31cf6d61e6ba)
Confirm, and click on the Preview query data button. Confirm if the results are returned as expected. In case there needs to be changes made, please edit the SQL script and return to this step after using the Check SQL Script button. Save.
![](../__attachments/3358097409/image-20230626-191523.png?inst-v=ac5eec74-b94c-4536-9f14-31cf6d61e6ba)
Return to the filter main screen and click on the “Active” checkbox. Save.
![](../__attachments/3358097409/image-20230626-191609.png?inst-v=ac5eec74-b94c-4536-9f14-31cf6d61e6ba)
Ensure the OSQLSCRIPTS extractor is enabled using the following KB article: KB 108 - Enable/Disable ABAP Extractor (powerconnect.io). Data will now be extracted and sent to Splunk.
Note: All the operators that can be used in Classic Open SQ can be used in this SQL script.
If the aim is to extract delta data, appropriate where conditions should be added in the query itself. For these purposes following placeholders are foreseen:
Placeholder | Description |
$now | extractor current execution time in system time zone |
$today | extractor current execution date in system time zone |
$yesterday | extractor day before execution date in system time zone |
$timestamp | extractor current timestamp in UTC |
$lastrunts | extractor last execution timestamp in UTC (this is obsolete but still supported, it is recommended to use $lastrun) |
$lastrun | extractor last execution timestamp in UTC |
$lastrundate | extractor last execution date (system time zone) |
$lastruntime | extractor last execution time (system time zone) |
$lastrun_st | extractor last execution timestamp in system time zone |
$timestamp_st | extractor current timestamp in system time zone |
In version 8.01, an enhanced functionality has been introduced to facilitate a more dynamic testing experience when using placeholders within conditions. This enhancement provides users with a popup screen where placeholder values can be adjusted, offering greater flexibility and ease of testing.
Example Scenario: Consider the scenario where the placeholder $yesterday
is utilized in a script, and the calculated value for $yesterday
is 24.03.2024. In situations where there is no data available for this specific date, the enhanced functionality in version 8.01 allows for the modification of this placeholder value for testing purposes.
This means that users now have the option to change the value assigned to $yesterday
directly from the popup screen. By doing so, testing scenarios can be more accurately simulated, ensuring the script behaves as expected under various conditions.
This enhancement streamlines the testing process, enabling users to validate script behavior with different placeholder values without the need for manual adjustments within the script code.
![image-20240325-024045.png](../__attachments/3358097409/image-20240325-024045.png?inst-v=ac5eec74-b94c-4536-9f14-31cf6d61e6ba)
Splunk Event
Important: The fields will vary significantly based on the SQL script selection criteria specified in the Metric Filter
The event will look like this in Splunk:
![](../__attachments/3358097409/image-20230626-190740.png?inst-v=ac5eec74-b94c-4536-9f14-31cf6d61e6ba)
SAP Navigation
The OSQLSCRIPTS extractor is the equivalent of using an SQL statement to read data from the database. Therefore, the equivalent navigation in the SAP system is variable.