topic Re: Help me with search query for my usecase in Splunk Search

Help me with search query for my usecase

sravankaripe — Tue, 29 Sep 2020 13:28:02 GMT

i have two id's lets say ID1 and ID2

i want to use transaction command for both ID1 and ID2 in same query , please help me with this.l

i have more than two events those events contains some id's like

event 1

TRANSACTION_ID=101

event 2

REQUEST_ID=101 TRANSACTION_ID=102

event 3

TRANSACTION_ID=101 REQUEST_ID=105

i want to combine all 3 events event1,event2 and event3 into single event by value 101.

Re: Help me with search query for my usecase

cmerriman — Tue, 28 Mar 2017 14:35:19 GMT

can you give a bit more detail in what you're trying to accomplish and data examples or fields?

Re: Help me with search query for my usecase

somesoni2 — Tue, 28 Mar 2017 14:42:43 GMT

Without much information, I would say you can create a common fields using eval like | eval commonID=coalesce(ID1,ID2) and use it in transaction. Again, transactions are expensive and there might be alternatives, so if you add more information, we may be able to provide better suggestions.

Re: Help me with search query for my usecase

woodcock — Tue, 28 Mar 2017 14:42:51 GMT

You should avoid the use of transaction whenever possible. You have not given us much information to go on but maybe this:

... | stats values(*) AS * BY ID

Re: Help me with search query for my usecase

DalJeanis — Tue, 28 Mar 2017 15:07:00 GMT

Are you combining the two transactions because you are linking the TRANSACTION_ID from the first event to the REQUEST_ID from the second?

If so, are you trying to also connect up a third transaction like this if it exists REQUEST_ID=102 TRANSACTION_ID=105?

What are the actual data fields involved that you will be working with?

Re: Help me with search query for my usecase

sravankaripe — Tue, 28 Mar 2017 15:08:15 GMT

i want to connect both by value 101

Re: Help me with search query for my usecase

sravankaripe — Tue, 28 Mar 2017 15:08:52 GMT

and combine both

Re: Help me with search query for my usecase

somesoni2 — Tue, 28 Mar 2017 15:37:13 GMT

You should be able to combine all three events using something like this

your base search 
| eval ID=split(coalesce(REQUEST_ID,"#")."#".TRANSACTION_ID,"#")
| mvexpand ID
| stats values(fieldthatyouwant1) as fieldthatyouwant1... by ID

Re: Help me with search query for my usecase

sideview — Tue, 29 Sep 2020 13:28:10 GMT

I'm making an assumption here that the sourcetype is always distinct in all 3 of your event examples. For example that "event 2" is always of sourcetype "sourcetype2". And event1 is sourcetype1 etc. If that assumption isn't true but is true of some other field, you'll have to modify this example a bit.

<your search terms>
| eval normalizedId=if(sourcetype="sourcetype2",REQUEST_ID,TRANSACTION_ID)
| fields normalizedId, TRANSACTION_ID someField anotherField yetAnotherField
| stats list(*) as * by normalizedId

what this will do, is roll up all the fields that you see in the fields line, by ID. And for the ID it will pick the REQUEST_ID field value within event2, but the TRANSACTION_ID value for all other events.

some other quick best practice notes:
- Avoid doing list() as * without something before to limit the fields. Here i've used a fields clause, but anything that serves to limit the fields is fine. Sending unconstrained lists into list() as * can blow up memory usage if there are tons of fields (100, 200), often for no gain.
- If the conditional logic to normalize the id is more complex, you often have to switch to the case() function in eval instead of if() The full list of functions in eval is here - https://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/CommonEvalFunctions it's worth at least skimming and bookmarking.
- transaction is probably not the way to go. For one thing it breaks map reduce (forcing all event rows to come back to the search head), for another it'll be slower, However if you really feel like you want to see the raw event text, note you can do list(_raw) as _raw. You may want to sneak areversecommand in before the stats if so to reorder the lines earliest first. (The astute reader will notice thatlist(_raw) as _raw` effectively brings back all the rows to the search head too. )

Re: Help me with search query for my usecase

woodcock — Tue, 28 Mar 2017 15:39:21 GMT

Maybe like this:

... | eval ID=coalesce(ID1, ID2) | stats values(*) AS * BY ID

Re: Help me with search query for my usecase

woodcock — Tue, 28 Mar 2017 20:35:45 GMT

Try this:

|makeresults | eval raw="1 2 3"
| makemv raw
| mvexpand raw
| eval TRANSACTION_ID=case(raw==1, "101", raw==2, "102", raw==3, 101)
| eval REQUEST_ID=case(raw==2, "101", raw==3, 105)
| rename raw AS _raw
| fields - _time

| rename COMMENT AS "Everything above fakes sample data; everything below shows a guess at a solution"

| eval COMBINED_ID = if(isnull(REQUEST_ID), TRANSACTION_ID, TRANSACTION_ID . " " . REQUEST_ID)
| makemv COMBINED_ID
| stats list(_raw) AS raws values(*) AS * BY COMBINED_ID