Getting Data In

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

premkumarbilla
Loves-to-Learn Lots

 

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


Labels (1)
0 Karma

premkumarbilla
Loves-to-Learn Lots
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. 

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

premkumarbilla
Loves-to-Learn Lots

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.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma
Get Updates on the Splunk Community!

Security Professional: Sharpen Your Defenses with These .conf25 Sessions

Sooooooooooo, guess what. .conf25 is almost here, and if you're on the Security Learning Path, this is your ...

First Steps with Splunk SOAR

Our first step was to gather a list of the playbooks we wanted and to sort them by priority.  Once this list ...

How To Build a Self-Service Observability Practice with Splunk Observability Cloud

If you’ve read our previous post on self-service observability, you already know what it is and why it ...