Transaction starting and ending 'event' are not always showing the correct overview

[Sentira]

Hi,

I am trying to create a Transaction where my starting and ending 'event' are not always showing the correct overview.  I expect the yellow marked group events as result:

 

index=app sourcetype=prd_wcs host=EULMFCP1WVND121 "EquipmentStatusRequest\"=" D0022
| eval _raw = replace(_raw, "\\\\", "")
| eval _raw = replace(_raw, "\"", "")
| rex "Chute:DTT_S01.DA01.(?<Door>[^\,]+)"
| rex "EquipmentName:DTT_S01.DA01.(?<EquipmentName>[^\,]+)"
| rex "EquipmentType:(?<EquipmentType>[^\,]+)"
| rex "Status:(?<EquipmentStatus>[^\,]+)"
| rex "TypeOfMessage:(?<TypeOfMessage>[^\}]+)"
| eval Code = EquipmentStatus+"-"+TypeOfMessage+"-"+EquipmentType
| lookup Cortez_SS_Reasons.csv CODE as Code output STATE as ReasonCode
| where ReasonCode = "Ready" OR ReasonCode = "Full"
| transaction EquipmentName startswith=(ReasonCode="Full") endswith=(ReasonCode="Ready")  
| eval latestTS = _time + duration
| eval counter=1
| accum counter as Row
| table _time latestTS Row  ReasonCode
| eval latestTS=strftime(latestTS,"%Y-%m-%d %H:%M:%S.%3N")

The script above is showing the following overview as result and the marked line is not correct. I don't know how this is happened. Because, I expect that Transaction function will always take first events starting with "Ready" and ending with "Full".. 

Thanks in advance.

[Sentira]

Thanks, this script gives only 3 rows. But, I want to have an overview like (TS: Timestamp of the event):

[ITWhisperer]

Try something like this

| streamstats count by ReasonCode EquipmentName reset_on_change=t global=f
| where count=1

[PickleRick]

OK. So you want to have a "transaction" consisting of any sequence of Full events ending with a single Ready event. Any Ready events not preceeded by a Full event are not a part of any transaction and should be discarded?

| streamstats current=f window=1 values(ReasonCode) as LastReasonCode
| where ReasonCode="Full" OR LastReasonCode="Full" OR isnull(LastReasonCode)

This should filter out the events which are Ready and are preceeded by Ready.

Now we can mark beginnings of each of those "streaks"

| eval bump=if(ReasonCode="Full" AND LastReasonCode="Ready",1,0)

And we can find which transaction is which

| streamstats current=t sum(bump) as tran_id

Now you have your unique transaction ID which you can use to find first and last timestamp

| stats min(_time) as earliest max(_time) as latest by tran_is
| eval duration=latest-earliest

[Sentira]

Unfortunately your script does not provide the correct overview.

I want to know how long a machine has had a "Full" status. I can calculate that by taking the first "full" status and the "first" Ready status together and the difference is the duration.

for example:

Full -->This one
Full --> Skip
Ready -->This one
Full-->This one
Ready-->This one
Full-->This one
Full -->Skip
Ready -->This one
Ready --> skip

[PickleRick]

Your input data is definitely _not_ in the same order as shown in the opening post.

[ITWhisperer]

Transaction seems to have a mind of its own (there are some not well documented nuances to how it works). Try something like this before your transaction command (to give it a hand!)

| streamstats count(eval(ReasonCode="Full")) as fullCount count(eval(ReasonCode="Ready")) as readyCount by EquipmentName
| where fullCount=1 OR readyCount=1