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

[olbapito]

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!!

[olbapito]

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

[gcusello]

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

[gcusello]

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