Splunk Search

How to match my search with a lookup?

kiran331
Builder

Hi

I want to match the search with lookup file. I have a lookup with host names. i have to match with windows data for the last login on asset. how can i match lookup file?

search i'm using:

eventtype=windows_logon_success src_ip=* src_nt_host=* user!="*$"|rename src_nt_host as Name [|inputlookup asset_list.csv|table Name] | table Name _time
Tags (2)
0 Karma
1 Solution

somesoni2
Revered Legend

Try like this (gets the last logon time from windows logon data, for each hosts available in Lookup file)

eventtype=windows_logon_success src_ip=* [|inputlookup asset_list.csv|table Name | rename Name as src_nt_host ] user!="*$" | dedup src_nt_host  |rename src_nt_host as Name  | table Name _time

Update*

For summary data

| tstats summariesonly count from datamodel=Authentication.Authentication where * by _time,Authentication.src span=1h | drop_dm_object_name("Authentication") | search  [|inputlookup asset.csv | table Name | rename Name AS src ]| dedup src| table _time src

OR

| tstats summariesonly count from datamodel=Authentication.Authentication where  [|inputlookup asset.csv | table Name | rename Name AS "Authentication.src"] by _time,Authentication.src span=1h | drop_dm_object_name("Authentication") | dedup src| table _time src

View solution in original post

0 Karma

somesoni2
Revered Legend

Try like this (gets the last logon time from windows logon data, for each hosts available in Lookup file)

eventtype=windows_logon_success src_ip=* [|inputlookup asset_list.csv|table Name | rename Name as src_nt_host ] user!="*$" | dedup src_nt_host  |rename src_nt_host as Name  | table Name _time

Update*

For summary data

| tstats summariesonly count from datamodel=Authentication.Authentication where * by _time,Authentication.src span=1h | drop_dm_object_name("Authentication") | search  [|inputlookup asset.csv | table Name | rename Name AS src ]| dedup src| table _time src

OR

| tstats summariesonly count from datamodel=Authentication.Authentication where  [|inputlookup asset.csv | table Name | rename Name AS "Authentication.src"] by _time,Authentication.src span=1h | drop_dm_object_name("Authentication") | dedup src| table _time src
0 Karma

kiran331
Builder

As search is taking too long, Is it possible to match lookup with Summary data?
query i'm trying:

| tstats summariesonly count from datamodel=Authentication.Authentication where * by _time,Authentication.src span=1h | drop_dm_object_name("Authentication") |table src _time [|inputlookup asset.csv | table Name | rename Name AS src ]| dedup src| table _time src

0 Karma

somesoni2
Revered Legend

Try the updated answer(s).

0 Karma

sundareshr
Legend

I think this is what you're looking for. Let me know if this doesn't work

eventtype=windows_logon_success [|inputlookup asset_list.csv | table Name | rename Name AS src_nt_host ] | stats latest(src_nt_host) as Name latest(_time) as Time | eval Time=strftime(Time, "%c%) | table Time Name
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...