Splunk Search

How to eliminate join for better query perfomance

vikashperiwal
Path Finder

I have two query with same source , index with differnt time (once current day, other one pevious week day), i want to substract the current day minus previous day. below is the query i could write , i want to eliminate the join for better performance.

index=eniq source=dbmon-tail://Eniq/DC_E_IMSSBG_PROXYREGISTRAR* earliest=-4h
| fields _time, source, NE_ID, SignalingNetworkConnection_netId, SBGSIPREGSTATREGUSERGAUGE
| fields - _raw
| eval NE_netId=NE_ID.":".SignalingNetworkConnection_netId
| search NE_netId="ONHOOD-SBG-01:5" OR NE_netId="PQPTFD-SBG-01:5" OR NE_netId="ABBRLW-SBG-01:4" OR NE_netId="BCGLMR-SBG-01:4"
| stats values(eval(if(like(source,"%DC_E_IMSSBG_PROXYREGISTRAR_RAW%"),SBGSIPREGSTATREGUSERGAUGE,""))) AS sbgSipRegStatRegUserGauge
values(eval(if(like(source,"%DC_E_IMSSBG_PROXYREGISTRARV6_RAW%"),SBGSIPREGSTATREGUSERGAUGE,""))) AS sbgSipRegStatRegUserGaugeV6 values(SignalingNetworkConnection_netId) AS SignalingNetworkConnection_netId by NE_netId,_time, NE_ID
| eval sbgSipRegStatRegUserGauge1 = (sbgSipRegStatRegUserGaugeV6)|stats sum(sbgSipRegStatRegUserGauge1) as sbgSipRegStatRegUserGauge1 by NE_netId _time

| join [search

index=eniq source=dbmon-tail://Eniq/DC_E_IMSSBG_PROXYREGISTRAR* earliest= -7d-4h@m
| fields _time, source, NE_ID, SignalingNetworkConnection_netId, SBGSIPREGSTATREGUSERGAUGE, x
| fields - _raw
| eval NE_netId=NE_ID.":".SignalingNetworkConnection_netId
| search NE_netId="ONHOOD-SBG-01:5" OR NE_netId="PQPTFD-SBG-01:5" OR NE_netId="ABBRLW-SBG-01:4" OR NE_netId="BCGLMR-SBG-01:4"
| stats values(eval(if(like(source,"%DC_E_IMSSBG_PROXYREGISTRAR_RAW%"),SBGSIPREGSTATREGUSERGAUGE,""))) AS sbgSipRegStatRegUserGauge
values(eval(if(like(source,"%DC_E_IMSSBG_PROXYREGISTRARV6_RAW%"),SBGSIPREGSTATREGUSERGAUGE,""))) AS sbgSipRegStatRegUserGaugeV6 values(SignalingNetworkConnection_netId) AS SignalingNetworkConnection_netId by NE_netId,_time, NE_ID
| eval sbgSipRegStatRegUserGauge2 = (sbgSipRegStatRegUserGaugeV6)|stats sum(sbgSipRegStatRegUserGauge2) as sbgSipRegStatRegUserGauge2 by NE_netId _time]
| eval sbgSipRegStatRegUserGauge = (sbgSipRegStatRegUserGauge1 - sbgSipRegStatRegUserGauge2)
| timechart span=15m sum(sbgSipRegStatRegUserGauge) AS sbgSipRegStatRegUserGauge by NE_netId

0 Karma

dmarling
Builder

I'm not sure how your join is functioning at all at the moment since you don't have a field specified in it and the _time field on your subsearch in the join will be last week's timestamp so it won't line up with the _time field on your main search. I believe the overall concept you have is sound though. If you use union instead of join it should provide a bit better performance and you won't run into the problem of the subsearch being stopped after 30 seconds. I did tweak your second search a bit so it makes the _time field of last weeks data line up with the current time field so the subsequent stats that joins the two data sets together will function.

