Splunk Search

Splunk Search Query to fill empty columns of data with data from other rows

wm
New Member

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_idsss_idbooking_timestampbooking_mq_timestampbooking_ep_timestampmq_latencyep_latencycatchup_updated_timewww_to_sss_latencymq_to_sss_update_latency
abc 1235976165192024/06/15 09:22:372024/06/15 09:24:162024/06/15 09:24:169902024/06/15 09:24:2610910
abc 341597616518        
abc 218931597616517        
abc 12015976149372024/06/15 07:50:142024/06/15 07:51:122024/06/15 07:51:125802024/06/15 07:51:19657
abcc 219597614936        
abc 219597614935        

 

 

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 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Your question would be much easier to understand if you skip the complex SPL, first give sample data (anonymize as needed), illustrate desired output, then explain the logic between illustrated data sample and desired output without SPL.  To diagnose your attempted SPL, you also illustrate the actual output from that SPL, and explain how actual output is different from desired output if that is not painfully obvious. (Remember: What is "obvious" to you is not always obvious to volunteers who lack intimate knowledge about your dataset and your use case.)

As a side note, the illustrated SPL implies that your sourcetype=sss_trade_www_timestamp contains snippets like "trade_id=foo",  "mx_status=bar", and so on.  If so, Splunk would have extracted trade_id, status, etc. without your rex.  Is there any reason Splunk is not giving you those?

0 Karma

wm
New Member

ah okay thanks gotcha. 

Current output is like the table i shown and desired output is the table after that.

catchup_updated_time is not populated for some as its taken from another sourcetype which is www_cattchup_Logs

Please let me know if futher info is required

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Will filldown do the trick? E.g.

| filldown catchup_updated_time
0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...