Compare data in different souretypes with no common field

[kashifqau]

I am having below situation

• I am having 2 different sourcetypes "logs" and "range".
  • logs contains log events which are having a field with name "num"
  • range contains 2 different fields with names "lowerlimit" and "upperlimit"
  • I have to create a search to get the "num" field from sourcetype "logs" and compare it in sourcetype(range) and display the lowerlimit and upperlimit for which num>=lowerlimit AND num<=upperlimit

I created a main search to get "lowerlimit" and "upperlimit" and a subsearch to get "num", however after that I do not know how to perform the comparison.

[I am having no common field among both these searches]

Thank you and looking forward for a solution.

[nikita_p]

Hi @kashifqau,
Can you try below query if it helps you.
index=xyz sourcetype=logs AND sourcetype=range| stats count BY num,range |search num>=lowerlimit AND num<=upperlimit

[kashifqau]

Thank you nikita_p for your reply.

Sorry to say that provided search is not producing desire. stats count by num, range returns no result

[nikita_p]

Can you please try using OR:-
sourcetype=logs OR sourcetype=range

[ddrillic]

-- [I am having no common field among both these searches]

For such a case, you can use -

 eval combined_field = coalesce(fielda, fieldb)

[MonkeyK]

Kashifqau, if you already have used a subsearch that gives you "num", the way to compare fields is the "where" command,
so
| where num>=lowerlimit AND num<=upperlimit

Not sure why Splunk needs where separate from search, but where is what lets you compare fields in the same record.

[kashifqau]

Thank you for replies

@ddrillic , the coalesce function combines the 2 fields into a single one. In any case I have to make a condition between values of 2 different sourcetypes, which yields in no result. I am trying further with coalesce but as of now I didn't succeeded in it

@MonkeyK, my issue is that i have to make a condition between fields in 2 different sourcetypes. In this case a normal where clause does not work because we are having data in below format

num lowerlimit upperlimit
100

80 110
40 60
310 400

and so on. In this case

[MonkeyK]

I see, so you have a subsearch that can get "num" from logs, but it does not include that value in the records from range.

There are a few ways to do this, including using this:
use your current search and eventstats to get the value that you want

base search
| eventstats first(num) as num
| where num>=lowerlimit AND num<=upperlimit