How to edit my search to use transaction command to exclude values?

[ggiovan]

Hi, I have the following search that returns 10,552 events over a given period of time:

index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03")

Then I have second search using the "transaction" command that returns 664 events, in each of which there are two related events I want to exclude from the first search through SESSIONID:

 index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03") 
| transaction SESSIONID maxspan=2m startswith=(ACT_NAME="LOGON") endswith=(ACT_NAME="LOGOFF BY CLEANUP")
| where eventcount = 2

Can you help me to find a solution for this problem with only one search to return 9224 ( =10,552 - (664*2) ) events?
I have already used "search NOT [transaction ..]", "keepevicted = true" with "evicted = 1", append [ search..| transaction SESSIONID]...but don't work
I can not use a "inputlookup"
Thanks so much

[sravankaripe]

index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03") NOT [search index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03")
| transaction SESSIONID maxspan=2m startswith=(ACT_NAME="LOGON") endswith=(ACT_NAME="LOGOFF BY CLEANUP")
| where eventcount = 2 | return 1000 $SESSIONID]

[ggiovan]

Hello, I run this:

index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03")
NOT [search index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03")
| transaction SESSIONID maxspan=2m startswith=(ACT_NAME="LOGON") endswith=(ACT_NAME="LOGOFF BY CLEANUP") | where eventcount = 2 | return 1000 $SESSIONID]

Returns 8,240 events. Some values of second search (which must be excluded) are not excluded.
Please have other suggestions?
Thank you all.

[somesoni2]

Give this a try

index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03") NOT [search  index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03") 
 | transaction SESSIONID maxspan=2m startswith=(ACT_NAME="LOGON") endswith=(ACT_NAME="LOGOFF BY CLEANUP")
 | where eventcount = 2 | table SESSIONID] 

OR

 index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03") 
 | transaction SESSIONID maxspan=2m startswith=(ACT_NAME="LOGON") endswith=(ACT_NAME="LOGOFF BY CLEANUP") keeporphan=t 
 | where eventcount=1

[ggiovan]

Hello, I run this:

index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03")
NOT [search  index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03")
| transaction SESSIONID maxspan=2m startswith=(ACT_NAME="LOGON") endswith=(ACT_NAME="LOGOFF BY CLEANUP") | where eventcount = 2 | table SESSIONID]

Returns 8,242 events. Some values of second search (which must be excluded) are not excluded.
The second suggestion return 0 events.

[ggiovan]

Thank you all.

[ggiovan]

Hi, I have run the second suggestion with a change, now it's almost ok!

index=oracle
(INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03")
| transaction SESSIONID maxspan=2m startswith=ACT_NAME="LOGON")  endswith=ACT_NAME="LOGOFF BY CLEANUP" keeporphans=t
| where _txn_orphan=1

But now I can not use the "eventcount = 2" when I use the "keeporphans = t", the value "eventcount" is lost. Can you help me?
Thank you very much

[ggiovan]

The "eventcount is lost" means that if I write "...| where _txn_orphan=1 AND eventcount=2" it doesn't work, return 0 events. If I show the eventcount with "...| table eventcount " with "keeporphans=t" the output is null in every record.
Thanks

[somesoni2]

When the _txn_orphan is 1, means the transaction is not complete and you'll not get eventcount=2. Any specific reason you want to include that?

[ggiovan]

Hi, I need to exclude all the events that start with "LOGON" and end with "LOGOFF BY CLEANUP" and that they are exactly 2 in 2 minutes.
That's why I need "eventcount = 2".
Thank you very much

[ggiovan]

Have you other suggestions? Thank you!

[SplunkersRock]

try this

index=1st NOT [search index=2nd |return 15000 $sessionID]

[ggiovan]

Hello, I run this:

index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03")
NOT [search  index=oracle (INSTANCE_NAME="01" OR INSTANCE_NAME="02" OR INSTANCE_NAME="03")
| transaction SESSIONID maxspan=2m startswith=(ACT_NAME="LOGON") endswith=(ACT_NAME="LOGOFF BY CLEANUP") | where eventcount = 2 | return 1000 $SESSIONID]

Returns 8,240 events. Some values of second search (which must be excluded) are not excluded.

[ggiovan]

Have you other suggestions? Thank you!