Search Optimization on my query

[bapun18]

Hi,
I have a query that is giving me results in around 60-70 seconds I wanted to reduce the time, seems NOT is taking more time and my search is parsing for around 35-40 seconds. Please help me to optimize the below query so that it will run within 30 seconds.

index=syslogs earliest=-5m
| fields _time host SYSLOG_mne SYSLOG_message
| rename host as device_ip
| dedup device_ip
| search NOT
[| inputlookup my_devices_lookup
| dedup device_ip
| table device_ip]
| fields _time device_ip SYSLOG_mne SYSLOG_message
| stats values(device_ip) by _time SYSLOG_mne SYSLOG_message

[scelikok · Accepted solution]

Hi @bapun18,

Please try this below;

 index=syslogs earliest=-5m
| lookup my_devices_lookup device_ip as host OUTPUTNEW device_ip as found
| where isnull(found)
| stats values(device_ip) by _time SYSLOG_mne SYSLOG_message