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

[mikylace]

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?

[Runals · Accepted solution]

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.

[mikylace]

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!

[Runals]

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)

[NOUMSSI]

hi,
try this:

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

[mikylace]

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).

[stephanefotso]

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

[mikylace]

unfortunately this doesn't works 😞

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

[stephanefotso]

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

[mikylace]

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

[stephanefotso]

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