topic Re: How to get data from two indexes? in Getting Data In

How to get data from two indexes?

mpasha — Tue, 29 Sep 2020 21:22:48 GMT

Good day everyone,
i am dealing with an issue that i haven't been able to find an answer for so far. here is the problem:
I have two indexes collecting data; one index collects from DHCP which have Client_IP address that has been assigned to a machine and the other index is DNS which collects Clients internet queries. DNS index have the same "Client_IP" field. now i want to be able to take the Client_IP from the DNS search; find the hostname found in DHCP and create a table that includes time, Client_Name "from DHCP index" and Client_IP that matches the time of DNS query. DHCP data needs to have the closest time to the DNS query since DHCP can assign the same IP to a different client.
really appreciate any help with this issue.

Thanks,

Re: How to get data from two indexes?

harishalipaka — Tue, 29 Sep 2020 21:22:51 GMT

Hi @mpasha
If time and client_id have same value in both results than join with both fields.
Like |join time client_id

Or else join with only client_id

Re: How to get data from two indexes?

mpasha — Tue, 29 Sep 2020 21:22:53 GMT

here is the search based on your suggestion but it errors out!! I am pretty sure i am not using the proper syntax:
index=dnsa OR index=dhcp Query_Type!=12|join Client_IP|table _time Client_IP Client_name DNS_Query

Re: How to get data from two indexes?

harishalipaka — Tue, 25 Sep 2018 18:38:40 GMT

Hi @mpasha
Can you try like this.

index=dnsa  Query_Type!=12 |table Client_Ip ,xxx,yyy |join Client_IP [search index=dhcp Query_Type!=12 |table Client_Ip ,xxx,yyy]|table _time Client_IP Client_name DNS_Query

Re: How to get data from two indexes?

mpasha — Tue, 29 Sep 2020 21:22:59 GMT

it works if you manually search for a specific IP address like the following:

index=dnsa Query_Type!=12 Client_IP=172.24.9.245|join Client_IP [search index=dhcp Client_IP=172.24.9.245]|table _time Client_IP Client_Name DNS_Query

what i am looking for is something like a "lookup table" where the value of the client_IP is automatically picked and fed into the other search for the Client_Name value. the above search works perfectly if you are creating a form where you are searching for an IP and input the IP address manually!!
is this even possible?
by the way here is a sample output of the search for a certain IP. how can i format it so that the user and IP is listed once together with all DNS_Queries??

Re: How to get data from two indexes?

harishalipaka — Tue, 25 Sep 2018 19:14:35 GMT

@mpasha

Sorry I didn't get what do you want.
If my answer helped you please up vote or accept as answer.

Re: How to get data from two indexes?

mpasha — Tue, 25 Sep 2018 19:33:09 GMT

yes!! it partially answered my question.
Thanks so much for your help!!
Would love to see if there is a way to re-format the table to show the client IP address and client name once together with all DNS queries for the selected time frame.

Re: How to get data from two indexes?

harishalipaka — Tue, 25 Sep 2018 19:41:01 GMT

Hi @mpasha
Sorry up vote for answer not for comment.
Ok
What I understood filter by client ip and client name.

Add end of your query.

|Where client_,ip=xxxx and client_name="xxx"

Re: How to get data from two indexes?

harishalipaka — Tue, 25 Sep 2018 19:53:19 GMT

Hi @mpasha
Did you get your answer.

Re: How to get data from two indexes?

mpasha — Tue, 25 Sep 2018 19:55:10 GMT

i did and have already did like the answer and accept it.
am i missing something?