index="***" sourcetype="xaxd:*****" "GrantContributorAccess" "Assigned Contributor role to user"
| rex field=Message "\[****=(?<accessId>.*?)\] - Assigned Contributor role to user (?<customerEmail>.*?) for customerId=(?<customerId>.*?) in directoryName=(?<azureDirectory>.*?) in subscriptionId=(?<subscriptionId>.*?)$"
| stats max(_time) as LATEST_ASSIGN by customerEmail | eval LATEST_ASSIGN=strftime(LATEST_ASSIGN,"%Y-%m-%d %H:%M:%S")
| map maxsearches=1000 search="search index="***" sourcetype="xaxd:*****" "RevokeContributorAccess" "Deleting user $customerEmail$" earliest=$LATEST_ASSIGN$"
| rex field=Message "\[RevokeContributorAccess=(?<accessId>.*?)\] - Deleting user (?<customerEmail>.*?) from AzureAD$"
| stats max(_time) as LATEST_REVOKE by customerEmail | eval LATEST_REVOKE=strftime(LATEST_REVOKE,"%Y-%m-%d %H:%M:%S")
I want to use the field "LATEST_ASSIGN" in the mapping subqueries as the "earliest" time for them.
Please help. Thanks in advance.
Prem
index="***" sourcetype="xaxd:*****" "GrantContributorAccess" "Assigned Contributor role to user"
| rex field=Message "\[****=(?<accessId>.*?)\] - Assigned Contributor role to user (?<customerEmail>.*?) for customerId=(?<customerId>.*?) in directoryName=(?<azureDirectory>.*?) in subscriptionId=(?<subscriptionId>.*?)$"
| map maxsearches=1000 search="search index="***" sourcetype="xaxd:*****" "RevokeContributorAccess" "Deleting user $customerEmail$" earliest=max(_time)"
| rex field=Message "\[RevokeContributorAccess=(?<accessId>.*?)\] - Deleting user (?<customerEmail>.*?) from AzureAD$"
| stats max(_time) as LATEST_REVOKE by customerEmail | eval LATEST_REVOKE=strftime(LATEST_REVOKE,"%Y-%m-%d %H:%M:%S")
Used this but the sub query is not exactly working according to given timeline. I am expecting results after the earliest time.
I didn't mean use the string "max(_time)" but instead use LATEST_ASSIGN as you are doing, just do not format it as a string, which will not be supported in that format
| stats max(_time) as LATEST_ASSIGN by customerEmail
| map maxsearches=1000 search="search index="***" sourcetype="xaxd:*****" "RevokeContributorAccess" "Deleting user $customerEmail$" earliest=$LATEST_ASSIGN$"
Your LATEST_ASSIGN value will be an epoch value and that is good for earliest=...
Tried this as well, it doesn't appear to be picking the earliest time, i actually tried normal notations like "-5m" as the value. It's not picking it.
Do you know that the map search you are giving actually finds anything?
search index="***" sourcetype="xaxd:*****" "RevokeContributorAccess" "Deleting user A_KNOWN_CUSTOMER_EMAIL" earliest=-5m
I have run a similar test and it passes the earliest time in the search.
Rather than formatting LATEST_ASSIGN, just leave it as the max(_time) value and that should work - you can always format it for display after the map command