index="index1" sourcetype=show_command | join id [ search index="index2" sourcetype=software_data ] | sort _time | stats last(state) as state by name, device_id, interface | where state != "UP" | dedup name | stats count I want to optimize this query with without join using stats and OR, can anyone help me? Here index1 has fields like id,device_id and interface index2 has name,id Here index1 has suppose 15 ids, then index2 has 30 ids. Out of which 10 ids are common. I am feeling difficulty here because "by name, device_id, interface" in this name is of index2 field and rest are of index1. So, I am feeling difficult. ... View more

index="index1 sourcetype="sourcetype1" | join deviceId [ search index="index2" sourcetype="sourcetype2" productFamily="xyz" ] | where productFamily="xyz" | sort _time | stats last(p2mp) as p2mp by deviceName index1 has fields like deviceId,p2mp,deviceName index2 has fields like productFamily,deviceId I want to remove join, want to rebuild the optimized query which doesn't have job, append,sub search etc. Just OR, stats, eval .. I am trying with maintaining timestamp because I am using last keyword here and after doing OR and stats, all the data mixed up and I am not getting the result which I want. Can anyone help me?? ... View more