How do I merge two rows coming from different macros?

[neerajs_81]

Hi All,
I am appending two macros to generate the following result set using append command.  The 1st row comes from one macro while the 2nd row comes from the other.  Field rule_id is common in both macro result set.

How can i achieve the following  ? End goal is to show the same in Dashboard so i am looking to consolidate the data into one common row .   Any suggestions ?   I have tried using eval as recommended by @gcusello  in Solved: Merging events from two indexes - Splunk Community  but its not working out in my case.

Desired Output:

Triggered_time	Acknowledged_time	difference	rule_id
2022-08-03 23:27:13	2022-08-03 23:28:37	00:01:24.9021888	xxxxx

[ITWhisperer]

Assuming, of the 2 differences, you want the difference from the Acknowledged event

| eval difference=if(isnotnull(Acknowledged_time),difference,null())
| stats values(Triggered_time) as Triggered_time values(Acknowledged_time) as Acknowledged_time values(difference) as difference by rule_id

If not, please specify which difference you want to keep

[gcusello]

Hi @neerajs_81,

could you share the search you used to achieve your result?

Anyway the solution is to use stats.

Ciao.

Giuseppe

[neerajs_81]

Thanks for responding. These macros are specific to Splunk ES.

`get_notable_index` | eval `get_event_id_meval`,rule_id=event_id | search rule_id=353EA38E-CBD0-4D90-9EDA-B15D16089D17@@notable@@0fb5ae992e6da8629c0a67596540bf58| `get_owner` | fields rule_id, owner, _time, user | eval Triggered_time_epoch=if(owner=="unassigned",_time,null())
| append [| `incident_review` | where status_label="TTA- Acknowledged"
| search rule_id=353EA38E-CBD0-4D90-9EDA-B15D16089D17@@notable@@0fb5ae992e6da8629c0a67596540bf58]
| eval status_time=strftime(_time,"%Y-%m-%d %H:%M:%S")
| eval Triggered_time=strftime(Triggered_time_epoch,"%Y-%m-%d %H:%M:%S")
| eval Acknowledged_time =if(status_label=="TTA- Acknowledged",status_time,null())
| streamstats window=2 global=f range(_time) as difference by rule_id
| fieldformat difference=tostring(difference,"duration")
| table Triggered_time Acknowledged_time difference rule_id rule_name owner status_label

[gcusello · Accepted solution]

Hi @neerajs_81,

if it comes from ES, Ihint to leave all as is.

If you want anyway to have only one row, you could add a stats command at the end of your search:

`get_notable_index` | eval `get_event_id_meval`,rule_id=event_id | search rule_id=353EA38E-CBD0-4D90-9EDA-B15D16089D17@@notable@@0fb5ae992e6da8629c0a67596540bf58| `get_owner` | fields rule_id, owner, _time, user | eval Triggered_time_epoch=if(owner=="unassigned",_time,null())
| append [| `incident_review` | where status_label="TTA- Acknowledged"
| search rule_id=353EA38E-CBD0-4D90-9EDA-B15D16089D17@@notable@@0fb5ae992e6da8629c0a67596540bf58]
| eval status_time=strftime(_time,"%Y-%m-%d %H:%M:%S")
| eval Triggered_time=strftime(Triggered_time_epoch,"%Y-%m-%d %H:%M:%S")
| eval Acknowledged_time =if(status_label=="TTA- Acknowledged",status_time,null())
| streamstats window=2 global=f range(_time) as difference by rule_id
| fieldformat difference=tostring(difference,"duration")
| stats values(Triggered_time) AS Triggered_time values(Acknowledged_time) AS Acknowledged_time values(difference) AS difference values(rule_name) AS rule_name values(owner) AS owner values(status_label) AS status_label BY rule_id 
| table Triggered_time Acknowledged_time difference rule_id rule_name owner status_label

Ciao.

Giuseppe

[gcusello]

Hi @neerajs_81,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