Splunk Search

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

ggiovan
Engager

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

0 Karma

sravankaripe
Communicator

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]

0 Karma

ggiovan
Engager

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.

0 Karma

somesoni2
Revered Legend

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
Engager

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.

0 Karma

ggiovan
Engager

Thank you all.

0 Karma

ggiovan
Engager

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

0 Karma

ggiovan
Engager

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

0 Karma

somesoni2
Revered Legend

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?

0 Karma

ggiovan
Engager

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

0 Karma

ggiovan
Engager

Have you other suggestions? Thank you!

0 Karma

SplunkersRock
Path Finder

try this

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

0 Karma

ggiovan
Engager

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.

0 Karma

ggiovan
Engager

Have you other suggestions? Thank you!

0 Karma
Get Updates on the Splunk Community!

Unlock New Opportunities with Splunk Education: Explore Our Latest Courses!

At Splunk Education, we’re dedicated to providing top-tier learning experiences that cater to every skill ...

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...