I have a log like this
eventtype=000111 msg=malicious srcip=126.96.36.199
eventtype=123 msg=traffic srcip=188.8.131.52 hostname=MyMachine
Both lines are on the same index, would like to get something like this
eventtype=000111 msg=malicious srcip=184.108.40.206 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!!
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.
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
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