topic Re: Filter results based on timeframe in Splunk Search

How to filter results based on timeframe?

alexspunkshell — Mon, 25 Sep 2023 16:51:17 GMT

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.

Re: Filter results based on timeframe

gcusello — Mon, 25 Sep 2023 13:31:31 GMT

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

Re: Filter results based on timeframe

alexspunkshell — Mon, 25 Sep 2023 16:50:31 GMT

@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

Re: Filter results based on timeframe

gcusello — Mon, 25 Sep 2023 14:09:05 GMT

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

Re: Filter results based on timeframe

alexspunkshell — Mon, 25 Sep 2023 15:00:00 GMT

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

Re: Filter results based on timeframe

gcusello — Mon, 25 Sep 2023 15:02:50 GMT

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

Re: Filter results based on timeframe

alexspunkshell — Mon, 25 Sep 2023 18:21:59 GMT

@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*".

Re: Filter results based on timeframe

alexspunkshell — Mon, 25 Sep 2023 18:26:33 GMT

@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

Re: Filter results based on timeframe

gcusello — Tue, 26 Sep 2023 06:29:56 GMT

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