Getting Data In

How to copy the field values of one event to another event of different sources by comparing with IP and MAC ?

nagendra008
Explorer

I tried to extract fields form different sources by comparing two IPs. I want to copy the fields of location and state by comparing IP and MAC. Present I am seeing the table 1, but I want a result as table 2.

Table 1:

Time    IP            MAC    LOCATION    STATE    source

10:00   10.10.10.11   XXX                         foo.com

12:00   10.10.10.11   YYY                         foo.com

11:11   10.10.10.11   XXX    INDIA       A.P      joo.com

Table 2:

output: I want to copy the fields of location and state by comparing IP and MAC

Time    IP            MAC    LOCATION    STATE    source

10:00   10.10.10.11   XXX    INDIA       A.P      foo.com

12:00   10.10.10.11   YYY                         foo.com
0 Karma
1 Solution

nagendra008
Explorer

Hi,

I used the transaction, but it's grouping the values with matching IP and MAC.
Here is the another way without join or transaction commands.

Search:

index = foo.com OR index = joo.com | eval IPMAC = coallesce(IP,MAC)| streamstats current=t values(LOCATION) as LOCATION , values(STATE) as STATE by IPMAC | table *

Note: Here, the key point is streamstats which copy the fields of the second index by matching IP and MAC.

If you are having different field names which represent the same structure, try using the search below. It might be useful.
Search:

index = foo.com OR index = joo.com |eval IP  = coallesce(IP,IPADDR) | eval MAC = coallesce(MAC, MAC1)| eval IPMAC = coallesce(IP,MAC)| streamstats current=t values(LOCATION) as LOCATION , values(STATE) as STATE by IPMAC | table *

Thanks,
Nagendra.

View solution in original post

0 Karma

nagendra008
Explorer

Hi,

I used the transaction, but it's grouping the values with matching IP and MAC.
Here is the another way without join or transaction commands.

Search:

index = foo.com OR index = joo.com | eval IPMAC = coallesce(IP,MAC)| streamstats current=t values(LOCATION) as LOCATION , values(STATE) as STATE by IPMAC | table *

Note: Here, the key point is streamstats which copy the fields of the second index by matching IP and MAC.

If you are having different field names which represent the same structure, try using the search below. It might be useful.
Search:

index = foo.com OR index = joo.com |eval IP  = coallesce(IP,IPADDR) | eval MAC = coallesce(MAC, MAC1)| eval IPMAC = coallesce(IP,MAC)| streamstats current=t values(LOCATION) as LOCATION , values(STATE) as STATE by IPMAC | table *

Thanks,
Nagendra.

0 Karma

gfreitas
Builder

Hi nagendra008,

I would suggest you to use a lookup to enrich the data. Another possible sollution would be to use the transaction command to bring events that have the same IP and MAC address together and use the field of location and state of one of the events.

Hope this helps.

0 Karma

nagendra008
Explorer

Hi Gfreitas,

On my understanding lookup will be helpful only with small data, Lookup performance is less when we try to use with millions of records.

Regards,
Nagee.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...