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!

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

New Learning Videos on Topics Most Requested by You! Plus This Month’s New Splunk ...

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...