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,
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
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??
@mpasha
Sorry I didn't get what do you want.
If my answer helped you please up vote or accept as answer.
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.
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"
Hi @mpasha
Did you get your answer.
i did and have already did like the answer and accept it.
am i missing something?
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
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
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