Filtering mutiple table rows created from a single log entry

dhennessey — Thu, 22 Jun 2017 13:06:40 GMT

I have multiple JDBC connection pools and their realtime stats are written to a log on a regular basis. I would like to isolate the information for a single pool (poolC) and create some visualizations from the data, allowing me to track pool performance over time.
My problem is that even after I've isolated the fields from the log entry, any attempts to filter based on the isolated fields fails. I suspect this is because all the data originates from a single log entry. Here is a sample output of a single log entry:

LATEST STATS 
   Pool Name :  PoolA[Num Active :   0]  [Max Idle Pool :   2]  [Min Idle Pool :   1]  [Total Connections Used :      533]
    Pool Name :  PoolB[Num Active :   0]  [Max Idle Pool :   2]  [Min Idle Pool :   1]  [Total Connections Used : 8]
    Pool Name :  PoolC[Num Active :   0]  [Max Idle Pool :   3]  [Min Idle Pool :   1]  [Total Connections Used : 890]
    Pool Name :  PoolD[Num Active :   0]  [Max Idle Pool :   3]  [Min Idle Pool :   1]  [Total Connections Used : 386] 
END STATS

The first thing I've done is to use the 'rex' command to pull out fields from the log entry.

index="poolIndex" source="/dir/connectionPool.log" | rex max_match=0 field=_raw "Pool Name : (?<poolname>.+?(?=\[))\[Num Active :\s+(?<numactive>\d+)\]\s+\[Max Idle Pool :\s+(?<maxidlepool>\d+)\]\s+\[Min Idle Pool :\s+(?<minidlepool>\d+)\]\s+\[Total Connections Used :\s+(?<totconn>\d+)"

With the fields defined, I then 'piped' the results to a table command to format the data to tablular form:

| table host _time poolname numactive maxactivepool maxidlepool minidlepool totconn

This creates a table visualization of the data using the extracted fields:

poolname          numactive          maxidlepool          minidlepool          totconn
PoolA                   0                2                 1                    533
PoolB                   0                2                 1                    8
PoolC                   0                3                 1                    890
PoolD                   0                3                 1                    386

My hope was that I could then use the 'search' command to filter the results for just the poolC row of data. So I added an additional 'pipe' to search command:

| search poolname="poolC"

So the full query is as follows:

index="poolIndex" source="/dir/connectionPool.log" | rex max_match=0 field=_raw "Pool Name : (?<poolname>.+?(?=\[))\[Num Active :\s+(?<numactive>\d+)\]\s+\[Max Active Pool :\s+(?<maxactivepool>\d+)\]\s+\[Max Idle Pool :\s+(?<maxidlepool>\d+)\]\s+\[Min Idle Pool :\s+(?<minidlepool>\d+)\]\s+\[Total Connections Used :\s+(?<totconn>\d+)"  | table host _time poolname numactive maxactivepool maxidlepool minidlepool totconn | search poolname="poolC"

Even though I am attempting to filter on poolname="poolC", the records for all pools are returned. I suspect this is because the filter is still acting on the single log entry from which all the data was derived. I would like the filter to act on the 'table' results and return the single row containing the poolC metrics.

Is there a command that will filter the results of a table when the rows are derived from a single log entry?
Is the 'table' command the best way to approach this problem?

Ultimately my goal is to isolate the poolC metrics so that I can create charts and visualizations on it over time.

Re: Filtering mutiple table rows created from a single log entry

richgalloway — Thu, 22 Jun 2017 14:22:14 GMT

If the rex command finds more than one match, it puts them all into a multi-value field. To treat that field as multiple events, you must use the mvexpand command. Since you have several mv fields, getting them all expanded is a little tricky, but there's an example in the search ref manual. See Example 3 at http://docs.splunk.com/Documentation/Splunk/6.6.1/SearchReference/Mvexpand.

topic Filtering mutiple table rows created from a single log entry in Getting Data In

Filtering mutiple table rows created from a single log entry

Re: Filtering mutiple table rows created from a single log entry