Optimizing Query with Join to with out join

[durgave]

I have the below query which seemingly working okay. I was looking things that I can use to optimize the below query with possibly less to no joins.

attrs.stack=produs line.et=model line.model="\"Unique Model2\"" OR line.model="\"Unique Model1\"" 
| dedup line.dev_id
| rename line.customer_id as CUSTOMER_ID 
| join CUSTOMER_ID
   [search STACK=produs et=CP earliest=-1d@d latest=@d 
    [search attrs.stack=produs line.et=model line.model="\"Unique Model2\"" OR line.model="\"Unique Model1\""  
    |  dedup line.customer_id | rename line.customer_id as CUSTOMER_ID 
    |  fields CUSTOMER_ID]|  table CUSTOMER_ID,SERVICE_PROVIDER_PATH
    ]
| table SERVICE_PROVIDER_PATH, CUSTOMER_ID, line.model, line.serial_num

et=CP sample log message 

2020-07-15 18:20:21.391, STACK="produs", EVENT_TYPE="et=CP", YEAR_MONTH_DAY="20200715", SERVICE_PROVIDER_PATH="xyz\partner", SERVICE_PROVIDER_NAME="partner1", PARENT_SERVICE_PROVIDER_ID="178", CUSTOMER_NAME="38091", SERVICE_PROVIDER_ID="245", CUSTOMER_ID="382091", CUSTOMER_GUID="9AB6B77D-3646-413F-8F8D-95F3833F1919", TENANT_ID="33146442", IS_ACTIVE_FL="Y", INSERT_DT="2020-07-13 20:50:43.0", UPDATE_DT="2020-07-13 21:29:49.0", PSE_ENABLED_FL="N", AMV_ENABLED_FL="N", AUTO_UPDATE_ENABLED_FL="Y", LICENSE_EXPIRATION_DT="{null}", AUTO_UPDATE_VERSION="{null}", IS_TEST_FL="{null}", INSTANT_ON_ENABLED_FL="Y", SDA_ENABLED_FL="N", DNS_PREFERRED_FL="N", REMOTE_EWS_ENABLED_FL="Y", CLOUD_PROXY_OFFLINE_SECONDS="259200", REMOTE_EWS_CSRF_ENABLED_FL="N", TOTAL_CUSTOMERS="15014596"

sample et=model

{"line":{"time":"2020-07-16T23:12:06.9099864Z","msg":"device info","currentLinkPlatformVersion":"{null}","deviceIntrinsicUuid":"31bc0037-d260-44c1-aeef-a7329c1553c3","isLinkForWebEnabled":"True","isLinkForWebSupported":"True","isLinkForDeviceEnabled":"{null}","isLinkForDeviceSupported":"False","printer_def_id":"\"Unique Model1\"","mac_address":"BxxxxxxxEF0","firmware_version":"00000000_0111","mcid":"QWDEFRV","ocv_status":"ok","mSKU":"Present","model_num":"2567DE","model_support":"DABC","serial_num":"ABCDE32345","host_name":"{null}","ipaddr":"1.1.1.1","conn_id":"1173","customer_name":"329","customer_id":"3129","model":"\"Unique Model1\"","dev_id":"1246614","cid":"8697DE5311D6","et":"B","logger":"DeviceResultLoggerContinuationWorker"},"source":"stdout","task":"wiblig","attrs":{"service":"service","stack":"produs"}}

Any help is greatly appreciated!!

[to4kawa]

sample:

