DHCP lookup to output fields for user, mac based on IP Address by lease time for all sourcetypes

[nathanluke86]

Hi, I was wondering if anyone could help with this problem.

I have created a lookup for DHCP logs which consists of columns that i have screen shot:

What I am trying to achieve is to create and automatic lookup to add fields for user and mac to all other logs that have src_ip or dest_ip, which I have managed to do.

What I want is for the user and mac to reflect based on the time frame the user was assigned that IP (hope this makes sense)

The issue at the moment is although I update the lookup every hour searching logs over a week ago would give me an incorrect user as their IP lease has expired.

Any ideas or ways to accomplish this would be a great help. I think this would really add value and save allot of time matching ip's to users

TIA

[to4kawa]

How about regularly issuing queries with alerts and exporting them with outputlookup?

[nathanluke86]

Do you mean alert on ip change and export to csv as I am successfully doing something similar?

The issue I have is when an IP changes, old events are mapped to the new user who has been assigned that IP and not the actual user who was assigned that ip before.

Thanks

[to4kawa]

How do you purge a lookup table of values over 30 days?

stats latest(user) as user values(something) as somthing

Why not follow the link and update using a query like this?

[nathanluke86]

@to4kawa

I have been looking into this but I am a little confused as to how this would map at search time.

Say for instance I search a firewall log (index=juniper ....) and the time range was a random day last month, how would I map to the user who was assigned that ip in that time period.

Would the relative_time(now() have to be included in the juniper search as what I am trying to achieve is to search a time frame and automatically the user assigned at that time would display.

Below I have included similar SPL I originally used before asking this question.

index =windows source=DHCP
| rex field=nt_host "(?[^.]*)."
|lookup user.csv Source_Workstation as WorkStation OUTPUT user
| where mvcount(user)=1
|streamstats current=f last(dest_ip) as new_ip last(_time) as time_of_change by dest_mac
| where ip!=new_ip
| convert ctime(time_of_change) as time_of_change
| rename ip as old_ip
| table time_of_change, user, WorkStation, dest_mac old_ip, new_ip

Any pointers would be greatly appreciated

[to4kawa]

 index=juniper  
|eval ip=coalesce(src_ip,dest_ip)
|join type=outer ip [ search index=windows source=DHCP
| rex field=nt_host "(?[^.]*)."
|lookup user.csv Source_Workstation as WorkStation OUTPUT user
| where mvcount(user)=1
|streamstats current=f last(dest_ip) as new_ip  by dest_mac
|where ip != new_ip
|eval ip=mvappend(ip,new_ip)
|mvexpand ip
|table user ip]

This is a bad query.
Extract the IP address and username from the DHCP log and match it with the FW IP address.

Since I don't know the details of the log, I used join.