How/Why am I am getting more events and less statistics for a last 24 hours period as compared to a last 4 hours window

[wm]

Hi all,

index=sky sourcetype=sky_trade_wss_timestamp
| rex field=_raw "trade_id=\"(?<trade_id>\X+)\", event_id"
| rex field=_raw "mx_status=\"(?<status>\X+)\", operation"
| rex field=_raw "operation=\"(?<operation>\X+)\", action"
| rex field=_raw " action=\"(?<action>\X+)\", tradebooking_sgp"
| rex field=_raw " eventtime_sgp=\"(?<distributor_timestamp>\X+)\", sky_to_mq"
| rex field=_raw "distributor_latency=\"(?<distributor_latency>[^\"]+)\".*\bportfolio_name=\"(?<portfolio_name>[^\"]+)\".*\bportfolio_entity=\"(?<portfolio_entity>[^\"]+)\".*\btrade_type=\"(?<trade_type>[^\"]+)"

| join event_id
[ search index=sky sourcetype=Sky_WSS_EP_Logs "Successfully processed event"
| rex field=_raw "INFO: (?<ep_timestamp>\d{8} \d{2}:\d{2}:\d{2}.\d{3})"
| rex field=_raw "Successfully processed event: (?<event_id>\X+), action"
| eval ep_timestamp = strftime(strptime(ep_timestamp."+0800", "%Y%d%m %H:%M:%S.%N%z"), "%Y-%m-%d %H:%M:%S.%3N")]

| join type=left sky_id
[ search index=sky "New trades in amendment" "*pnl*"
| rex "Trade Events (?<trades>.*)"
| rex max_match=0 field=trades "(?<both_id>\d+:\d+)"
| mvexpand both_id
| rex field=both_id ":(?<sky_id>\d+)"
| rex max_match=1 field=_raw "(?<booking_pnl_timestamp>\d{4}+-\d{2}+-\d{2} \d{2}:\d{2}:\d{2},\d{3})"]

| rex field=tradebooking_sgp "(?<booking_timestamp>\d{4}/\d{2}/\d{2} \d{2}:\d{2}:\d{2})"
| rex field=distributor_timestamp "(?<distributor_timestamp>\d{4}/\d{2}/\d{2} \d{2}:\d{2}:\d{2}\.\d{3})"

| eval booking_pnl_timestamp = booking_pnl_timestamp."+0800"

| eval ep_latency = strptime(ep_timestamp, "%Y-%m-%d %H:%M:%S.%N%z") - strptime(booking_timestamp, "%Y/%m/%d %H:%M:%S.%N%z")
| eval pnl_latency = strptime(booking_pnl_timestamp, "%Y-%m-%d %H:%M:%S,%N%z") - strptime(booking_timestamp, "%Y/%m/%d %H:%M:%S.%N%z")

| search trade_id = "*"
| search sky_id = "*"
| search event_id = "*"
| search action = "*"
| search mx_status = "live"
| search operation = "*"

| table trade_id, sky_id, event_id, booking_timestamp, distributor_timestamp, ep_timestamp, mx_status, operation, action, distributor_latency, ep_latency, portfolio_name, portfolio_entity
| sort ep_timestamp
| join type=left sky_id
[ search index=sky sourcetype=sky_cashfx_catchup_logs "[WSS] - Done incremental update"
| rex field=_raw "Max Skylib TradeID: (?<sky_id>\d+)"
| rex field=_raw "^(?<catchup_updated_time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2},\d{3})"
| eval catchup_updated_time = strftime(strptime(catchup_updated_time, "%Y-%m-%d %H:%M:%S,%3N"), "%Y/%m/%d %H:%M:%S.%3N")
| table sky_id, catchup_updated_time, _raw,
]
| eval booking_timestamp_unix = strptime(booking_timestamp, "%Y/%m/%d %H:%M:%S")
| eval catchup_unix_time = strptime(catchup_updated_time, "%Y/%m/%d %H:%M:%S.%3N")
| eval wss_to_sky_latency = catchup_unix_time - booking_timestamp_unix
| eval distributor_timestamp_unix = strptime(distributor_timestamp, "%Y/%m/%d %H:%M:%S.%3N")
| eval distributor_latency = distributor_timestamp_unix - booking_timestamp_unix
| eval ep_timestamp_unix = strptime(ep_timestamp, "%Y/%m/%d %H:%M:%S.%3N")
| eval ep_latency = ep_timestamp_unix - distributor_timestamp_unix
| eval mq_to_sky_update_latency = catchup_unix_time - distributor_timestamp_unix
| eval ep_timestamp = strftime(strptime(ep_timestamp, "%Y-%m-%d %H:%M:%S.%3N"), "%Y/%m/%d %H:%M:%S.%3N")
| eval distributor_timestamp = strftime(strptime(distributor_timestamp, "%Y/%m/%d %H:%M:%S.%4N"), "%Y/%m/%d %H:%M:%S.%4N")
| eval ep_timestamp_unix = strptime(ep_timestamp, "%Y/%m/%d %H:%M:%S.%3N")
| eval distributor_timestamp_unix = strptime(distributor_timestamp, "%Y/%m/%d %H:%M:%S.%4N")
| eval ep_latency = ep_timestamp_unix - distributor_timestamp_unix

