How do I create an alert that is triggered if a source type exists in a lookup table?

[soumyacharya91]

I want to create an alert that triggers when a source type doesn't exist in a lookup table (e.g. srctype.csv). But I'm not sure how to create the search string for this. The fields I'm using in the srctype.csv lookup table is source type as follows:

If the source type matches a source type listed in the table, the alert should triggered. Any help on how to do this are much appreciated.

[KailA · Accepted solution]

Hello :),

You can try something like that :

index=foo 
| stats count by sourcetype
| inputlookup append=true srctype.csv
| stats count by sourcetype
| where count > 1

That should return you the sourcetype present in the index AND in the lookup
Let me know.

KailA

[soumyacharya91]

Thanks,

This code have done the job with a minor change, i.e |where count=1. using this we can get all the remaining fields which are not available.

[KailA]

I thought you wanted the sourcetype present in the index and the lookup but glad that help you 🙂

[Vijeta]

Please try below query ,

index=foo |stats count by sourcetype|join type=outer sourcetype [|inputlookup srctype.csv| eval x=1] | where isnull(x)

This will give you all sourcetype from index foo and the field x with value 1 wherever the lookup has sourcetype else the value of x will be null if the particular sourcetype does not exist in lookup.

[cmerriman]

without knowing the dataset for sourcetypes you're looking at, something like this might work:

index=foo |stats count by sourcetype|join sourcetype [|inputlookup srctype.csv]

and alert if the results are greater than 0

[soumyacharya91]

Actually I need the values which are not matched from the lookup table.

In this query I think, It is matching and publishing the result of current match instead of the values which are not matched with the lookup table. I need to trigger the alert if that not match is present only.

I tried below query also but I'm getting the same result as yours.

index=foo
| lookup srctype.csv sourcetype OUTPUT sourcetype as sourcetype