index=_internal |head 1 | fields _time _raw 
| eval _raw="2020-07-15 18:20:21.391, STACK=\"produs\", EVENT_TYPE=\"et=CP\", YEAR_MONTH_DAY=\"20200715\", SERVICE_PROVIDER_PATH=\"xyz\partner\", SERVICE_PROVIDER_NAME=\"partner1\", PARENT_SERVICE_PROVIDER_ID=\"178\", CUSTOMER_NAME=\"38091\", SERVICE_PROVIDER_ID=\"245\", CUSTOMER_ID=\"382091\", CUSTOMER_GUID=\"9AB6B77D-3646-413F-8F8D-95F3833F1919\", TENANT_ID=\"33146442\", IS_ACTIVE_FL=\"Y\", INSERT_DT=\"2020-07-13 20:50:43.0\", UPDATE_DT=\"2020-07-13 21:29:49.0\", PSE_ENABLED_FL=\"N\", AMV_ENABLED_FL=\"N\", AUTO_UPDATE_ENABLED_FL=\"Y\", LICENSE_EXPIRATION_DT=\"{null}\", AUTO_UPDATE_VERSION=\"{null}\", IS_TEST_FL=\"{null}\", INSTANT_ON_ENABLED_FL=\"Y\", SDA_ENABLED_FL=\"N\", DNS_PREFERRED_FL=\"N\", REMOTE_EWS_ENABLED_FL=\"Y\", CLOUD_PROXY_OFFLINE_SECONDS=\"259200\", REMOTE_EWS_CSRF_ENABLED_FL=\"N\", TOTAL_CUSTOMERS=\"15014596\""
| appendpipe [eval _raw="{\"line\":{\"time\":\"2020-07-16T23:12:06.9099864Z\",\"msg\":\"device info\",\"currentLinkPlatformVersion\":\"{null}\",\"deviceIntrinsicUuid\":\"31bc0037-d260-44c1-aeef-a7329c1553c3\",\"isLinkForWebEnabled\":\"True\",\"isLinkForWebSupported\":\"True\",\"isLinkForDeviceEnabled\":\"{null}\",\"isLinkForDeviceSupported\":\"False\",\"printer_def_id\":\"\\\"Unique Model1\\\"\",\"mac_address\":\"BxxxxxxxEF0\",\"firmware_version\":\"00000000_0111\",\"mcid\":\"QWDEFRV\",\"ocv_status\":\"ok\",\"mSKU\":\"Present\",\"model_num\":\"2567DE\",\"model_support\":\"DABC\",\"serial_num\":\"ABCDE32345\",\"host_name\":\"{null}\",\"ipaddr\":\"1.1.1.1\",\"conn_id\":\"1173\",\"customer_name\":\"329\",\"customer_id\":\"3129\",\"model\":\"\\\"Unique Model1\\\"\",\"dev_id\":\"1246614\",\"cid\":\"8697DE5311D6\",\"et\":\"B\",\"logger\":\"DeviceResultLoggerContinuationWorker\"},\"source\":\"stdout\",\"task\":\"wiblig\",\"attrs\":{\"service\":\"service\",\"stack\":\"produs\"}}"
| appendpipe [eval _raw="{\"line\":{\"time\":\"2020-07-16T23:12:06.9099864Z\",\"msg\":\"device info\",\"currentLinkPlatformVersion\":\"{null}\",\"deviceIntrinsicUuid\":\"31bc0037-d260-44c1-aeef-a7329c1553c3\",\"isLinkForWebEnabled\":\"True\",\"isLinkForWebSupported\":\"True\",\"isLinkForDeviceEnabled\":\"{null}\",\"isLinkForDeviceSupported\":\"False\",\"printer_def_id\":\"\\\"Unique Model1\\\"\",\"mac_address\":\"BxxxxxxxEF0\",\"firmware_version\":\"00000000_0111\",\"mcid\":\"QWDEFRV\",\"ocv_status\":\"ok\",\"mSKU\":\"Present\",\"model_num\":\"2567DE\",\"model_support\":\"DABC\",\"serial_num\":\"ABCDE32345\",\"host_name\":\"{null}\",\"ipaddr\":\"1.1.1.1\",\"conn_id\":\"1173\",\"customer_name\":\"329\",\"customer_id\":\"382091\",\"model\":\"\\\"Unique Model1\\\"\",\"dev_id\":\"1246614\",\"cid\":\"8697DE5311D6\",\"et\":\"B\",\"logger\":\"DeviceResultLoggerContinuationWorker\"},\"source\":\"stdout\",\"task\":\"wiblig\",\"attrs\":{\"service\":\"service\",\"stack\":\"produs\"}}"
]]

| kv 
| eval CUSTOMER_ID=coalesce(CUSTOMER_ID,'line.customer_id')
| table SERVICE_PROVIDER_PATH, CUSTOMER_ID, line.model, line.serial_num
| stats values(*) as * by CUSTOMER_ID

recommend:

attrs.stack=produs line.et=model line.model="\"Unique Model2\"" OR line.model="\"Unique Model1\""  OR (STACK=produs et=CP earliest=-1d@d latest=@d )
| eval CUSTOMER_ID=coalesce(CUSTOMER_ID,'line.customer_id')
| table SERVICE_PROVIDER_PATH, CUSTOMER_ID, line.model, line.serial_num
| stats values(*) as * by CUSTOMER_ID

and use where or search

[niketn]

@durgave please provide volume details for each of your data sources.

Time is hard-coded in one of your Sub Searches but not all. Any specific reason?

Since you are not performing any aggregation, please restrict the number of fields to only required for each index before the dedup command using something like | fields yourField1 yourField2 | dedup yourField1

Refer to .Conf session around Master joining your dataset without using the join command.

[richgalloway]

What optimizations have you tried so far?

One of the best optimizations one can make to any search is to include index=, source=, and sourcetype= (as many as are known) in it.