Splunk Search

Optimizing Query with Join to with out join

durgave
Engager

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!!

Labels (4)
0 Karma

to4kawa
Ultra Champion

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

0 Karma

niketn
Legend

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...