We left off in the last article Part 2 making a POST API call in PowerShell authenticate and authorize to generate a bearer token you can use further to call the IDCS AuditEvents API.
In this article you will make a call to Log Analytics to find the latest date time in the IDCSAudit_CL table to use later and pass it in your Oracle IDCS AuditEvents call.
When building a timmer trigger data connector for Azure Sentinel, one that runs every 5 minuets or so. You want to ensure you do not miss data due to the last data called and the firing of the LogicApp\Azure function. To do this most APIs from SaaS providers that you call have ways to deal with specific queries and the results as it relates to datetime windows. The Oracle IDCS AuditEvents call is not different and support an approach as follows:
&sortBy=timestamp&sortOrder=descending&filter=timestamp ge " " and timestamp le "2020-07-22T17:55:22Z"
Getting the end datetime is a simple call in PowerShell so that as the Azure Function triggers and runs you use:
$endtime = (Get-date -UFormat %Y-%m-%dT%R:%SZ)
The start time can actually be gathered by looking at the Azure Sentinel Log Analytics IDCSAudit_CL Table and using a KQL Query to search for the latest date time stamp. This will ensure that you pickup and query the Oracle IDCS API right where you left off ingesting the last time. Imaging if a transient error occurs while posting data or the timer trigger fails, by querying the LA Table for the latest date time stamp you ensure you pickup where you last left off preventing any missed data.
To collect the LA tables lastest entry you can use a KQL Query with Azure PowerShell.
union isfuzzy=true (IDCSAudit_CL | summarize arg_max(TimeGenerated , TimeGenerated ) |project TimeGenerated ) | summarize arg_max(TimeGenerated , TimeGenerated ) | project TimeGenerated
You will also define a few variables and use them to pass into our Azure PowerShell to execute the search query and also later to post data
# variables to Invoke and search for OCIAudit_CL latest table entry \ and HTTP DATA Collector API $workspaceID = "LA WORKSPACEID" $CustomerId = "LA WORKSPACEID" $workspaceKey = "LA WORKSPACEKEY" $SharedKey = "LA WORKSPACEKEY" $LogType = "IDCSAudit_CL" $timeStampField = "timestamp"
Now you will leverage Azure PowerShell to KQL query search Azure Sentinel Log Analytics workspace
# Invoke and search for OCIAudit_CL latest table entry if found pass datetime value, if not pass a time generated from 5 days ago. $starttime = (Invoke-AzOperationalInsightsQuery -WorkspaceId $workspaceID -Query "union isfuzzy=true (IDCSAudit_CL | summarize arg_max(TimeGenerated , TimeGenerated ) |project TimeGenerated ) | summarize arg_max(TimeGenerated , TimeGenerated ) | project TimeGenerated" -ErrorAction SilentlyContinue).Results.TimeGenerated
Notice the use of wrapping the cmdlet in ().Results.TimeGenerated you are dot sourcing the results back to a specific string value the actual date time stamp from the latest LA Table row and value from TimeGenerated. In the next article you will talk more about the TimeGenerated and how to line posting data to Log analytics so that TimeGenerated matches the time in the logs from SaaS provider.
Also notice if you first start this script and no data is present in the table because you have never posted you need to handle this possible condition that would happen first time you run. In the PowerShell above you do this by -ErrorAction SilentlyContinue. That way if the table did not exist the PowerShell would throw an error and the script would stop. the $starttime will now be null at this point.
To continue now you need to do a If statement to handle this peculiar condition of I want to collect and post data but on first run there is no data to collect a start datetime from and the variable is null. You can use PowerShell statement like this.
# Conditional check if IDCSAudit_CL table does not exist need to prime and pump, set starttime a day ago If (!$starttime) { #some additional if \ then to spot check if OCIAudit_CL table exists ? $starttime = (Get-date).AddDays(-1).ToString('yyyy-MM-ddThh:mm:ssZ') #.ffffZ }
Here you are checking based on the KQL Search results, if an error the $starttime is null and the PowerShell If statement will check if $starttime is null then Get the Dat Time a day ago from now and pass that formatted into a $starttime variable.
On subsequent runs you could remove this from the script or because the $starttime will now find a value in the LA Tables it will skip past the null check.
You now have the proper starttime and endtime values to pass to the Oracle IDCS AuditEvents API Endpoint.
In the next article you will dive into the IDCS AuditEvents API call, and how to deal with pagination as you POST the Audit Events back to Azure Sentinel.
As a reminder the complete script can be found here: https://github.com/swiftsolves-msft/PowerShell-Scripts/blob/master/Get-OIDCSAuditEvents.ps1
Sources: