Join two indexes with two different time ranges

[vrmandadi]

I am trying to join two indexes through a common field but has a different name in the indexes and want to run in different time ranges .The common field is the IPAddress which is ipv4 in search1 and IP in search2 .What is the fast approach for the search to run , is it by joining the indexes or using the search1 as inputlookup . Below are the searches

Search 1 - [timerange - previous month]
index=main sourcetype=rf Severity=High| rename IP as ipv4

Search 2 -[timerange - 24 hrs]

index=xyz

Below is the join I am using.I am trying to match the IP and output the host_name , platform ,from search 2 .the timerange is previous month but the search 2 should run for last 24hrs

index=main sourcetype=rf Severity=High earliest=-1mon@mon latest=@m
| rename IP as ipv4
| join ipv4 [search index=xyz earliest=-24hrs ].
| eval description="The host : " . host_name. " with Source IP: ". ipv4. " has Severity:" .Severity. " Solution:" .Insight. " WITH VDM:" . Method." AT: ". time."with OS Type :" .platform
| table description

This resulted in no results as the search in join has no data in previous month

Thanks in advance

[gcusello]

Hi vrmandadi,
join isn't a good command because it's very slow.
So you could try something like this:

(index=main sourcetype=rf Severity=High earliest=-2mon@mon latest=-mon@mon) OR (index=xyz earliest=-24h@h latest=now)
| eval ipv4=coalesce(IP,ipv4)
| stats values(host_name) AS host_name values(Severity) AS Severity values(insight) AS insight values(Method) AS Method earliest(_time) AS _time values(platform) AS platform BY ipv4
| eval description="The host : ".host_name." with Source IP: ". ipv4." has Severity:".Severity." Solution:".Insight." WITH VDM:". Method." AT: ". _time."with OS Type :" .platform
| table description

Beware to the field names.
Bye.
Giuseppe

[vrmandadi]

Hello @gcusello
Thank you for your response . How can I use the search 2 results as an inputlookup?.

The search 2 which is index=xyz |stats count by host_name ipv4 platform |fields host_name ipv4 platform |outputlookup mylookup.csv

I am using the above search results into the main query like below
index=main sourcetype=rf Severity=High
| rename IP as ipv4
| join ipv4
[| inputlookup mylookup.csv
| fields ipv4 IP host_name platform]
| rename "Detection Method" as "Detection_Method"
| rename " Insight" as "V_Insight"
| rename "Product Result" as "Product_Result"
| rename "Software OS" as "Software_OS"
| replace "" WITH "abc" IN "Software_OS"
| replace "" WITH "xyz" IN "V_Insight"
| replace "" WITH "abc" IN "Product_Result"
| eval time=strftime(_time,"%d/%m/%Y %H:%M:%S")
| eval node=abc
| eval resource="Vulnerability"
| eval type="Vulnerability"
| eval severity=1
| eval description="The host : " . host_name. " with Source IP: ". ipv4. " has Severity:" .Severity. " Solution:" .V_Insight. " WITH VDM:" . Detection_Method." AT: ". time."with OS Type :" .platform
| table description

but the issue is that when I run the search for previous month time range I see no results as the lookup does not have any data in the last month

[gcusello]

Hi vrmandadi,
instead | inputlookup use the lookup command that is like a join command:

index=main sourcetype=rf Severity=High
| rename IP as ipv4 
| lookup mylookup.csv ipv4 OUTPUT IP host_name platform 
| rename "Detection Method" as "Detection_Method" 
| rename " Insight" as "V_Insight" 
| rename "Product Result" as "Product_Result" 
| rename "Software OS" as "Software_OS" 
| replace "" WITH "abc" IN "Software_OS" 
| replace "" WITH "xyz" IN "V_Insight" 
| replace "" WITH "abc" IN "Product_Result" 
| eval time=strftime(_time,"%d/%m/%Y %H:%M:%S") 
| eval node=abc
| eval resource="Vulnerability" 
| eval type="Vulnerability" 
| eval severity=1 
| eval description="The host : " . host_name. " with Source IP: ". ipv4. " has Severity:" .Severity. " Solution:" .V_Insight. " WITH VDM:" . Detection_Method." AT: ". time."with OS Type :" .platform
| table description

Bye.
Giuseppe

[vrmandadi]

Hello Giuseppe ,

The lookup file I am using is coming from the search below
index=xyz |stats count by host_name ipv4 platform |fields host_name ipv4 platform |outputlookup mylookup.csv

How to schedule this search to run everyday and use this results as lookup file .the search runs for previous month and the lookup should be the latest run of the search

[gcusello]

Hi vrmandadi,
ok, anyway there's no sense to use join and inputlookup because the lookup command is a special join!
So if in your lookup there are three fields (host_name ipv4 platform) you have to adapt my search:

 index=main sourcetype=rf Severity=High
 | rename IP as ipv4 
 | lookup mylookup.csv ipv4 OUTPUT host_name platform 
 | rename "Detection Method" as "Detection_Method" 
 | rename " Insight" as "V_Insight" 
 | rename "Product Result" as "Product_Result" 
 | rename "Software OS" as "Software_OS" 
 | replace "" WITH "abc" IN "Software_OS" 
 | replace "" WITH "xyz" IN "V_Insight" 
 | replace "" WITH "abc" IN "Product_Result" 
 | eval time=strftime(_time,"%d/%m/%Y %H:%M:%S") 
 | eval node=abc
 | eval resource="Vulnerability" 
 | eval type="Vulnerability" 
 | eval severity=1 
 | eval description="The host : " . host_name. " with Source IP: ". ipv4. " has Severity:" .Severity. " Solution:" .V_Insight. " WITH VDM:" . Detection_Method." AT: ". time."with OS Type :" .platform
 | table description

Bye.
Giuseppe

[gcusello]

Hi vrmandadi,
to schedure a search to insert values in a lookup, you have to create an alert and schedule it as you like.
I suggest to analyze also summary indexes, that could be useful for you.
Bye.
Giuseppe