topic Re: How to make two searches in the same index but different event types? in Splunk Search

How to make two searches in the same index but different event types?

olbapito — Tue, 30 Aug 2022 15:32:02 GMT

Hi!

I have a log like this

eventtype=000111 msg=malicious srcip=11.11.22.22

eventtype=123 msg=traffic srcip=11.11.22.22 hostname=MyMachine

Both lines are on the same index, would like to get something like this

eventtype=000111 msg=malicious srcip=11.11.22.22 hostname=MyMachine

I´ve tryied using joins, but they just could get results when indexes are different. because the initial condition of eventtype doesn´t match with the second event. this is the query which doesn´t work

index=index_ logid=1122 | fields * |
join srcip [search index=index_ | table hostname ] |
table eventtype msg srcip hostname

Can you help me? Thanks!!

Re: How to make two searches in the same index but different event types?

gcusello — Tue, 30 Aug 2022 15:53:39 GMT

HI @olbapito,

it isn't relevant that the events are in the same index with different eventtypes, you could use join (but I don't hint:

index=index_ logid=1122 eventtype=000111 | join srcip [ search index=index_ eventtype=123 | table srcip hostname ] | table eventtype msg srcip hostname

I hint to use a different approach because Splunk isn't a DB and Join is a very slow command;

index=index_ ((logid=1122 eventtype=000111) OR eventtype=123) | stats values(eventtype) AS eventtype values(msg) AS msg values(hostname) AS hostname BY srcip

Ciao.

Giuseppe

Re: How to make two searches in the same index but different event types?

olbapito — Tue, 30 Aug 2022 17:25:17 GMT

Hi Giuseppe,

I like the way of doing that without join. i´ve notice that joins are terrible slow.

The problem with the example is that it give data that I don´t need. I mean eventtype 000111 is an event which occurs very few times in a week, meanwhile event 123 occurs permanently. both events, 000111 and 123 have the srcip field, but just the event 123 have the hostname field, so I need to having found an unique 000111 event, look for a 123 event which has the same ip address on the same time (around 10 minutes) to take its hostname.

On the query suggested I´m getting ip and hostname for every event which has an 123 event. but they don´t have an 000111 event.

Thanks

Re: How to make two searches in the same index but different event types?

gcusello — Wed, 31 Aug 2022 06:28:19 GMT

Hi @olbapito,

using the above search you display the hostname only if present so the srcips without hostname aren't relevant so you can exclude the results without hostname with a simple search after the stats command:

index=index_ ((logid=1122 eventtype=000111) OR eventtype=123) | stats values(eventtype) AS eventtype values(msg) AS msg values(hostname) AS hostname BY srcip | search hostname=*

But anyway, if you want you can take the hostname value only from eventtype=123, you can try something like this:

index=index_ ((logid=1122 eventtype=000111) OR eventtype=123) | stats values(eventtype) AS eventtype values(msg) AS msg values(eval(if(eventtype=123,hostname,""))) AS hostname BY srcip

Ciao.

Giuseppe