How to create alert only if the lookup table values matches with the field in the search?

[AL3Z]

Hi ,

I am trying to make a search only if the values of lookup table i.e  groups.csv   fields  username  matches with the   username in the below search it should raise an alert .

index=foo sourcetype=WinEventLog 
| stats  values(username) as username, values(Target_Domain) as Domain by userid

Thanks

[isoutamo]

Hi

you could try something like this

index=foo sourcetype=WinEventLog 
| stats values(username) as username, values(Target_Domain) as Domain by userid
| append [ inputlookup groups.csv | fields userid ]
| stats count(userid) as Total values(username) as username, values(Target_Domain) as Domain by userid
| where Total > 1
| fields userid username Domain

 I use here userid not username. You could easily change that to username if there is no several names by userid. If there are several, then you should change this to work with those.

r. Ismo

[AL3Z]

@gcusello @isoutamo Hi,

My actual search is this its not working as expected.

index=foo sourcetype=XmlWinEventLog (EventCode=4743 AND NOT user_name="Win$")
| lookup groups.csv user_name as user_name OUTPUT user_name
| stats count values(EventCode) as EventCode values(signature) as sig values(user_name) as account_deleted values(Target_Domain) as Domain by _time UserSid

my lookup table ( If any deleted user present in the lookup table user_name  it should trigger an alert )

user_name

win$

linux$

vmaware$

[gcusello]

Hi @AL3Z,

the lookup command is used to enrich your search with the content of the lookup.

If you want to use the lookup to filter your main search you have to use it in a different way:

index=foo sourcetype=XmlWinEventLog (EventCode=4743 AND NOT user_name="Win$")
[ | inputlookup groups.csv | fields user_name ]
| stats count values(EventCode) as EventCode values(signature) as sig values(user_name) as account_deleted values(Target_Domain) as Domain by _time UserSid

Ciao.

Giuseppe

[AL3Z]

@gcusello 

if we use append only its raising an alerts why so ? 

| append [ | inputlookup groups.csv | fields user_name ]

[gcusello]

Hi @AL3Z,

this is required to have in your results values that could be not present from your main search (e.g. searching missing hosts), in your case, you need only to filter results from the main search using the lookup (if I correctly understood).

So I hint to use my previous solution.

Ciao.

Giuseppe

[gcusello]

Hi @AL3Z,

did you tried to filter events in the main search?

something like this:

index=foo sourcetype=WinEventLog [ | inputlookup groups.csv | fields username ]
| stats  values(username) as username, values(Target_Domain) as Domain by userid

if you have results you can trigger the alert.

Ciao.

Giuseppe