topic Re: Join two indexes with two different time ranges in Splunk Search

Join two indexes with two different time ranges

vrmandadi — Mon, 29 Jul 2019 13:48:23 GMT

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

Re: Join two indexes with two different time ranges

gcusello — Mon, 29 Jul 2019 14:11:49 GMT

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

Re: Join two indexes with two different time ranges

vrmandadi — Wed, 30 Sep 2020 01:31:34 GMT

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

Re: Join two indexes with two different time ranges

gcusello — Mon, 29 Jul 2019 15:18:00 GMT

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

Re: Join two indexes with two different time ranges

vrmandadi — Wed, 30 Sep 2020 01:31:42 GMT

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

Re: Join two indexes with two different time ranges

gcusello — Tue, 30 Jul 2019 08:46:31 GMT

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

Re: Join two indexes with two different time ranges

gcusello — Wed, 31 Jul 2019 12:31:49 GMT

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