https://community.splunk.com/t5/Splunk-Search/Can-you-help-me-optimize-this-query-without-join-using-stats-and/m-p/403779#M116835 <P>index="index1" sourcetype=show_command_output<BR /> | join Id [ search index="index2" sourcetype=software_data ]<BR /> | sort _time<BR /> | stats last(state) as state by name, device_id, interface<BR /> | where state != "UP"<BR /> | dedup name, device_id, interface</P> <P>index1 has fields Id, device_id, interface<BR /> index2 has fields name, Id </P> <P>Let me know if require more information </P> <P>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</P> Tue, 29 Sep 2020 21:28:56 GMT m4sucess 2020-09-29T21:28:56Z https://community.splunk.com/t5/Splunk-Search/Can-you-help-me-optimize-this-query-without-join-using-stats-and/m-p/403777#M116833 <P>index="index1" sourcetype=show_command<BR /> | join id [ search index="index2" sourcetype=software_data ]<BR /> | sort _time<BR /> | stats last(state) as state by name, device_id, interface<BR /> | where state != "UP"<BR /> | dedup name<BR /> | stats count</P> <P>I want to optimize this query with without join using stats and OR, can anyone help me?</P> <P>Here index1 has fields like id,device_id and interface<BR /> index2 has name,id</P> <P>Here index1 has suppose 15 ids, then index2 has 30 ids. Out of which 10 ids are common.</P> <P>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.</P> Tue, 29 Sep 2020 21:28:20 GMT https://community.splunk.com/t5/Splunk-Search/Can-you-help-me-optimize-this-query-without-join-using-stats-and/m-p/403777#M116833 m4sucess 2020-09-29T21:28:20Z https://community.splunk.com/t5/Splunk-Search/Can-you-help-me-optimize-this-query-without-join-using-stats-and/m-p/403778#M116834 <P>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)</P> Mon, 01 Oct 2018 17:21:27 GMT https://community.splunk.com/t5/Splunk-Search/Can-you-help-me-optimize-this-query-without-join-using-stats-and/m-p/403778#M116834 darrenfuller 2018-10-01T17:21:27Z https://community.splunk.com/t5/Splunk-Search/Can-you-help-me-optimize-this-query-without-join-using-stats-and/m-p/403779#M116835 <P>index="index1" sourcetype=show_command_output<BR /> | join Id [ search index="index2" sourcetype=software_data ]<BR /> | sort _time<BR /> | stats last(state) as state by name, device_id, interface<BR /> | where state != "UP"<BR /> | dedup name, device_id, interface</P> <P>index1 has fields Id, device_id, interface<BR /> index2 has fields name, Id </P> <P>Let me know if require more information </P> <P>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</P> Tue, 29 Sep 2020 21:28:56 GMT https://community.splunk.com/t5/Splunk-Search/Can-you-help-me-optimize-this-query-without-join-using-stats-and/m-p/403779#M116835 m4sucess 2020-09-29T21:28:56Z https://community.splunk.com/t5/Splunk-Search/Can-you-help-me-optimize-this-query-without-join-using-stats-and/m-p/403780#M116836 <P>Hi, I have updated the query and added few information, let me know if you need more info.</P> <P>Here index1 has suppose 15 ids, then index2 has 30 ids. Out of which 10 ids are common.</P> Tue, 02 Oct 2018 17:00:04 GMT https://community.splunk.com/t5/Splunk-Search/Can-you-help-me-optimize-this-query-without-join-using-stats-and/m-p/403780#M116836 m4sucess 2018-10-02T17:00:04Z