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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...