topic Re: Idenitfying Common Value between Two Indexes and Exporting Results with a Mix of Fields in Getting Data In

Idenitfying Common Value between Two Indexes and Exporting Results with a Mix of Fields

danfinan — Thu, 03 Oct 2019 14:44:47 GMT

Hi guys,

Apologies for the long winded title! I'm struggling to extract a common value from two indexes and get out any meaningful data. I have two products on our network that both include the IP address of a user's device. I would like to match the IP address in two seperate events and combine them into one result. The tricky part is that the timestamps are not idenitcal. An example:

Index=Red (Timestamp 11:00am)
userName=Bob Jones, userIP=192.168.2.12, userPass=true

Index=Blue (Timestamp 11:02:04)
NAME:bob.jones,IPADD:192.168.2.12,EventID:4682672

...

I would like the result to be formatted something like this:

Time Name IP EventID userPass

...

I'd be grateful if anybody could point me in the right direction. I have an ok-to-good understanding of RegEx but I've tried creating a common field and it never works as expected.

Thank you for any help you can provide!

Re: Idenitfying Common Value between Two Indexes and Exporting Results with a Mix of Fields

richgalloway — Thu, 03 Oct 2019 16:55:26 GMT

Since the two timestamps are different, you'll have to choose one of them to display. I'll choose the Red time.

index=Red OR index=Blue | eval IP=coalesce(userIP, IPADD) 
| stats first(_time) as Time, values(userName) as Name, values(EventID) as EventID, values(userPass) as userPass by IP 
| table Time Name IP EventID userPass

Re: Idenitfying Common Value between Two Indexes and Exporting Results with a Mix of Fields

danfinan — Wed, 09 Oct 2019 10:00:27 GMT

Hi richgalloway, thanks for taking the time to help with this - very kind of you! Your solution got me a little closer to what I am trying to achieve. The 'coalesce' input created the common fields that I needed, thank you.

The table created has the correct layout however the events from the two indexes do not make it to the same row - likely because the timestamps may be off - in your experience, is there a way to combat this? Let's say for example that the event from index=red was at 11:04:09 and the event in index=blue happened 11:05:01. Can that still be paired? Failing that, can the timestamp for a single index be ignored?

If I'm asking something completely out of the scope of this question, please don't worry about it. Thank you for getting me close to my target though!

Re: Idenitfying Common Value between Two Indexes and Exporting Results with a Mix of Fields

richgalloway — Wed, 09 Oct 2019 11:32:24 GMT

Timestamps should not be a factor in this query as the events from each index are joined by IP, not time, and the first timestamp found is the one displayed in the table. Can you share some (sanitized) output?