All Apps and Add-ons
Highlighted

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

New Member

I want to combine the results from DBConnect inputs with the associated connectionname, stanzaname and sql query fields in 'index=internal sourcetype=dbxaudit' . I have used a naming convention for the DBConnect input sourcetype that will pattern match in the internal index stanzaname 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=bfroraclesbx sourcetype="NODATA"
| rename TIMEFRAME as TimeFrame
| rename TASK
NAME as TaskName
| rename COUNT as ErrorCount
| rename AVG as Average
| rename STDDEV as StandardDeviation
| rename MINDURATION as MinDuration
| rename MAX
DURATION as MaxDuration
| eval source = split(source, "")
| eval source = mvindex(source, 0)
| append [search index=
internal sourcetype=dbxaudit | where like (stanzaname, "%NODATA%") | 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 stanzaname 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.

0 Karma
Highlighted

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

New Member

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.stanzaname

0 Karma
Highlighted

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

Ultra Champion

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.

Highlighted

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

New Member

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 'DBCONNECTDBXAUDITDBINPUT' 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.

0 Karma
Highlighted

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

Ultra Champion

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.
0 Karma
Highlighted

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

New Member

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.

0 Karma
Highlighted

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

Ultra Champion

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

0 Karma
Highlighted

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

New Member

SloshBurch,

What I wound up doing is creating a lookup file to store all of the requisite information I need for the dbxaudit events in _internal. The stanzaname 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.

0 Karma
Highlighted

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

Ultra Champion

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

0 Karma