topic Re: How to use fields from Main query in a map subquery? in Getting Data In

How to use fields from Main query in a map subquery?

premkumarbilla — Wed, 23 Mar 2022 05:08:16 GMT

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

Re: How to use fields from Main query in a map subquery?

bowesmana — Wed, 23 Mar 2022 05:32:16 GMT

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

Re: How to use fields from Main query in a map subquery?

premkumarbilla — Wed, 23 Mar 2022 05:39:12 GMT

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.

Re: How to use fields from Main query in a map subquery?

bowesmana — Wed, 23 Mar 2022 06:59:58 GMT

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=...

Re: How to use fields from Main query in a map subquery?

premkumarbilla — Wed, 23 Mar 2022 07:05:47 GMT

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.

Re: How to use fields from Main query in a map subquery?

bowesmana — Thu, 24 Mar 2022 03:36:25 GMT

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.