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
_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.
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
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.
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
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.
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
|stats earliest(_time) as Time list(ServiceLayerName) as ServiceLayerName
list(ElapsedTime) as ElapsedTime
by InterfaceName BusinessIdentifier MessageIdentifier
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.