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.
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
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
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?
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.
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 ?
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?
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
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.
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
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?
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"
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
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