Splunk Search

Why is the subsearch truncating when using JOIN in the query?

vrmandadi
Builder

I am joining two queries by a common field but the problem is that the subsearch is truncating is there a better way to write the query without using join?

Query:

index=abc host=xyz| table session |rename session as session_id | join  session_id  [search index=aaa  |table session_id,System]

I am joining by common field called session_id and output should have the session id and System.

0 Karma
1 Solution

elliotproebstel
Champion

Give this a try:

(index=abc host=xyz) OR index=aaa 
| eval session_id=coalesce(session, session_id)
| stats values(System) AS System BY session_id

View solution in original post

elliotproebstel
Champion

Give this a try:

(index=abc host=xyz) OR index=aaa 
| eval session_id=coalesce(session, session_id)
| stats values(System) AS System BY session_id

View solution in original post

vrmandadi
Builder

Thank you for the quick response @elliotproebstel

What does the coalesce function do ,does it compare the session with the session id and output only those which are matching?

0 Karma

elliotproebstel
Champion

The coalesce function will take the first non-null value it encounters in the fields listed as arguments. For the example above, it will look at each event and see if the event has a field called session defined. If it does, it moves that value into the field called session_id. If the event does not have a field called session, then it looks for a field called session_id. If it finds that, it "moves" the value into session_id (but since that's the same one it started from, there's actually no move here). In this case, I assume your events have EITHER a value for session or a value for session_id but never both. If they might have both and you'd like one to take precedence, you'd list that field first. The coalesce function can take an arbitrary number of fields as arguments and follow the logic described here to assign the first non-null value to the specified field.

0 Karma

vrmandadi
Builder

Thank you for the explanation.Can we do the same with count the session id like

index=abc identity=adm |stats count(session)

index=abc identity!=adm |stats count(session)

I am trying to calculate the count of session id by identity=adm and count of session without the identity with coalease ?

0 Karma

elliotproebstel
Champion

You want the result to be the number of unique values for session in events where identity=adm and also the number of unique values for session in events where identity!=adm? Is that right?

0 Karma

vrmandadi
Builder

I tried something like this

(index=abc identity=) OR (index=mbo host=*ads)
| eval identity_count=if(identity=="adm","good","bad")
| eval session_id=coalesce(session, session_id,identity_count)
| stats count by identity_count session_id System

Is this the correct query,

I am trying to get a output in a tabular form with System,good,bad as columns

Eg:
system | good | bad
ALB 2000 1000
CLT 4000 3000

0 Karma

elliotproebstel
Champion

In your final results, you don't seem to be displaying the session_id at all. Is the "good" count a count of events that contained identity="adm" or a count of unique session_id values that were found in events with identity="adm"? Unless every event has a different session_id value, those counts will be different.

0 Karma

vrmandadi
Builder

so I am calculating the good count based on identity=adm and count of bad which are identity!=adm and yes the session_id is unique

0 Karma

vrmandadi
Builder

Sorry but one clarification ,the search you have given

(index=abc host=xyz) OR index=aaa
| eval session_id=coalesce(session, session_id)
| stats values(System) AS System BY session_id

does this search matches the session in index=abc and session_id in index=aaa and outputs only those which are matching along with system?

0 Karma

niketnilay
Legend

Try the following search filter for events that match both indexes:

(index=abc host=xyz) OR index=aaa 
| eval session_id=coalesce(session, session_id)
| stats count as eventCount values(index) as index values(System) AS System BY session_id
| search eventCount>1 AND index="abc" AND index="aaa"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

vrmandadi
Builder

@niketnilay

Thanks for the reply,It seems to work but can I Add one more field into the search,i tried adding it in the coalesce command ..is this the correct way?

(index=abc identity=adm) OR (index=aaa )
| eval session_id=coalesce(session, session_id,identity)
| stats count as eventCount values(index) as index values(System) AS System BY session_id
| search eventCount>1 AND index="abc" AND index="aaa" |fields- eventCount

0 Karma

vrmandadi
Builder

I tried something like this

(index=abc identity=) OR (index=xyz) | eval identity_count=if(identity=="TWC_ADM_adm"," good_identity","bad_identity")
| eval session_id=coalesce(session, session_id,identity_count) | search identity_count=
|chart count over system by identity_count

0 Karma