topic Re: Fields value correlation not working in Splunk Search

Fields value correlation not working

hemendralodhi — Mon, 21 Nov 2016 02:48:56 GMT

Hello,

I have a query regarding ordering of ElapsedTime field. It is not coming properly with associated ServiceLayerName.

I want to have a table which shows tabular details of fields and Message Identifier is common in them. I am using transaction command to group Message Identifier. For each InterfaceName there are couple of ServiceLayerNames and associated ElapsedTime with them. Issue is when I run below query fields are grouped and showing result but ElapsedTime is out of order for different ServiceLayerName.

For e.g. Below is my query:

index=index  MessageIdentifier= | transaction MessageIdentifier 
| table _time InterfaceName BusinessIdentifier MessageIdentifier ServiceLayerName ElapsedTime

Output:

_time         InterfaceName   BusinessIdentifier  MessageIdentifier  ServiceLayerName  ElapsedTime  
2016-11-03    Interface       BI                  MI                 A                 00:00:00.085
                                                                     B                 00:00:00.091

Time: 00:00:00.085 should be associated with B and 00:00:00.091 should be with A but associated values are not correct. I think table command is just presenting ElapsedTime value and not correlated with ServiceLayerName.

Can you please advise how to correct this or any other way to formulate the query.

Thanks
Hemendra

Re: Fields value correlation not working

hemendralodhi — Mon, 21 Nov 2016 02:53:55 GMT

Re: Fields value correlation not working

hemendralodhi — Mon, 21 Nov 2016 02:54:15 GMT

This is how output looks like.

Re: Fields value correlation not working

lguinn2 — Mon, 21 Nov 2016 04:52:39 GMT

The transaction command creates multi-valued fields for service layer and elapsed time, but no particular ordering is guaranteed across the fields. To achieve your table, I would do this instead - which is also much faster:

index=index  MessageIdentifier=*
|stats earliest(_time) as Time list(ElapsedTime) as ElapsedTime  
by InterfaceName BusinessIdentifier MessageIdentifier ServiceLayerName

If this doesn't look the way you like, please comment.

Re: Fields value correlation not working

hemendralodhi — Mon, 21 Nov 2016 08:13:24 GMT

Thanks Iguinn for your quick response.

I see the results are coming as correct but without transaction cmd earlier it was also coming. Of course it is fast now. I want to have grouping on Message Identifier as that is unique across all other fields too. When I run transaction on above query it is not grouping MI. Result is coming as three separate instance of MI(3 value) each having 2 elapsed time value like below

InterfaceName BusinessIdentifier MessageIdentifier ServiceLayerName Time ElapsedTime
Interface BI MI Service Layer 147816445700:00:00.085
00:00:00.091
I want to have something like in above screenshot where grouping can be done for multiple same value message identifier and it shows associated servicelayer name and Elapsed Time.

Thanks much.

Re: Fields value correlation not working

lguinn2 — Mon, 21 Nov 2016 12:38:54 GMT

You shouldn't be running transaction at all for your problem. It is slow and not a good fit.

If you like the earlier look with with group, then do this

index=index MessageIdentifier=*
|stats earliest(_time) as Time list(ServiceLayerName) as ServiceLayerName
list(ElapsedTime) as ElapsedTime

by InterfaceName BusinessIdentifier MessageIdentifier

Re: Fields value correlation not working

hemendralodhi — Tue, 22 Nov 2016 00:46:45 GMT

You are awesome!! It is working absolutely fine and super fast.

I read that transaction is for grouping similar value fields , thats why used it. I was not aware that we have other methods also to by pass it. Thanks much - upvoted.

Re: Fields value correlation not working

woodcock — Sat, 25 Mar 2017 06:24:30 GMT

Like this:

index=index MessageIdentifier= | stats min(_time) AS _time list(ServiceLayerName) AS ServiceLayerName list(ElapsedTime) AS ElapsedTime BY InterfaceName BusinessIdentifier MessageIdentifier
| table _time InterfaceName BusinessIdentifier MessageIdentifier ServiceLayerName ElapsedTime