Can you help me optimize this query without join using stats and OR?

[m4sucess]

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.

[darrenfuller]

This is not answerable without some more information about the data in these indexes 1 and 2. Can you post an event or two example of each (scrubbed is fine)

[m4sucess]

Hi, I have updated the query and added few information, let me know if you need more info.

Here index1 has suppose 15 ids, then index2 has 30 ids. Out of which 10 ids are common.

[m4sucess]

index="index1" sourcetype=show_command_output
| 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, device_id, interface

index1 has fields Id, device_id, interface
index2 has fields name, Id

Let me know if require more information

i am facing difficulty here "by name,device_id,interface" because name is field of index2 and device_id and interface are the fields of index1