| table trade_id, portfolio_name, portfolio_entity, sky_id, event_id, booking_timestamp, booking_timestamp_unix, distributor_timestamp, distributor_timestamp_unix, ep_timestamp, distributor_latency, ep_latency, catchup_updated_time, wss_to_sky_latency, mq_to_sky_update_latency, mx_status, operation, action, catchup_unix_time
| dedup sky_id
| sort booking_timestamp
| rex field=trade_id "^\w+ (?<dealnumber>\d+)$"
| join type=left dealnumber
[ search index=wss "Sending message" source="/proj/flowfx/wss/FFXWS01P/log/MQ1.log"
```Exclude Far Legs of Swap Trades for first Iteration of Dash```
NOT "<swap_leg>2</swap_leg>"
```Exclude Cancels, Amends, Auxiliaries, Allocations, Blocks - allocated ```
NOT "<status>"
```Exclude MM Deals ```
NOT "<WSSMMTRADE>"
| rex "\<transaction\>(?P<tid>.*?)\<\/transaction\>"
| rex "\<deal_number\>(?P<dealnumber>.*?)\<\/deal_number\>"
| rex "\<external_deal\>(?P<sourcesystemid>.*?)\<\/external_deal\>"
| rex "\<cust_type\>(?P<custType>.*?)\<\/cust_type\>"
| eval region=case(host == "pffxa01z", "Global", host == "pffxa02z", "China")
| eval wssSendingTime=strftime(_time,"%Y-%m-%d %H:%M:%S.%Q")
| table tid,dealnumber,region,custType,sourcesystemid,wssSendingTime, booking_timestamp, booking_timestamp_unix, distributor_timestamp_unix, catchup_unix_time
| join type=left tid
[ search index=wss source="/proj/flowfx/ffx/log/flowfx-processor.log" "INFO SLA FFX-Processor received"
| rex "transactionId\=(?P<tid>.*?)\,"
| eval flowfxincomingtime=strftime(_time,"%Y-%m-%d %H:%M:%S.%Q")
| table flowfxincomingtime,tid, booking_timestamp, booking_timestamp_unix, distributor_timestamp_unix, catchup_unix_time
]
| table tid,dealnumber,region,custType,sourcesystemid,wssSendingTime,flowfxincomingtime
| eval wssSendingTimeUnix=strptime(wssSendingTime,"%Y-%m-%d %H:%M:%S.%Q")
| eval flowfxincomingtimeUnix=strptime(flowfxincomingtime,"%Y-%m-%d %H:%M:%S.%Q")
| eval timebetweenWssFlowfx = flowfxincomingtimeUnix - wssSendingTimeUnix
| table tid,dealnumber,region,custType,sourcesystemid,wssSendingTime,flowfxincomingtime,timebetweenWssFlowfx,wssSendingTimeUnix,flowfxincomingtimeUnix, booking_timestamp, booking_timestamp_unix, distributor_timestamp_unix, catchup_unix_time
| join type=left tid
[ search index=wss source="/proj/flowfx/ffx/log/flowfx-trade-sender-cim.log" "INFO SLA FFX-Trade-Sender sent"
| rex "nearTransactionId\=(?P<tid>.*?)\,"
| eval CIMsendingTime=strftime(_time,"%Y/%m/%d %H:%M:%S.%Q")
| eval MQ_available_time=strftime(_time - 7200, "%Y-%m-%d %H:%M:%S.%Q")
| table CIMsendingTime,tid,MQ_available_time,booking_timestamp, booking_timestamp_unix, distributor_timestamp_unix
]
| table tid,dealnumber,region,custType,sourcesystemid,wssSendingTime,flowfxincomingtime,timebetweenWssFlowfx,wssSendingTimeUnix,flowfxincomingtimeUnix,CIMsendingTime, MQ_available_time, booking_timestamp, booking_timestamp_unix, distributor_timestamp_unix, catchup_unix_time
]
| eval CIMsendingTime_unix = strptime(CIMsendingTime, "%Y/%m/%d %H:%M:%S.%3Q")
| eval cim_latency = CIMsendingTime_unix - booking_timestamp_unix
| eval distributor_latency = distributor_timestamp_unix - CIMsendingTime_unix
| eval distributor_to_sky_latency = catchup_unix_time - CIMsendingTime_unix
| where len(CIMsendingTime) > 0
| eval cim_latency = round(cim_latency * 1000,0)
| eval distributor_latency = round(distributor_latency * 1000,0)
| eval ep_latency = round(ep_latency * 1000,0)
| eval wss_to_sky_latency = round(wss_to_sky_latency * 1000,0)
| eval mq_to_sky_update_latency = round(mq_to_sky_update_latency * 1000,0)
| eval distributor_to_sky_latency = round(distributor_to_sky_latency * 1000,0)
| table trade_id, portfolio_name, sky_id, booking_timestamp,CIMsendingTime, distributor_timestamp, ep_timestamp, catchup_updated_time, wss_to_sky_latency, cim_latency, distributor_latency, ep_latency, mq_to_sky_update_latency, distributor_to_sky_latency, mx_status, operation, action

My above current search query but i get more events and less statistics results in last 24 hours period and compared to last 4 hours period.

[wm]

Thanks @gcusello ,

How do I replace join with stats as I am taking data from other tables

[gcusello]

Hi @wm ,

probably because you're using many join commands and this command uses subsearches: subsearches have the limit of 50,000 results, so probably the match between the subsearches are less because there are less results than the ones that should be.

Splunk isn't a database so you cannot use the approach thet you usually use in a query, in other words, avoid join command and correlata searches using the stats command.

In addition, using join, yousurela have a very slow search.

Search in Community and you'll find many examples of replace of join with stats.

Ciao.

Giuseppe