Join DB Connect input results with the DB Connect input configuration attributes

[chrismmckenna]

I want to combine the results from DBConnect inputs with the associated connection_name, stanza_name and sql query fields in 'index=_internal sourcetype=dbx_audit' . I have used a naming convention for the DBConnect input sourcetype that will pattern match in the _internal index stanza_name field.

I've successfully used append to return the two data sets (search below), but want the results inline with each other so I can use the hostname, source, sourcetype, connection_name and sql fields in the alert configuration.

This is what I have so far:

index=bfr_oracle_sbx sourcetype="NO_DATA"
| rename TIMEFRAME as TimeFrame
| rename TASK_NAME as TaskName
| rename COUNT as ErrorCount
| rename AVG as Average
| rename STDDEV as StandardDeviation
| rename MIN_DURATION as MinDuration
| rename MAX_DURATION as MaxDuration
| eval source = split(source, "_")
| eval source = mvindex(source, 0)
| append [search index=_internal sourcetype=dbx_audit | where like (stanza_name, "%NO_DATA%") | eval sql=split(_raw, "sql=") | eval sql=mvindex(sql, 1)]
| table host, source, sourcetype, Average, sql

I'd want to use the 'sourcetype' defined and returned by the DB Connect results as a pattern match in the _internal stanza_name field - at least I think I do - that's how I thought best to correlate the DB Connect input results with the DB Connect input configuration parameters.

[sloshburch]

I don't have a DBConnect instance to validate this with so trying my best. From a logical perspective you could load all the data in the base search, and then use a stats to combine. Also, the renames and the evals can be collapsed:

( index=bfr_oracle_sbx sourcetype="NO_DATA" ) OR ( index=_internal sourcetype=dbx_audit stanza_name=*NO_DATA* )
| rename TIMEFRAME as TimeFrame, TASK_NAME as TaskName, COUNT as ErrorCount, AVG as Average, STDDEV as StandardDeviation, MIN_DURATION as MinDuration, MAX_DURATION as MaxDuration
| eval source = mvindex( split( source , "_" ) , 0 ), sql=mvindex( split(_raw, "sql=" ) , 1 )
| fields host, source, sourcetype, Average, sql
| stats latest(*) AS * by ___

What are the unique things that correlate the results between the two indexes? Those field names should go where the "by ___" in the above.

[chrismmckenna]

SloshBurch,

What I wound up doing is creating a lookup file to store all of the requisite information I need for the dbx_audit events in _internal. The stanza_name of the DB Connect input contains the sourcetype associated with the returned data set. I have a scheduled report that parses the stanza_name and saves a field 'srctype' to the lookup plus other fields, like sql. When an alert triggers, I do a lookup to associate the sql with alert saved search.

[sloshburch]

Ok, sounds like you found a solution? Maybe flesh it out and accept it as answer to your own question?

[chrismmckenna]

This isn't quite providing the desire results. The above saved search is used for alerting purposes. I want to provide the sql with every alert that is triggered for DB Connect inputs. If multiple alerts are triggered, the sql is only provided with the latest alert.

The saved search name (alert) has the same value as the DB Connect input's stanza_name.

[sloshburch]

Show us what you've got so far since it likely changed since the Aug 2nd post.

[chrismmckenna]

Thank you Burch. This works.

I'm going to be doing this repeatedly for a large number of alerts. I'd like to keep it clean and simple for ease of reuse.

I'm thinking that I'd like to use a KV store for the dbinput configuration attributes.

The 'source' metadata of the dbinput result is set to the 'connection_name' field of the dbinput configuration attributes.

index=_internal sourcetype=dbx_audit operation=dbinput
| dedup stanza_name
| fields host,connection_name,stanza_name,sql,state,success,log_level
| outputlookup DBCONNECT_DBXAUDIT_DBINPUT

I've created the KV store and tried to populate with the above, but the following error:

"Error in 'outputlookup' command: Lookup failed because collection 'DBCONNECT_DBXAUDIT_DBINPUT' in app 'search' does not exist, or user 'christopher.mckenna@pb.com' does not have read access"

The docs say I need Cloud Support to add the collection. So that's what I've done.

[sloshburch]

Cool. A few things:

1. If this works (as you mentioned), then select the "Accept Answer" link so others know this is an answered question they can learn from
2. if you are doing dedup and fields, you likely could get better performance with the 'stats' command as shown in my example (| stats values(*) by stanza_name)
3. You could also explore a non KVstore lookup (csv) or a Summary Indexing approach as well. Both should be defined in the docs.

[chrismmckenna]

Bear in mind that the renames and evals before the append [subsearch ...] only deal with the results from a DB Connect input. These will vary depending on the DC Connect input results. I'm looking for a general subsearch append/join method that correlates: result.sourcetype <=> _internal.stanza_name