Deployment Architecture

How to optimize the current query?

debjit_k
Path Finder

Hi 

hope you are doing good.

im working on a use case which will trigger if any user is trying to connect from non business country. 

attaching the snap for the query.

my query 

want to optimize it more if one user is trying is log in from more than 2-3 country than it will trigger.

can you please help me with the query 

 

thanks 

debjit 

75F75914-F270-48E1-BB99-2FE20B70A9E9.jpeg

Labels (3)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @debjit_k,

the first possible optimization is to move the search "Keywords="*Audit Success*) in the main search and leave only the search for country at the end of the search.

Then, if possible, try a different string to search because to have asterisk at the beginning of a string isn't efficient.

Then what's the sense of use IP and other fields in a stats command and then dedup by IP, in this way you have a longer search and you loose (or not use) some information,

Then you could put iplocation command after the stats command.

so you could use a different stats:

index=sdp_siem_win (host=AZPLTADFS1 OR host=AZPLTADFS1) keywords="*Audit Success*)
| rex "first regex"
| rex "second regex"
| stats values(Username) AS Username values(Keyword) AS Keyword values(EventCode) AS EventCode count BY IP
| iplocation IP 
| search NOT [inputlookup SDP_Country.csv | dedup Country | fields Country]
| table IP Country Username keyword EventCode count

Next time, please, don't share your search using a screenshot but put it as text in a Code Sample window.

Ciao.

Giuseppe

 

debjit_k
Path Finder

Hi @gcusello,

 

thank you for the updated query but im looking for a query which will only trigger if a single is log from 2 different country 

 

example

ip                   user           country

10.0.0.0       debjit         india

10.0.0.0       debjit         japan 

 

can you please help me to fig out the above solution

 

thanks

debjit

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @debjit_k,

please try something like this:

index=sdp_siem_win (host=AZPLTADFS1 OR host=AZPLTADFS1) keywords="*Audit Success*)
| rex "first regex"
| rex "second regex"
| iplocation IP 
| search NOT [inputlookup SDP_Country.csv | dedup Country | fields Country]
| stats values(IP) AS IP dc(Country) AS Country_count values(Country) AS Country BY Username
| where Country_count>1

please, next time share your search as text!

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...