topic How can I filter a transaction that contains multiple matches - and force a numeric sort? in Getting Data In

How can I filter a transaction that contains multiple matches - and force a numeric sort?

jasongb — Fri, 08 Sep 2017 17:48:05 GMT

I have used the 'transaction' command to isolate transactions that are made up of roughly 45 events each. I have a regex that identifies a TaskName and the TotalMilliseconds for each event, producing 45 matches for each transaction.

Questions:

When I try to filter the transaction (TotalMilliseconds>500, for example), the criteria is applied only against the first match. How can I ensure ALL matches are considered when filtering?

How can in insert a 'tab' character when formatting a concatenated field value? TotalMilliseconds."\t".TaskName and its derivatives do not work.

How can I filter the results to show only matches where TotalMilliseconds<500 (for example)? Any attempt I've made so far has only applied the filter to the FIRST match in my list of 45 values.

Is there any way to force a numeric sort on a string field?

Thanks for looking!

Appendix:

Query:

base search
|rex "rex to find ClientID"
|rex "long rex that finds TaskName and TotalMilliseconds"
|transaction field1 field2 maxspan=5m unifyends=true startswith="beginning" endswith="ending"
|search [[[or, 'where']]] TotalMilliseconds>500 <--neither meets my needs

Results:

ClientID TimeAndTaskName
abc123 1127 (UseCaseA)
12 (UseCaseB)
21 (UseCaseY)

Goal (filtering TotalMilliseconds>20):

ClientID TimeAndTaskName
abc123 21 UseCaseY
1127 UseCaseA

Re: How can I filter a transaction that contains multiple matches - and force a numeric sort?

alemarzu — Fri, 08 Sep 2017 17:59:24 GMT

I'm not quite sure what you need, but try like this.

... | transaction ... | mvexpand TotalMilliseconds | where match(field1, field2) AND TotalMilliseconds > 500

Re: How can I filter a transaction that contains multiple matches - and force a numeric sort?

DalJeanis — Fri, 08 Sep 2017 19:47:36 GMT

That depends on what you are trying to achieve.

| streamstats count as recno
| appendpipe 
     [ 
    | mvexpand TotalMilliseconds
    | stats sum(TotalMilliseconds) as GrandTotalMilliseconds max(TotalMilliseconds) as maxTotalMilliseconds by recno
    | eval deleteme="deleteme"
    ]
| eventstats 
       max(GrandTotalMilliseconds) as GrandTotalMilliseconds 
       max(maxTotalMilliseconds) as maxTotalMilliseconds by recno
 | where isnull(deleteme)

After those lines run, on each transaction you will have the grand total of all TotalMilliseconds values, and also the largest of all TotalMilliseconds values for that record. Test those against your requirements and go from there.

tab value - if you have splunk 6.6, then try printf.

| eval myfield="firstthing".printf("%c",9)."secondthing"

I can't swear whether having a tab there will do anything useful anyway, because my experience with splunk says the output is pretty basic.

Re: How can I filter a transaction that contains multiple matches - and force a numeric sort?

jasongb — Tue, 12 Sep 2017 03:45:40 GMT

Thanks to you both for your patient help; I see now how bad my original post's formatting is. This makes my question difficult to understand.

I've done some work and discovered a couple of things: first, it's difficult to sort numerically and not lexicographical(?) in Splunk. I've tried several means of doing so, with no success at all. I'm a little shocked that it has to be that difficult, honestly... Second, it's difficult to filter multi-value fields like mine.

I had hoped to filter the results of the multi-value field descending - I've abandoned that. I did discover that (thanks only to the format of the numbers) I can filter the TotalMilliseconds using a regex that matches only numbers with 4 digits or more. It's 'good enough'.

I'm very interested in making DalJeanis' solution work! I can understand almost all of it, save the 'deleteme' functionality.... but it currently only produces a blank column in my results. And I'm on the cloud, which means 6.5.12, and no 'tab' functionality.