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!

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

Observability Newsletter Highlights | March 2023

 March 2023 | Check out the latest and greatestSplunk APM's New Tag Filter ExperienceSplunk APM has updated ...

Security Newsletter Updates | March 2023

 March 2023 | Check out the latest and greatestUnify Your Security Operations with Splunk Mission Control The ...