topic Re: How to edit my search to use transaction command to exclude values? in Splunk Search

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

ggiovan — Tue, 25 Apr 2017 15:38:07 GMT

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

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

SplunkersRock — Tue, 25 Apr 2017 16:05:47 GMT

try this

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

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

somesoni2 — Tue, 25 Apr 2017 16:08:10 GMT

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]

 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

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

sravankaripe — Tue, 29 Sep 2020 13:49:50 GMT

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]

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

ggiovan — Wed, 26 Apr 2017 09:36:33 GMT

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.

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

ggiovan — Wed, 26 Apr 2017 09:40:05 GMT

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.

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

ggiovan — Wed, 26 Apr 2017 09:42:40 GMT

Thank you all.

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

ggiovan — Wed, 26 Apr 2017 09:43:13 GMT

Have you other suggestions? Thank you!

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

ggiovan — Tue, 29 Sep 2020 13:50:32 GMT

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.

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

ggiovan — Wed, 26 Apr 2017 09:44:10 GMT

Have you other suggestions? Thank you!

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

ggiovan — Fri, 28 Apr 2017 11:56:51 GMT

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

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

ggiovan — Tue, 29 Sep 2020 13:52:32 GMT

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

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

somesoni2 — Tue, 29 Sep 2020 13:52:53 GMT

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?

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

ggiovan — Wed, 03 May 2017 14:57:20 GMT

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