topic Re: How to join data from two indexes in Splunk Search

How to join data from two indexes

poojak2579 — Mon, 17 Jul 2017 17:23:14 GMT

I want to get data from joining two indexes out of which one is summary index.
Summary Index has more than 500000 records
I have two fields Asset and Date in the summary index as well as in the other index.

I am planning to schedule a query that will check for any new asset in today's records and if it is a new, it will insert that record in the summary index.

I tried to do it by leftjoin but it works if I specify a particular value for the Asset.

Below is the query that works(ASSETNAME is the hard coded value)

index=I ASSETNAME earliest=-1d@d latest=now
|fields Asset Date
|join type=left Combo(index=summary ASSETNAME earliest=-1Y@Y latest=now|eval Asset1=Asset
|where isnull(Asset1)

The same query does not work if I remove the asset name and run it with all the records in the Summary Index.

It shows me null values in the column 'Asset1' for the assets that are there in the summary index.

I am not sure if it is because of the the limit of the records a subsearch can return.
Please suggest that if there is a better way of querying instead of using join.

I tried to do it by this way also but it is not showing me complete set of records.

(index=I earliest=-1d@d latest=now) OR (index=summary earliest=-1Y@Y latest=now)

Re: How to join data from two indexes

sbbadri — Mon, 17 Jul 2017 17:48:36 GMT

try this

1) (index=A OR index=B) rest of your query
2) index=A earliest=-1d@d latest=now | append [ search index=summary earliest=-1Y@Y latest=now]

Re: How to join data from two indexes

cmerriman — Mon, 17 Jul 2017 17:55:23 GMT

i would try this:

(index=I earliest=-1d@d latest=now) OR (index=summary earliest=-1Y@Y latest=now)|stats values(importantFieldFromIndex1) values(importantFieldFromSummaryIndex) by Asset Date

since both have Asset and Date fields, you should be returned with a lot of data from either index shared by the two common fields.

otherwise, if the join is the way you want to work it out, i'd try flipping them around, though i suggest trying to work through using the stats command, since join has limitations.

index=summary ASSETNAME earliest=-1Y@Y latest=now|eval Asset1=Asset
|where isnull(Asset1)|stats count by Asset Date
|join type=left Asset Date [index=I earliest=-1d@d latest=now
|fields Asset Date ]

Re: How to join data from two indexes

DalJeanis — Mon, 17 Jul 2017 18:27:21 GMT

Always mark your code as code (the button marked 101 010 for example) so that the web interface doesn't
strip out HTML-like constructs.
Your code as posted can't work, because the subsearch isn't in square braces.
According to the posted code, you are left-joining on a field named Combo, and we can't see what that is.
From your description, I don't know whether Date is a required part of the asset information, or whether Asset is a unique key. The right answer depends on that.
We don't know the format of the field Date. It may be significant to the solution.
Your final line as posted is correct. (I'd suggest using latest=@d for the index=I, but that's quibbling.) The next part after that is the processing that determines which ones need to be added.

This version assumes that Date1 is the Date field on the index=I record, that it is called Date2 on the summary record, and that Asset is a unique key regardless of the Date value.

(index=I Asset=* earliest=-1d@d latest=@d) OR (index=summary Asset=* earliest=-1Y@Y latest=now)
| eval NewDate = if(index=I, Date1, null())
| stats latest(NewDate) as Date2, count(eval(index="summary")) as SumExists by Asset
| where SumExists=0
| table Asset Date2

Re: How to join data from two indexes

poojak2579 — Tue, 29 Sep 2020 14:57:39 GMT

am sorry for the mistake, I wrote just part of the query .

Index=I has many other fields along with Asset and Date.

I want to get the Earliest Date when that asset was tracked.

Here is the query

|where isnull(combo1)

Re: How to join data from two indexes

poojak2579 — Mon, 17 Jul 2017 23:03:51 GMT

Thanks for the reply. I tried it with append also but results are truncated to maxout of 50000.

Re: How to join data from two indexes

