topic Re: Join/Subsearch - unsure which to use and why in Splunk Search

Join/Subsearch - unsure which to use and why

jb123213123 — Mon, 01 Mar 2021 14:16:30 GMT

My dataset is in a rather strange format. For a given 'event', I have numerous splunk entries all linked by a 'session_id'.

Within those sessions, there exist 2 fields I care about: name, request

But these all exist independently on one of the numerous entries for a given session_id. For example, we could have 5 events for one session:

{session_id:15, time: 1600, offset:1}

{session_id:15, time: 1605, name:bob}

{session_id:15, time: 1607, type: sandwich}

{session_id:15, time: 1608, type: drink}

{session_id:15, time: 1610, offset: 2}

The first question is, for a given name - bob here - how do I do a search that would do the following:

Find all events with bob, count how many sandwich orders there were, count how many drink orders there were.

My attempt was to enumerate all session_ids for a given name, so for bob there could be session_ids of 5,7,10,15

Then for each session_id, do a subsearch specifying the session_id, and attempt to count the number of type:sandwich and type:drink fields there were. I couldn't work out how to get this to return to the main query in a good format.

Re: Join/Subsearch - unsure which to use and why

gcusello — Mon, 01 Mar 2021 14:25:05 GMT

Hi @jb123213123,

if you want the number of session_ids, type=sandwich and type=drink for each name it's easy and it's possible to have with only one command:

your_search | stats dc(session_id) AS dc_session_id count(eval(type="sandwich")) AS n_sandwich count(eval(type="drink")) AS n_drink BY name

if you want the details for each session_id is more complicate.

Do you need it?

Ciao.

Giuseppe

Re: Join/Subsearch - unsure which to use and why

jb123213123 — Mon, 01 Mar 2021 14:34:06 GMT

Hi Giuseppe,

Thanks for the quick response. That query definitely looks like its in the right direction, but is not showing any count for n_sandwich or n_drink.

It does seem to be counting the number of session_ids for each name though - just 0's for the other two events.

Any ideas?

Re: Join/Subsearch - unsure which to use and why

gcusello — Mon, 01 Mar 2021 14:39:44 GMT

Hi @jb123213123,

my search runs if the "type" field is already extracted, if not, you have to create a field extraction for it

| rex "type:\s+(?<type>\w+)\}"

to add to your search before the stats command.

Ciao.

Giuseppe

Re: Join/Subsearch - unsure which to use and why

jb123213123 — Mon, 01 Mar 2021 14:51:13 GMT

Hi:

Update - this is now working for one of the types but not the other. This is strange, however in the data i have noticed that "name" and "type: sandwich" are only ever present in the same single event. another single event (for each session_id) contains the type:drink.

I think this is causing issues as it cant count drink if it is looking for the name?

Re: Join/Subsearch - unsure which to use and why

gcusello — Mon, 01 Mar 2021 14:52:04 GMT

Hi @jb123213123,

Yes.

Ciao.

Giuseppe

Re: Join/Subsearch - unsure which to use and why

jb123213123 — Mon, 01 Mar 2021 14:56:47 GMT

How do I get around this? I can't edit the data.

If i search on session_id, I could presumably count if the drinks field is present - can this be done through a subsearch into the main search?

Thanks very much for your help.

Re: Join/Subsearch - unsure which to use and why

gcusello — Mon, 01 Mar 2021 15:07:53 GMT

Hi @jb123213123,

forget subsearches, Splunk isn't a procedural language!

You have to extract all the fields you need (in this case type).

So type could contain "sandwich" or "drink" and you count the occurrence of "sandwich" and "drink" using the count(eval(type="xxxx").

You can count in the same stats command both sandwich and drink.

I hope to be clear, otherwise, please share an example of your logs.

Ciao.

Giuseppe