Getting Data In

How to get data from two indexes?

mpasha
Path Finder

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,

0 Karma
1 Solution

harishalipaka
Builder

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

View solution in original post

mpasha
Path Finder

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??
alt text

0 Karma

harishalipaka
Builder

@mpasha

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

mpasha
Path Finder

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.

0 Karma

harishalipaka
Builder

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"

harishalipaka
Builder

Hi @mpasha
Did you get your answer.

0 Karma

mpasha
Path Finder

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

0 Karma

harishalipaka
Builder

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

View solution in original post

harishalipaka
Builder

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

0 Karma

mpasha
Path Finder

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

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!