I need to display all the values in the below search

[tvon1990]

index="index1" PROJECTNAME="*" ( OBJECT_TYPE="*" OR OBJECT_TYPE="*" )  | dedup PROJECTNAME OBJECT_TYPE NAME |map [search index="inedx2" $NAME$ |eval NAME="$NAME$"|eval OBJECT_TYPE="$OBJECT_TYPE$"|eval PROJECTNAME="$PROJECTNAME$" ]| dedup source | table PROJECTNAME OBJECT_TYPE NAME  source

[tvon1990]

Hi ,
No it didn't provide the answer I needed. however i tried and sovled it.

index="index1" PROJECTNAME="*" ( OBJECT_TYPE="**" OR OBJECT_TYPE="*Field*" ) | stats count by PROJECTNAME OBJECT_TYPE NAME | map  [ search index="index2" sourcetype="sqr*" $NAME$ | eval NAME = "$NAME$" | eval OBJECT_TYPE="$OBJECT_TYPE$" | eval PROJECTNAME="$PROJECTNAME$"| dedup NAME source | table source NAME OBJECT_TYPE PROJECTNAME] maxsearches=1000| sort by source

[DalJeanis]

@tvon1990 - I've converted your comment to an answer so you can mark the question as closed by accepting your own answer. I've also marked your code as code so that the * and such won't be read as HTML markers. (Just highlight your code and use the 101 010 button to keep the interface from messing with your code.)

You can simplify that first line to this...

index="index1" PROJECTNAME="*"  OBJECT_TYPE="*"  | stats 

...because two asterisks is the same as one, and "*Field*" is covered by "*"

FYI, the field NAME in the command |dedup NAME source is redundant, since there will only ever be one NAME in each mapped search, but it isn't hurting anything by being there.

[Richfez]

I've done some more digging around, and I don't think there's any supported way to do this in a generalized manner GIVEN the apparent fact you have no extracted fields on the index2 side of things, and that side is the "outer" side of the "join" (I hate to use those words because this is Splunk and not SQL, but they're the right words in this particular case).

If you can create fields out of index2, or at least create the one field you need, then this is not hard at all. It's simply the fact that it's a freeform, "search all text" problem on that side that causes the problem, and that it's the side that isn't always there.

So, my opinion is to create a field out of that side. You might have to use 35 separate EXTRACT-blah lines in your props.conf, but this is the only way I can find to do it correctly and properly (or even so that it'll actually work). For instance...

! mliu should not pass the b_claim_status_cd condition to the following sub query

could be

  EXTRACT-passed-invalid-condition = (?<error_caller>\w+) should not pass the( ?<error_condition>\w+) condition to the following sub query

After which you'll have a field called error_caller which is equal to mliu and error_condition equal to b_claim_status_cd. Your needs WILL vary, and will be different from the stuff I just wrote, but what I wrote may be a start. And again you might have to do that several times - one for each "style" of event. (Actually, I'd replace every single whitespace after the = with \s+, too, but I left it this way for readability and it should work like this). You may find you have fewer patterns than you thought and you can generalize the extractions and only make a few. I don't know, I have seen a total of one single event from that side.

Once you are done, you can easily subsearch (with some renaming/formatting) one into the other, transaction them, stats group them, or even join or whatever. Even if you just get one or two extracted, you can probably test with that (or ask again) and get something working.

