Splunk Search

How can I put fields from joined searches in a summary index?

paimonsoror
Builder

Hi folks;

I have the following query that i use as a base search to feed a dashboard:

index=app_caspectrum sourcetype="spectrum:event" source="prod_spectrum_reporting" | join event_key [ search index=app_caspectrum sourcetype="spectrum:alarm" source="prod_spectrum_reporting"    | rename orig_event_key as event_key    | join type=left alarm_key [ search index=app_caspectrum sourcetype="spectrum:alarm:activity" source="prod_spectrum_reporting"     | rename time as activity_time     | rename user as activity_user     | transaction alarm_key      | eval LastStatus=mvindex(activity_s, -1)     | table alarm_key LastStatus   ] ]

There are several fields that are part of each event that is produced as they are the fields from each subsearch for the related event_key's and whatnot. The problem however, is when I go and 'collect' this data and put it in my summary index, it only seems to be 'collecting' the fields from the first search. The fields that were added as a result of the joins are not being added.

I have even tried something silly like this :

index=app_caspectrum sourcetype="spectrum:event" source="prod_spectrum_reporting" | join event_key [ search index=app_caspectrum sourcetype="spectrum:alarm" source="prod_spectrum_reporting"    | rename orig_event_key as event_key    | join type=left alarm_key [ search index=app_caspectrum sourcetype="spectrum:alarm:activity" source="prod_spectrum_reporting"     | rename time as activity_time     | rename user as activity_user     | transaction alarm_key      | eval LastStatus=mvindex(activity_s, -1)     | table alarm_key LastStatus   ] ]
| fields condition_name

condition_name is a field that is added to each event from one of the join's. Once this is added to a summary index, condition_name isn't there.

0 Karma

sideview
SplunkTrust
SplunkTrust

It looks like you've figured out how to use the collect command manually, which is great.
I thought I'd give you a different answer trying to also rewrite the search to avoid the join command.
This search looks very much like a case that can be rewritten to not use join at all.

However I can't tell you exactly how to do it yet, without knowing more of the relationship between alarm_key and event_key.

Can you provide some specific example events to illustrate that relationship?

Until then, I can get you as far as this:

index=app_caspectrum source="prod_spectrum_reporting" ( sourcetype="spectrum:event" OR sourcetype="spectrum:alarm" OR sourcetype="spectrum:alarm:activity") 
| eval event_key=if(sourcetype="spectrum:alarm",orig_event_key,null())
| eval activity_time=if(sourcetype="spectrum:alarm:activity",time)
| eval activity_user=if(sourcetype="spectrum:alarm:activity",user)

From there, possibly it's as simple as just tacking a

| stats last(activity_s) as LastStatus values(condition_name) as Condition values(mclass_name) as ModelClass values(mtype_name) as ModelType values(device_type) as DeviceType values(activity_time) as ActivityTime by alarm_key event_key 

but I suspect there's a nuance or two in the relationship between event_key and alarm_key that will require a little streamstats/eventstats/eval magic trick just before the stats.

0 Karma

paimonsoror
Builder

I may have figured this out. Here is what I ended up doing:

index=app_caspectrum sourcetype="spectrum:event" source="prod_spectrum_reporting" 
| eval LastStatus="Triggered"
| eval activity_time="NA"
| eval activity_user="NA"
| eval activity_s="NA"
| join event_key [ search index=app_caspectrum sourcetype="spectrum:alarm" source="prod_spectrum_reporting"    
 | rename orig_event_key as event_key    
 | join type=left alarm_key [ 
  search index=app_caspectrum sourcetype="spectrum:alarm:activity" source="prod_spectrum_reporting"     
  | rename time as activity_time     
  | rename user as activity_user     
  | transaction alarm_key      
  | eval LastStatus=mvindex(activity_s, -1)     
  | table alarm_key LastStatus activity_time activity_s  
  ] 
] 
| eval _raw="Alarm Time=" . time . " Model=" . model_name . " Condition=" . condition_name . " ModelClass=" . mclass_name . " ModelType=" . mtype_name . " DeviceType=" . device_type . " Activity=" . activity_s . " ActivityTime=" . activity_time . " Event=\"" . replace(event_msg, "\"", "'") . "\"" 
| collect index="summary_executive" sourcetype="executive_stash"

and then creating a transforms.conf on my searchheads:

[executive_stash_delim]
DELIMS = "="

and a props.conf

[executive_stash]
REPORT-activity = executive_stash_delim
0 Karma

paimonsoror
Builder

For what it's worth, I've also tried something like this to no avail:

index=app_caspectrum sourcetype="spectrum:event" source="prod_spectrum_reporting" 
| eval LastStatus="Triggered"
| eval activity_time="NA"
| eval activity_user="NA"
| eval activity_s="NA"
| join event_key [ search index=app_caspectrum sourcetype="spectrum:alarm" source="prod_spectrum_reporting"    
 | rename orig_event_key as event_key    
 | join type=left alarm_key [ 
  search index=app_caspectrum sourcetype="spectrum:alarm:activity" source="prod_spectrum_reporting"     
  | rename time as activity_time     
  | rename user as activity_user     
  | transaction alarm_key      
  | eval LastStatus=mvindex(activity_s, -1)     
  | table alarm_key LastStatus activity_time activity_s  
  ] 
] 
| eval _raw="Alarm Time=" . time . " Model=" . model_name . " Condition=" . condition_name . " ModelClass=" . mclass_name . " ModelType=" . mtype_name . " DeviceType=" . device_type . " Activity=" . activity_s . " ActivityTime=" . activity_time . " Event=\"" . replace(event_msg, "\"", "'") . "\""
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...