How to filter results based on timeframe?

[alexspunkshell]

In my search results, I am getting IP and user details.

I want to filter my search results if the same IP has been used by any user "*@xyz.com" in last 30 days.

[gcusello]

Hi @alexspunkshell,

could you share your search?

anyway, you could run something like this:

<your_search>
| eval period=if(_time>now()-86400,"Last 24 hours","Previous")
| stats 
   dc(period) AS period_count 
   values(period) AS period
   latest(if(evalperiod="Last 24 hours",_time,"") AS latest_last_24_hours
   latest(if(evalperiod="Previous",_time,"") AS latest_previous
   BY IP user
| where period_count12 AND period="Previous"
| eval latest_previous=strftime(latest_last_24_hours,"%Y-%m-%d %H:%M:%S")
| table IP user latest_previous

Anyway, see my approach and adapt it to your requirements.

Ciao.

Giuseppe

[alexspunkshell]

@gcusello 

index=test
eventType IN (security.threat.detected, security.internal.threat.detected)
|rename client.userAgent.rawUserAgent as User_Agent client.geographicalContext.city as Src_City client.geographicalContext.state as src_state client.geographicalContext.country as src_country displayMessage as Threat_Description signature as Signature client.device as Client_Device client.userAgent.browser as Client_Browser
| stats count min(_time) as firstTime max(_time) as lastTime by src_ip user Signature Threat_Description Client_Device eventType Src_Details Src_City User_Agent Client_Browser

[gcusello]

Hi @alexspunkshell,

I don't understand the structure of your search,

anyway, the raw "| search NOT idp_user" requires something after the field, otherwise Splunk excludes only events with the string "idp_user".

like the following | search NOT idp_accountname IN (*idp*references*): you'll never have results and you cannot check it because you are working with the NOT operator.

You could modify the stats command using my approach and the following check:

index=test
| rex field=_raw "user (?<idp_user>\d+\S+)"
| search NOT idp_user=* NOT actor.alternateId="*idp*" eventType IN (security.threat.detected, security.internal.threat.detected)
| rex field=debugContext.debugData.url "\S+username\=(?<idp_accountname>\S+idp-references)"
| regex src_ip!="47.37.\d{1,3}.\d{1,3}" 
| search NOT idp_accountname IN ("*idp*references*")
|rename  client.userAgent.rawUserAgent as User_Agent client.geographicalContext.city as Src_City client.geographicalContext.state as src_state client.geographicalContext.country as src_country displayMessage as Threat_Description signature as Signature client.device as Client_Device client.userAgent.browser as Client_Browser
| strcat "Outcome Reason: " outcome.reason ", Outcome Result: " outcome.result Outcome_Details 
| strcat "Source Country: " src_country ", Source State: " src_state  Src_Details
| eval period=if(_time>now()-86400,"Last 24 hours","Previous")
| eventstats 
   dc(period) AS period_count 
   BY IP user
| stats 
   count 
   values(period_count) AS period_count 
   min(_time) as firstTime 
   max(_time) as lastTime 
   by src_ip user Signature Threat_Description Client_Device eventType Src_Details Src_City  Outcome_Details User_Agent Client_Browser outcome.reason
| where period_count=1
| `security_content_ctime(firstTime)`
| `security_content_ctime(lastTime)`
| `okta_threatinsight_suspected_passwordspray_attack_filter` 
| `okta_threatinsight_threat_detected_filter`

As I said, try to adapt the approach of my search to your.

Ciao.

Giuseppe

[alexspunkshell]

@gcusello Thanks for your help.

I tried all the changes in the SPL too. However, period_count is showing 1.

Hence i am unable to filter in results.

[gcusello]

Hi @alexspunkshell,

if period_count=1 means that the event is present only before last 24 hours or inside last 24 hours, but not in both the periods.

Ciao.

Giuseppe

[alexspunkshell]

@gcusello 

For all the results i am getting period_count=1. 

Whereas only a few IP are used my user="*@xyz.com*" in the last 30 days.

I want to particularly filter if, the IPs were used by user="*@xyz.com*".

[alexspunkshell]

@gcusello  SPL Used

index=test
|rename  client.userAgent.rawUserAgent as User_Agent client.geographicalContext.city as Src_City client.geographicalContext.state as src_state client.geographicalContext.country as src_country displayMessage as Threat_Description signature as Signature client.device as Client_Device client.userAgent.browser as Client_Browser
| strcat "Outcome Reason: " outcome.reason ", Outcome Result: " outcome.result Outcome_Details  
| strcat "Source Country: " src_country ", Source State: " src_state  Src_Details | eval period=if(_time>now()-86400,"Last 24 hours","Previous")
| eventstats 
   dc(period) AS period_count 
   BY src_ip user
| stats 
   count 
   values(period_count) AS period_count 
   min(_time) as firstTime 
   max(_time) as lastTime 
   by src_ip user Signature Threat_Description Client_Device eventType Src_Details Src_City  Outcome_Details User_Agent Client_Browser outcome.reason

[gcusello]

Hi @alexspunkshell,

please try this:

index=test
|rename  client.userAgent.rawUserAgent as User_Agent client.geographicalContext.city as Src_City client.geographicalContext.state as src_state client.geographicalContext.country as src_country displayMessage as Threat_Description signature as Signature client.device as Client_Device client.userAgent.browser as Client_Browser
| strcat "Outcome Reason: " outcome.reason ", Outcome Result: " outcome.result Outcome_Details  
| strcat "Source Country: " src_country ", Source State: " src_state  Src_Details | eval period=if(_time>now()-86400,"Last 24 hours","Previous")
| eval period=if(_time>now()-86400,"Last 24 hours","Previous")
| stats 
   count 
   dc(period) AS period_count 
   min(_time) as firstTime 
   max(_time) as lastTime 
   values(Signature) AS Signature
   values(Threat_Description) AS Threat_Description 
   values(Client_Device) AS Client_Device
   values(eventType) AS eventType
   values(Src_Details) AS Src_Details
   values(Src_City) AS Src_City
   values(Outcome_Details) AS Outcome_Details
   values(User_Agent) AS User_Agent
   values(Client_Browser) AS Client_Browser
   values(outcome.reason) AS outcome_reason
   by src_ip user 
| where period_count=1

You can debug your search deleting the last row.

Ciao.

Giuseppe