Splunk Search

How do I merge two rows coming from different macros?

neerajs_81
Builder

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.

neerajs_81_1-1660030705687.png

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
Labels (3)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

Hi @neerajs_81,

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

Anyway the solution is to use stats.

Ciao.

Giuseppe

0 Karma

neerajs_81
Builder

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

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @neerajs_81,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...