(BTW, I did come up with a completely hacky solution involving makemv and mvexpand on _raw, but it's TERRIBLY slow and awful. As in "many minutes to run" on a few hundred items, and creating 100's of KB of normalized search. As in, more importantly, not something I'd share here or anywhere because it's too terrible. Plus I actually don't think even IT will work, it'll still not return all the things on both sides - I had figured I'd tackle that after at least I got it searching the other side, which I did and realized the futility.)

[Richfez]

tvon1990,

Also, on this last one - try a search on "index=Index2" over a reasonable time period then switch to the "patterns" tab to help you figure out how many props entries you may need.

Beyond that, did one of the four answers provide what you need? Do you need some help implementing any of the potential solutions mentioned?

Happy Splunking,
Richfez

[Richfez]

Let's take a step back.

You seem to have two sets of data. One of which is (or was) in a database, and another which is in Splunk. You are trying to match certain values across these two sets of data and want to output the intersection of them.

There are three general ways to go about this.

Create and use a DB Output, Create and use a DB Lookup, or Create a "regular" lookup from a DB.

The difference between them are large. Let's take them in order so you can decide which may fit your needs best.

A DB Output takes database material - literal rows of a database - and outputs them to Splunk. So your source of the data is a SQL Query, like select name, location, address from tablename. The destination of your data is a Splunk index. So, after you run this your data is in Splunk, just like regular data, and you can search on it just as if it's in Splunk normally, because it is in Splunk "normally". In that case, elsewhere in here is an answer showing a way to use stats to connect these together. The docs for Creating a DB Output cover this thoroughly, but if you decide this is the route you want to go and get stuck, please ask questions!

A DB Lookup leaves the data in the DB and lets you do more or less a "regular lookup" only directly into the DB. In this case, there's no index at all on the Splunk side - each time you need to connect a result you literally ask the DB again for the information. Now, if you keep in mind that most DBs are fairly quick you may realize this works quite well most of the time. The docs for Creating a DB Lookup step you through the entire process and show examples of use at the end. Again if you go this route and get stuck, ask questions!

Lastly, there is the ability to create a "regular" lookup using data from a DB. This is a hybrid. In it, you create a regular CSV or KVstore based lookup, then use dbxquery, and either/or/both of inputlookup and outputlookup to update that lookup. There is a short blurb on them here in the docs. I think that section is short because it's simple and all the real work to do this is in the Lookup documentation.

Can you please read through those docs and see if any of them fit the bill for what you are trying to do?

[Richfez]

From your comments above I think I get what you are asking. It would be great to have actual data to look at, but hopefully we can guess our way through this just knowing the little we know.

You seem to be connecting things together in a harder way than is usually necessary, so let's see if we can figure out a simpler method - even if not perfect. If we can get close with a simpler search, maybe it'll be easier to get it just right.

When you have data that matches up on several fields, the usual Splunk way to do this is to load all the data up at once, then use stats or transaction to group them on the common fields. You seem to have a lot of fields that DO match up, so for your search, try:

(index=index1 OR index=index2) PROJECTNAME="*" OBJECT_TYPE="*" | stats list(NAME) BY PROJECTNAME, OBJECT_TYPE

That will frab all the items in index1 or in index2 where the PROJECTNAME and OBJECT_TYPE are set, right? Then we use stats to list the one that's not in common (NAME) connecting together by projectname and object_type.

If you need to ... | dedup after that, feel free, but try it without first. You might also have to be careful with time frames. Hopefully this gets you something we can work with.

If you can, and if you need more help, please - it would be very beneficial to include a sample of the actual data you are starting with - preferably one from each index that should connect together. (Use the code 101010 button when you paste!) Then follow that up with a mock-up of just what you'd like to see out of those two when they're put together. Showing clearly what you are starting with and what you'd like to see can often be far more helpful than showing the search you have figured out so far (though it all helps!)

Happy Splunking,
Rich

[tvon1990]

Index1 sample event:
2017-06-28 11:43:15.688, PROJECTNAME="Test", OBJECTTYPE="2", OBJECT_TYPE="Field", NAME="B_CLAIM_STATUS_CD", EXTENDED_OBJ_NAME=" ", DESCR=" "

Index2 sample event:
! mliu should not pass the b_claim_status_cd condition to the following sub query

I'm passing the NAME as search parameter in the index2 it'll return the events wherever the string is there from there I can display the source name. But apart from that I need to display the passed NAME variable in order to show that this string is present in this source file is it possible.

[tvon1990]

Below is how data is. I need to pass the name as parameter to search in index2 (index="index2" name) and print the results as shown in below.
index1:
Projectname=Test
object_type=field
name=field
Projectname=Test
object_type=record
name=record

index2:
select field from record(test.sql Source)

expected result:
Projectname object_type name source
Test record record test.sql
Test field field test.sql

[Richfez]

Your obfuscation/generalization is too much - too many "field" "field" "field" values makes it very difficult to see what's going on. We work with data all the time, we can figure out the important bits if given a chance, so can you please post in some actual events? (If you have to obscure some IP addresses or names, fine, but try to keep the structure the same, and try to keep unique fields unique, not "field" but maybe "MyServerName1")

Thanks,
Rich

[niketn]

You still seem to miss the field for correcting data in the two indices. Based on the description index2 should have field called "name" with value= "field". If not please add. Also what is the source of index1?

[tvon1990]

source of index1 is db. No index 2 there is no filed called name.

[niketn]

If none of the field names are same atleast are there field values which can be used for correlation?

[tvon1990]

we can corelate the both with NAME field.

[Richfez]

Please do us a favor and provide samples of EACH of the two types of events? The ACTUAL data? Pretty pretty please? With all the fields intact? Preferable on a pair of events - one from each index - that would actually match up?

[tvon1990]

Index1 sample event:
2017-06-28 11:43:15.688, PROJECTNAME="Test", OBJECTTYPE="2", OBJECT_TYPE="Field", NAME="B_CLAIM_STATUS_CD", EXTENDED_OBJ_NAME=" ", DESCR=" "

Index2 sample event:
! mliu should not pass the b_claim_status_cd condition to the following sub query

I'm passing the NAME as search parameter in the index2 it'll return the events wherever the string is there from there I can display the source name. But apart from that I need to display the passed NAME variable in order to show that this string is present in this source file is it possible.

[Richfez]

OK, we're getting somewhere.

Does index2 contain free form events - ones with no structure, or at least undetermined structure? Or does it have at least some structure ,even if of a handful of different types?

I think all good solutions are going to require applying a structure for it. We can help with that (probably easily) but you'll have to decide if you have enough structure, or if you can change the structure (intrinsic) of that data.

How many results to do you get if you run

index=fw 
| stats count by punct 
| stats count

What is the final count?

[DalJeanis]

Then move the line | dedup source to be inside the map search, because after the map, that is getting rid of all the results except a single result event from each source, regardless of how many times the map was run.

Your mapped search should look like this -

[search index="inedx2" $NAME$ | dedup source | table source |eval NAME="$NAME$"|eval OBJECT_TYPE="$OBJECT_TYPE$"|eval PROJECTNAME="$PROJECTNAME$" ]

Be sure to get rid of the dedup that is after the map.

[Richfez]

What doesn't it show that you think it should? What types of values are there? Can you provide data?

[tvon1990]

I want to display the fields PROJECTNAME OBJECT_TYPE NAME source where source is from index2 and the other three is from index1

[tvon1990]

index1 --> contains the records and fields it's fetched from db
index2 contains the source code files
I want to find which fields are present in which source file and print them like
Source Name(object) Object type and Project