topic Re: How can I put fields from joined searches in a summary index? in Splunk Search

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

paimonsoror — Tue, 29 Sep 2020 14:40:31 GMT

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.

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

paimonsoror — Thu, 29 Jun 2017 13:34:24 GMT

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, "\"", "'") . "\""

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

paimonsoror — Thu, 29 Jun 2017 13:58:25 GMT

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

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

sideview — Tue, 29 Sep 2020 14:37:01 GMT

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.