topic Re: Transaction with different values of the same field in Splunk Search

Transaction with different values of the same field

sieutruc — Mon, 13 Jun 2016 11:20:26 GMT

Hello,

I have the log like below :

Jun 13 10:18:59 Debug: IID 917966106 done
Jun 13 10:18:59 Debug: IID 917967047 action 2
Jun 13 10:18:59 Debug: IID 917967047 action 1
Jun 13 10:18:58 Debug: IID 917966106 rewritten to IID 917967047 by engine1
Jun 13 10:18:58 Debug: IID 917966106 action 2
Jun 13 10:18:58 Debug: IID 917966106 action 1
Jun 13 10:18:58 Debug: IID 917966106 start
Jun 13 10:18:56 Debug: IID 817966106 done
Jun 13 10:18:56 Debug: IID 817966106 action 2
Jun 13 10:18:56 Debug: IID 817966106 action 1
Jun 13 10:18:56 Debug: IID 817966106 start

I want to group into 2 transaction, normally i can use :

index=X | rex field=_raw "Debug: IID (?\d+)" | transaction IID startswith="start" endswith="done"

But the problem is for the second transaction, the field IID has 2 values ( 917966106 and 917967047 ) but they belong to the same transaction.

Can you know how to create a transaction in this case, one containing 4 events and other containing 7 events ?

i would appreciate any idea !

UPDATE after the good answer of @sundareshr : my log looks actually like

Jun 13 10:18:59 Debug: IID 917966106 done
Jun 13 10:18:56 Debug: RID 23789 stop
Jun 13 10:18:59 Debug: IID 917967047 action 2
Jun 13 10:18:59 Debug: IID 917967047 action 1
Jun 13 10:18:58 Debug: IID 917966106 rewritten to IID 917967047 by engine1
Jun 13 10:18:58 Debug: IID 917966106 action 2
Jun 13 10:18:58 Debug: IID 917966106 action 1
Jun 13 10:18:58 Debug: IID 917966106 start
Jun 13 10:18:58 Debug: RID 23789 IID 917966106 created
Jun 13 10:18:58 Debug: RID 23789 start details: start connection
Jun 13 10:18:56 Debug: IID 817966106 done
Jun 13 10:18:56 Debug: RID 12345 stop
Jun 13 10:18:56 Debug: IID 817966106 action 2
Jun 13 10:18:56 Debug: IID 817966106 action 1
Jun 13 10:18:56 Debug: RID 12345 IID 817966106 created
Jun 13 10:18:56 Debug: RID 12345 start details: start connection

when i tried

index=X | rex field=_raw "Debug: IID (?\d+)" | rex field=_raw "Debug: RID (?\d+)" |rex field=_raw "rewritten to IID (?\d+)" | eventstats first(newid) as newid) by IID | eval IID=if((isnull(newid), IID, newid) | transaction IID RID startswith="start" endswith="done"

It does not give the 2 transaction ( 6 events and 10 events)
Can you give me a help again pls ?

Re: Transaction with different values of the same field

Richfez — Mon, 13 Jun 2016 11:51:25 GMT

Is the problem that you have two IIDs so that messes things up?

Do you want to use the LAST IID or the FIRST IID? Basically, is the above list you wrote two transactions, one for 917966106 with 9 events and one for 917967047 with 3 events, or is it two transactions one for 917966106 with 10 events and one for 917967047 with 2 events (i.e. does the line with two get included in the ...106 transaction or the ...047 transaction?

Re: Transaction with different values of the same field

sieutruc — Mon, 13 Jun 2016 12:06:02 GMT

one transaction is 917966106 and 917967047 , and other one is 817966106. 917967047 is rewritten from 917966106 but both are in the same transaction. So first transaction contain 4 events and other contains 7 events.

Re: Transaction with different values of the same field

sundareshr — Mon, 13 Jun 2016 13:09:52 GMT

Try this

 index=X | rex field=_raw "Debug: IID (?<IID>\d+)" |rex field=_raw "rewritten to IID (?<newId>\d+)" | eventstats first(newid) as newid) by IID | eval IID=if((isnull(newid), IID, newid) | transaction IID startswith="start" endswith="done"

Re: Transaction with different values of the same field

sieutruc — Mon, 13 Jun 2016 13:59:14 GMT

Thank you for the answer, i have updated my question, and with my current log, your query does not return 2 transactions. Can you take a quick look ?

Re: Transaction with different values of the same field

sundareshr — Mon, 13 Jun 2016 15:01:44 GMT

Try this

| rex "RID\s?(?<rid>\d+)"  | rex max_match=2 "IID\s?(?<iid>\d+)" | rex "(?<action>start|done)" | eval newid=mvindex(iid, 1) | eval iid=mvindex(iid, 0) | eventstats first(newid) as newids by iid | eval iids=if(isnull(newids), iid, newids) | eventstats first(iids) as iids by rid | transaction iids

Re: Transaction with different values of the same field

woodcock — Tue, 14 Jun 2016 15:31:00 GMT

Try this:

 index=X | rex field=_raw "Debug: IID (?<origIID>\d+)(?:\s+rewritten to IID (?<newIID>\d+))?"
| eval newIID=coalesce(newIID, origIID)
| eventstats latest(newIID) AS finalIID BY origIID
| transaction finalIID

BTW, transaction is a very expensive way to do this, I would swap the last line above with this one:

| stats list(_time)  list(_raw) BY finalIID

Or maybe even this:

| stats values(*) AS * values(_*) AS _* BY finalIID