topic Re: Comparing fields from two events in Splunk Search

Comparing fields from two events

rune_hellem — Mon, 28 Sep 2020 16:22:03 GMT

The data shown here is PMI (Performance Monitoring Infrastructure) data collected from WebSphere using a scripting framework from IBM. I am therefore not really able to format the output in any other way than shown below. Problem is that in order to create an alert for exhausted ThreadPools I need to compare fields from two events, as shown below

2014-04-11 08:32:33:902,node02,CEMember2,type=ThreadPool+name=WebContainer>WebContainer,WebContainer,threadPoolModule,PoolSize,id,4,type,BoundedRangeStatistic,lastSampleTime,2014-04-11 05:27:24:069,current,13,bounded,50...50,waterMarks,1...50,mean,9.227478332840127,integral,3.34956227E8
2014-04-11 08:32:33:902,node02,CEMember2,type=ThreadPool+name=WebContainer>WebContainer,WebContainer,threadPoolModule,ActiveCount,id,3,type,BoundedRangeStatistic,lastSampleTime,2014-04-11 08:32:29:404,current,2,bounded,0...0,waterMarks,1...11,mean,2.6708347887819315,integral,1.2661146E8

I have created a custom sourcetype creating fields, the one needed (as I am aware of) for this question is

req_time = 2014-04-11 08:32:33:902
pmi_node_name = node02
pmi_server_name = CEMember2
pmi_stat_provider_short = WebContainer
pmi_statistics_name = {PoolSize|ActiveCount}
pmi_current = {13|2}
pmi_bounded = {0|50}

I have created an alert like this

sourcetype=pmi_threadpool_events pmi_bounded > 0 | where pmi_current>pmi_bounded

But I have realized that I want this (pseudocode)

sourcetype=pmi_threadpool_events | where the following fields match: (req_time AND pmi_nodename AND pmi_servername AND pmi_stat_provider_short  AND THEN ((pmi_statistics_name = ActiveCount) pmi_current) > ((pmi_statistics_name = PoolSize)pmi_bounded)

That is - since I check several nodes and servers at the same time returning several pmi_stat_provider_short I need to have identical req_time, pmi_nodename, pmi_servername and pmi_stat_provider_short to know that the events is a transaction, and then check if pmi_current from the event with pmi_statistics_name = ActiveCount is greater than or equal to pmi_bounded from the event with pmi_statistics_name = PoolSize.

Re: Comparing fields from two events

MuS — Fri, 11 Apr 2014 08:36:04 GMT

do you have req_time, pmi_nodename and pmi_servername fields in all events?

Re: Comparing fields from two events

rune_hellem — Fri, 11 Apr 2014 08:38:58 GMT

Yes, all events have all fields as shown above, that is safe to assume

Re: Comparing fields from two events

somesoni2 — Fri, 11 Apr 2014 12:58:38 GMT

How about this:

sourcetype=pmi_threadpool_events pmi_statistics_name = ActiveCount OR pmi_statistics_name = PoolSize | stats max(pmi_current) as pmi_current max(pmi_bounded) as pmi_bounded by req_time, pmi_nodename, pmi_servername | where pmi_current>pmi_bounded

Updated answer.
Try this. I have eliminated the pmi_current field from events with pmi_statistics=ActiveCount.

|multisearch [search sourcetype=pmi_threadpool_events pmi_statistics_name = ActiveCount | table req_time, pmi_nodename, pmi_servername, pmi_statistics_name, pmi_current,pmi_stat_provider_long][search  sourcetype=pmi_threadpool_events pmi_statistics_name = PoolSize | table req_time, pmi_nodename, pmi_servername, pmi_statistics_name,pmi_bounded,pmi_stat_provider_long] | stats max(pmi_current) as pmi_current max(pmi_bounded) as pmi_bounded by req_time, pmi_nodename, pmi_servername | where pmi_current>pmi_bounded

Re: Comparing fields from two events

rune_hellem — Mon, 28 Sep 2020 16:22:39 GMT

To reflect my latest edit, I updated the search string to have pmi_stat_provider_short in the by clause

sourcetype=pmi_threadpool_events pmi_statistics_name = ActiveCount OR pmi_statistics_name = PoolSize | stats max(pmi_current) as pmi_current max(pmi_bounded) as pmi_bounded by req_time, pmi_node_name, pmi_server_name,pmi_stat_provider_long | where pmi_current>pmi_bounded

It is close, but still no sigar - since both events have the field pmi_current it will select random from either one of the events, which is not correct since PoolSize pmi_current can be higher than ActiveCount

Re: Comparing fields from two events

somesoni2 — Mon, 28 Sep 2020 16:22:55 GMT

Try the updated answer. I have included your new field pmi_stat_provider_short in it.

Re: Comparing fields from two events

mikaelbje — Mon, 28 Sep 2020 16:25:57 GMT

Hi Rune,

Could you throw in an eval with if to NULL out the pmi_current you don't need, combined with the answer from somesoni2? As far as I understand you're only interested in the pmi_current from one of the events, i.e.

| eval pmi_current=if(pmi_statistics_name == "ActiveCount", pmi_current, NULL)

Not sure if that's what you're looking for, just an idea 🙂

You could also check this out: http://answers.splunk.com/answers/93930/how-to-create-a-new-field-with-values-in-existing-field-based-on-the-values-in-other-field
You might lose some important data with this approach though. I guess your BY clause would be pmi_statistics_name

Re: Comparing fields from two events

rune_hellem — Thu, 24 Apr 2014 11:49:57 GMT

Solved - thanks Mikael for the eval-command

sourcetype=pmi_threadpool_events  | eval pmi_current=if(pmi_statistics_name == "ActiveCount", pmi_current, NULL)  | eval pmi_bounded=if(pmi_statistics_name == "PoolSize", pmi_bounded, NULL) | transaction _time,pmi_node_name,pmi_server_name,pmi_stat_provider_long | WHERE pmi_current >= pmi_bounded

Re: Comparing fields from two events

rune_hellem — Thu, 24 Apr 2014 11:53:41 GMT

Got the error

"...Multisearch subsearches may only contain purely streaming operations..."

and after trying out the eval command provided by Mikael I remembered the transaction-command.