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!

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...