I'll first insert my whole splunk search query and show whats it showing and whats the expected result index=sss sourcetype=sss_trade_www_timestamp
| rex field=_raw "trade_id=\"(?<tr...
See more...
I'll first insert my whole splunk search query and show whats it showing and whats the expected result index=sss sourcetype=sss_trade_www_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=\"(?<booking_mq_timestamp>\X+)\", sky_to_mq"
| rex field=_raw "mq_latency=\"(?<mq_latency>[^\"]+)\".*\bportfolio_name=\"(?<portfolio_name>[^\"]+)\".*\bportfolio_entity=\"(?<portfolio_entity>[^\"]+)\".*\btrade_type=\"(?<trade_type>[^\"]+)"
| join event_id
[ search index=sss sourcetype=Sss_Www_EP_Logs "Successfully processed event"
| rex field=_raw "INFO: (?<booking_ep_timestamp>\d{8} \d{2}:\d{2}:\d{2}.\d{3})"
| rex field=_raw "Successfully processed event: (?<event_id>\X+), action"
| eval booking_ep_timestamp = strftime(strptime(booking_ep_timestamp."+0800", "%Y%d%m %H:%M:%S.%N%z"), "%Y/%m/%d %H:%M:%S")]
| join type=left sss_id
[ search index=sss "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=booking_mq_timestamp "(?<booking_mq_timestamp>\d{4}/\d{2}/\d{2} \d{2}:\d{2}:\d{2})"
| eval booking_pnl_timestamp = booking_pnl_timestamp."+0800"
| eval ep_latency = strptime(booking_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 sss_id = "*"
| search event_id = "*"
| search action = "*"
| search mx_status = "live"
| search operation = "*"
| table trade_id, sss_id, event_id, booking_timestamp, booking_mq_timestamp, booking_ep_timestamp, mx_status, operation, action, mq_latency, ep_latency, portfolio_name, portfolio_entity
| sort booking_ep_timestamp
| join type=left sss_id
[ search index=sss sourcetype=sss_cashfx_catchup_logs "[Www] - Done incremental update"
| rex field=_raw "Max Ssslib TradeID: (?<sss_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"), "%Y/%m/%d %H:%M:%S")
| table sss_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")
| eval www_to_sss_latency = round(catchup_unix_time - booking_timestamp_unix, 0)
| eval booking_mq_timestamp_unix = strptime(booking_mq_timestamp, "%Y/%m/%d %H:%M:%S")
| eval mq_latency = round(booking_mq_timestamp_unix - booking_timestamp_unix, 0)
| eval booking_ep_timestamp_unix = strptime(booking_ep_timestamp, "%Y/%m/%d %H:%M:%S")
| eval ep_latency = round(booking_ep_timestamp_unix - booking_mq_timestamp_unix, 0)
| eval mq_to_sss_update_latency = round(catchup_unix_time - booking_mq_timestamp_unix, 0)
| table trade_id, portfolio_name, portfolio_entity, sss_id, event_id, booking_timestamp, booking_mq_timestamp, booking_ep_timestamp, mq_latency, ep_latency, catchup_updated_time, www_to_sss_latency, mq_to_sss_update_latency, mx_status, operation, action,
| dedup sss_id
| sort booking_timestamp It gives me this table but as I cant show all the tables row ill show the relevant ones trade_id sss_id booking_timestamp booking_mq_timestamp booking_ep_timestamp mq_latency ep_latency catchup_updated_time www_to_sss_latency mq_to_sss_update_latency abc 123 597616519 2024/06/15 09:22:37 2024/06/15 09:24:16 2024/06/15 09:24:16 99 0 2024/06/15 09:24:26 109 10 abc 341 597616518 abc 218931 597616517 abc 1201 597614937 2024/06/15 07:50:14 2024/06/15 07:51:12 2024/06/15 07:51:12 58 0 2024/06/15 07:51:19 65 7 abcc 219 597614936 abc 219 597614935 just assume the booking_timestamp, booking_mq_timestamp, booking_ep_timestamp, mq_latency, ep_latency are all filled Ok but since my catchup_updated_time is taking from a log entry its populated (eg. 2024-06-15 10:57:03,114 [Www] - Done incremental update. Max SSSSSS TradeID: 597618769), but the rest of the rows/columns are not populated. I want to highlight this specific row since its taking from logs and also fill the empty catchup_updated_time such that 597616518 and 597616517 should take the catchup_updated_time, latency etc of 597616519 as their id is before and 597616519 is the max id taken from logs its row should be highlighted. hence anything before or smaller than 597616519 should have same catchup_updated_time. However not until 597614937 as it already has a catchup_updated_time taken from logs. So same for the rest of the rows. Is this complicateD? Please let me know if you need more info