How to optimize the current query?

[debjit_k]

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 

 

[gcusello]

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]

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

[gcusello]

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