How to eliminate join for better query perfomance

[vikashperiwal]

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

[dmarling]

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

[vikashperiwal]

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

[dmarling]

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