topic Re: How do I edit my transaction search to only return on unique time result? in Splunk Search

How do I edit my transaction search to only return on unique time result?

mikylace — Tue, 30 Jun 2015 11:00:47 GMT

I'm trying to adjust the following search:

index=pcindex sourcetype=parlayx | transaction corr | search "lvl=ERROR" | table SMS_MSISDN,corr,time

I use the transaction command in order to obtain one single meta-trace with common fields I'm interested in. Then, I filter only for the erroneous ones, and finally, showing results in a "table" format (with phone number, correlatorID, time).
The problem is that the meta-field that the transaction command creates contains more than one "time" (one for every trace).

57300xxxxxxx    09c3d697-d1d1-479c-bfef-839f874460f0     2015-06-30T03:47:10.618
                                                         2015-06-30T03:47:10.620
                                                         2015-06-30T03:47:10.621
                                                         2015-06-30T03:47:40.621

How can I get only one time result, or an average of them at least?

Re: How do I edit my transaction search to only return on unique time result?

stephanefotso — Tue, 30 Jun 2015 11:16:48 GMT

Hello! try this to get the last time value :

index=pcindex sourcetype=parlayx| transaction corr | search "lvl=ERROR"|stats values(SMS_MSISDN) values(corr) first(time)

You can also try other functions, last(), max(), ...
Thanks

Re: How do I edit my transaction search to only return on unique time result?

mikylace — Tue, 30 Jun 2015 11:44:29 GMT

unfortunately this doesn't works 😞

it returns a different number of msisdn, more correlatorID than phonenumbers, and just one time (the first one)...

Re: How do I edit my transaction search to only return on unique time result?

NOUMSSI — Tue, 30 Jun 2015 11:50:41 GMT

hi,
try this:

index=pcindex sourcetype=parlayx | transaction corr | search "lvl=ERROR" |dedup SMS_MSISDN| table SMS_MSISDN,corr,time

Re: How do I edit my transaction search to only return on unique time result?

stephanefotso — Tue, 30 Jun 2015 12:05:11 GMT

True. try this

 index=pcindex sourcetype=parlayx|eventstats max(time) as time| transaction corr | search "lvl=ERROR"|table SMS_MSISDN corr time

Hope, it may help

Re: How do I edit my transaction search to only return on unique time result?

mikylace — Tue, 30 Jun 2015 13:00:28 GMT

thankyou, unfortunately there are not msisdn duplicated, so the result is the same as before... Nor the time is duplicated, all of them are different (by seconds or milliseconds, but different).

Re: How do I edit my transaction search to only return on unique time result?

mikylace — Tue, 30 Jun 2015 13:02:25 GMT

thankyou a lot, time is shown correctly but is always the same for all phonenumbers, like this:

573155737677 15ab891d-b075-4894-a2fa-4dcefc93ab77 2015-06-30T15:00:40.940
573157464749 3d17e720-6810-47be-b94f-0b66a4c97081 2015-06-30T15:00:40.940
573213437139 29245338-6763-4969-bbb2-53972bf6e004 2015-06-30T15:00:40.940
573008181388 09c3d697-d1d1-479c-bfef-839f874460f0 2015-06-30T15:00:40.940

Re: How do I edit my transaction search to only return on unique time result?

stephanefotso — Tue, 30 Jun 2015 13:44:40 GMT

Yes! Because i have used the max() command, means, 2015-06-30T15:00:40.940 is the max time.
But you can also use a subsearch to get the top time, something like this:

 index=pcindex sourcetype=parlayx [search index=pcindex sourcetype=parlayx|top 1 time|table time]|transaction corr | search "lvl=ERROR"|table SMS_MSISDN corr time

Re: How do I edit my transaction search to only return on unique time result?

Runals — Tue, 30 Jun 2015 14:02:12 GMT

In your search when you list "time" at the end in your table is that a field IN your data or are you talking about the "_time" field Splunk uses to list the time of the event. For the transaction command _time will list the first event of any events that are combined.

Re: How do I edit my transaction search to only return on unique time result?

mikylace — Mon, 28 Sep 2020 20:26:43 GMT

I think this is what I call "Columbus egg"....

THANKYOU! :DDD

index=pcindex sourcetype=parlayx | transaction corr | search "lvl=ERROR" | table _time, SMS_MSISDN, corr

Works Just Perfect 🙂
Thankyou so much!

Re: How do I edit my transaction search to only return on unique time result?

Runals — Mon, 28 Sep 2020 20:25:28 GMT

Glad that worked. If you haven't already seen it the transaction command also will calculate the duration between the first and last event in the transaction and put it into a field called duration. This is useful for figuring out long something took between start and end as well as being able to calculate the end time ie - | eval end_time = _time + duration | convert ctime(end_time)