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
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.
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.
Paste in the desired SQL script into the blank space, and click on the Check Script button.
Confirm the script was written successfully based on the message displayed. If an error is displayed, please edit the SQL query.
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.
Return to the filter main screen and click on the “Active” checkbox. Save.
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:
extractor current execution time in system time zone
extractor current execution date in system time zone
extractor day before execution date in system time zone
extractor current timestamp in UTC
extractor last execution timestamp in UTC (this is obsolete but still supported, it is recommended to use $lastrun)
extractor last execution timestamp in UTC
extractor last execution date (system time zone)
extractor last execution time (system time zone)
extractor last execution timestamp in system time zone
extractor current timestamp in system time zone
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:
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.