topic Re: Two index related inquiries in Splunk Search

Two index related inquiries

kulo — Thu, 13 Jul 2017 08:28:05 GMT

I now have two index needs related inquiries, which indexB the B field is a subset of A field of indexA, how do I change this
The two index events are as follows

I want to achieve the following effect

Re: Two index related inquiries

gcusello — Thu, 13 Jul 2017 08:32:27 GMT

Hi Kulo,
could you share more details?
Bye.
Giuseppe

Re: Two index related inquiries

kulo — Thu, 13 Jul 2017 09:09:49 GMT

I now want to achieve the effect is that if the indexA field1 is equal to the indexB field1, the output indexA field2 and indexB field2

The two indexes are as follows：

indexA

field1 field2
abc.com 192.168.0.1

indexB

field1 field2
abc.com malwaredomain

Re: Two index related inquiries

gcusello — Thu, 13 Jul 2017 09:16:59 GMT

Hi Kulo,,
there are many ways to do that:

index=indexA 
| rename field1 AS field field2 AS "A.field2"
| join field [ search index=indexB | rename field1 AS field field2 AS "B.fields2"| fields field "B.fields2" ]
| table field "A.field2" "B.fields2"

Obvioulsy, you don't nee to rename field1 if it has the same name in both the indexes and your don't need to rename field2 if has different field names in the two indexes.

Bye.
Giuseppe

Re: Two index related inquiries

kulo — Fri, 21 Jul 2017 09:15:28 GMT

I want to do is to indexA field1 and indexB field1 to do comparison, and indexA file1 for indexB field1 subset.

indexA

field1 field2 
abc.com 192.168.0.1


indexB

field1 field2
*.abc.com*  malwaredomain

Re: Two index related inquiries

gcusello — Fri, 21 Jul 2017 09:56:26 GMT

Are you sayng that in indexA.field1 you have only domain (abc.com) and in indexB.field1 you have the full url (/aaa.abc.com)?
and you want to compare indexA.field1 (abc.com) with indexB.field1 (aaa.abc.com)?
if this is you need, try something like this:

index=indexA 
 | rename field1 AS field field2 AS "A.field2"
 | join field [ search index=indexB | rex field=field1 ".*\.(?<field>\w*\.\w*)" | rename field2 AS "B.fields2" | fields field "B.fields2" ]
 | table field "A.field2" "B.fields2"

Bye.
Giuseppe

Re: Two index related inquiries

niketn — Mon, 24 Jul 2017 05:51:39 GMT

@kulo, Can you please confirm the exact field names/values because they are crucial for us to assist you with exact query. Based on your data examples seems like field names field1 and field2 are the same in two indexes A and B but have different values.

For example field1 in indexA contains partial URL and field1 in indexB containing complete URL. Also field2 in indexA contains IP and field2 in indexB contains some tag name.

Please confirm and add exact data (mock/anonymize if necessary).

Re: Two index related inquiries

kulo — Wed, 26 Jul 2017 06:59:25 GMT

I updated my question, trouble everyone to help look at

Re: Two index related inquiries

kulo — Wed, 26 Jul 2017 07:02:55 GMT

I updated my question, trouble everyone to help look at

Re: Two index related inquiries

gcusello — Wed, 26 Jul 2017 07:25:29 GMT

There are many ways:

index=indexA OR indexB
| stats values(category) AS category by domain src_ip
| table src_ip domain category

index=indexA
| join domain [search index=index2]
| table src_ip domain category

I prefer first chioice.

Bye.
Giuseppe

Re: Two index related inquiries

kulo — Wed, 26 Jul 2017 07:35:48 GMT

I would like to query with domain for comparison, if they are equal, the output src_ip, domain, category

Re: Two index related inquiries

gcusello — Wed, 26 Jul 2017 07:55:50 GMT

try

 index=indexA OR indexB
 | stats values(src_ip) AS src_ip values(category) AS category by domain
 | table src_ip domain category

in this way you could have more IPs for each domain, instead in the first solution you have a couple domain IP.

Bye.
Giuseppe

Re: Two index related inquiries

niketn — Wed, 26 Jul 2017 08:59:21 GMT

Create Field Alias for csv sourcetype to rename field domain as query. http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Addaliasestofields

index=dns OR index=malware_domain
| stats values(src_ip) as src_ip values(category) as category by query

Re: Two index related inquiries

DalJeanis — Wed, 26 Jul 2017 19:33:39 GMT

Here's a couple of other ways you could try...

| multisearch 
    [search index=dsn (other search terms) | fields src_ip query | rename query as domain]
    [search index=malware (other search terms) | fields domain category]
| stats values(*) as * by domain


index=dsn (other search terms) | fields src_ip query | rename query as domain
| join type=left domain [index=malware (other search terms) | fields domain category]