topic Re: Compare data in different souretypes with no common field in Splunk Search

Compare data in different souretypes with no common field

kashifqau — Mon, 25 Dec 2017 23:09:04 GMT

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.

Re: Compare data in different souretypes with no common field

ddrillic — Mon, 25 Dec 2017 23:30:03 GMT

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

For such a case, you can use -

 eval combined_field = coalesce(fielda, fieldb)

Re: Compare data in different souretypes with no common field

MonkeyK — Tue, 26 Dec 2017 02:11:34 GMT

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.

Re: Compare data in different souretypes with no common field

nikita_p — Tue, 26 Dec 2017 05:36:11 GMT

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

Re: Compare data in different souretypes with no common field

kashifqau — Tue, 26 Dec 2017 07:19:23 GMT

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

Re: Compare data in different souretypes with no common field

kashifqau — Tue, 26 Dec 2017 07:22:33 GMT

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

Re: Compare data in different souretypes with no common field

nikita_p — Tue, 26 Dec 2017 07:49:29 GMT

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

Re: Compare data in different souretypes with no common field

MonkeyK — Tue, 26 Dec 2017 21:12:43 GMT

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