poojak2579 — Mon, 17 Jul 2017 23:06:52 GMT

Thanks for the reply.
Not sure why OR is not working for me.
I tried this but it is not showing all the Assets.

index=I earliest=-1d@d latest=now) OR (index=summary earliest=-1Y@Y latest=now)

Re: How to join data from two indexes

poojak2579 — Mon, 17 Jul 2017 23:07:25 GMT

Thanks for the reply.

Re: How to join data from two indexes

poojak2579 — Mon, 17 Jul 2017 23:50:15 GMT

It will be great if anybody can help me understand why Or is not working for me.

Re: How to join data from two indexes

cmerriman — Tue, 18 Jul 2017 00:53:01 GMT

Try this and see what happens. What type of data comes back?

(index=I earliest=-1d@d latest=now) OR (index=summary earliest=-1Y@Y latest=now)
|eval combo = Asset+""+ID|eval combo1=If(index="summary",combo,null())
|stats min(First_Found_Date) as FFD min(Earliest_FF) as EFF values(combo1) as combo1 by combo
|where isnull(combo1)

Re: How to join data from two indexes

poojak2579 — Tue, 18 Jul 2017 20:34:55 GMT

If I am not wrong "OR" should return everything from the index=I as well as Index=summary.

(index=qualys_summary earliest=-1Y@Y latest=now) returns 8000 Assets

(index=I earliest=-1d@d latest=now) returns 6000 Assets

(index=I earliest=-1d@d latest=now) OR (index=summary earliest=-1Y@Y latest=now) returns 2500 Assets from Index=I only

Both the indexes have different fields. But Asset and Id is common to both.

Re: How to join data from two indexes

DalJeanis — Tue, 18 Jul 2017 20:42:47 GMT

That part you posted is missing the initial open parenthesis.

Hey, @cmerriman - If you could, please run a quick run-anywhere search to verify "earliest" can be used like that, with two different values inside different parenthesis? I've never actually run something like that and I'm not at work today/ tomorrow so I can't test it.

Re: How to join data from two indexes

poojak2579 — Tue, 18 Jul 2017 20:59:31 GMT

Thanks Dal Jeanis
I have that initial open parenthesis in my query.
(index=I earliest=-1d@d latest=now) OR (index=summary earliest=-1Y@Y latest=now)

Actually I was also not sure when I posted this question if we can use earliest like that with two different values in two different parenthesis.

Re: How to join data from two indexes

poojak2579 — Tue, 18 Jul 2017 21:11:15 GMT

I think we cant use earliest like that.
I tried this query on the same index and got today's results only.
(index=I earliest=-3d@d latest=now) OR (index=I earliest=-5d@d latest=now).

Is there any other way to get data from two different indexes with different time frame and without using join or append?

Re: How to join data from two indexes

cmerriman — Tue, 18 Jul 2017 21:13:13 GMT

i ran it with some of my own data using a earliest=-30d@d latest=@d and earliest=-1d@d latest=now and my events went from an average of 400 events/day to 100k yesterday, so i'd say it worked. i see both sourcetypes are coming through. @DalJeanis

Re: How to join data from two indexes

cmerriman — Tue, 18 Jul 2017 21:14:37 GMT

are you using index=qualys_summary or index=summary? you have it listed both ways in your previous comment

Re: How to join data from two indexes

poojak2579 — Tue, 18 Jul 2017 21:18:05 GMT

I shows me both the sourcetypes if I use "append" but only one sourcetype if I use "OR"

Re: How to join data from two indexes

poojak2579 — Tue, 18 Jul 2017 21:22:49 GMT

Please ignore that. I am using summary for all my queries. Please ignore that mistake.

Re: How to join data from two indexes

poojak2579 — Tue, 18 Jul 2017 21:31:29 GMT

So you are getting events for yesterday and today only and not 30 days?

Re: How to join data from two indexes

cmerriman — Tue, 18 Jul 2017 22:19:41 GMT

I am getting events all 30 days for one of my events and only yesterday for the other. It seems to be working for me