| union maxtime=300 timeout=300 
    [ search index=eniq source=dbmon-tail://Eniq/DC_E_IMSSBG_PROXYREGISTRAR* earliest=-4h 
    | fields _time, source, NE_ID, SignalingNetworkConnection_netId, SBGSIPREGSTATREGUSERGAUGE 
    | fields - _raw 
    | eval NE_netId=NE_ID.":".SignalingNetworkConnection_netId 
    | search NE_netId="ONHOOD-SBG-01:5" OR NE_netId="PQPTFD-SBG-01:5" OR NE_netId="ABBRLW-SBG-01:4" OR NE_netId="BCGLMR-SBG-01:4" 
    | stats values(eval(if(like(source,"%DC_E_IMSSBG_PROXYREGISTRAR_RAW%"),SBGSIPREGSTATREGUSERGAUGE,""))) AS sbgSipRegStatRegUserGauge
        values(eval(if(like(source,"%DC_E_IMSSBG_PROXYREGISTRARV6_RAW%"),SBGSIPREGSTATREGUSERGAUGE,""))) AS sbgSipRegStatRegUserGaugeV6 values(SignalingNetworkConnection_netId) AS SignalingNetworkConnection_netId by NE_netId,_time, NE_ID 
    | eval sbgSipRegStatRegUserGauge1 = (sbgSipRegStatRegUserGaugeV6) 
    | stats sum(sbgSipRegStatRegUserGauge1) as sbgSipRegStatRegUserGauge1 by NE_netId _time] 
    [ search index=eniq source=dbmon-tail://Eniq/DC_E_IMSSBG_PROXYREGISTRAR* earliest= -7d-4h@m 
    | fields _time, source, NE_ID, SignalingNetworkConnection_netId, SBGSIPREGSTATREGUSERGAUGE, x 
    | fields - _raw 
    | eval NE_netId=NE_ID.":".SignalingNetworkConnection_netId 
    | search NE_netId="ONHOOD-SBG-01:5" OR NE_netId="PQPTFD-SBG-01:5" OR NE_netId="ABBRLW-SBG-01:4" OR NE_netId="BCGLMR-SBG-01:4" 
    | eval _time=strptime(strftime(now(), "%x")." ".strftime(_time, "%H:%M:%S.%3N"), "%x %H:%M:%S.%3N")
    | stats values(eval(if(like(source,"%DC_E_IMSSBG_PROXYREGISTRAR_RAW%"),SBGSIPREGSTATREGUSERGAUGE,""))) AS sbgSipRegStatRegUserGauge
        values(eval(if(like(source,"%DC_E_IMSSBG_PROXYREGISTRARV6_RAW%"),SBGSIPREGSTATREGUSERGAUGE,""))) AS sbgSipRegStatRegUserGaugeV6 values(SignalingNetworkConnection_netId) AS SignalingNetworkConnection_netId by NE_netId,_time, NE_ID 
    | eval sbgSipRegStatRegUserGauge2 = (sbgSipRegStatRegUserGaugeV6) 
    | stats sum(sbgSipRegStatRegUserGauge2) as sbgSipRegStatRegUserGauge2 by NE_netId _time]
| stats values(sbgSipRegStatRegUserGauge1) as sbgSipRegStatRegUserGauge1 values(sbgSipRegStatRegUserGauge2) as sbgSipRegStatRegUserGauge2 by NE_netId _time
| eval sbgSipRegStatRegUserGauge = (sbgSipRegStatRegUserGauge1 - sbgSipRegStatRegUserGauge2) 
| timechart span=15m sum(sbgSipRegStatRegUserGauge) AS sbgSipRegStatRegUserGauge by NE_netId
If this comment/answer was helpful, please up vote it. Thank you.
0 Karma

vikashperiwal
Path Finder

Thank You for the reposne..

I could fix this .... thanks for the comments you added that helped me understanding the issue and i have extracted "Time" from _time and then i performed the search i.e join on the "Time" and it worked.

If you see my search query above , i did join on "Time".

0 Karma

dmarling
Builder

Glad I could help. If you could accept the answer I would appreciate it.

If this comment/answer was helpful, please up vote it. Thank you.
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...