Creating an alert for a file that wasn't received

[CharlesC]

We run some reports to list specific filenames that we've received over a period of time. These particular reports are predicated on account and file name matches. Can we create an alert from one of these reports to identify an account and filename that was NOT received? Please know how it can be done. Thanks.

An example of one of the reports below:

index=log source="/logs/file_tracking.log" (Accountname IN("Account1") AND Filename IN ("File1*","File2*","File3*","File4*","File5*","File6*","File7*")) 
| table Transfer, Account, File, Start_Time, End_Time
| sort - Start_Time

[gcusello]

Hi @CharlesC,

you have to create a lookup containing all the filenames to search (alled e.g. filenames.csv) with only one field (called e.g. Filename).

Then you have to run a search like this:

index=log source="/logs/file_tracking.log" (Accountname IN("Account1") AND Filename IN ("File1*","File2*","File3*","File4*","File5*","File6*","File7*")) 
| stats count BY Filename
| append [ | inputlookup filenames.csv | eval count=0 | fields Filename count ]
| stats sum(count) AS total BY Filename
| where total=0

with this search you have an alert that lists all the missing filenames.

Ciao.

Giuseppe

[CharlesC]

Buongiorno Giuseppe,

Thanks for the feedback. I created a new csv with just filenames for lookup, also created the new search you suggested too.  However it didn't produce any results when it ran. Tried it a few times since after modifying the search to add the faux filenames from my post and still getting an output of 0 results each time. 

[gcusello]

Hi @CharlesC,

if you run the main search

index=log source="/logs/file_tracking.log" (Accountname IN("Account1") AND Filename IN ("File1*","File2*","File3*","File4*","File5*","File6*","File7*")) 

and you manually search the Filenames, have you results?

Second try, this search is case sensitive, so please try this:

index=log source="/logs/file_tracking.log" (Accountname IN("Account1") AND Filename IN ("File1*","File2*","File3*","File4*","File5*","File6*","File7*")) 
| eval Filename=lower(Filename)
| stats count BY Filename
| append [ | inputlookup filenames.csv | eval eval Filename =lower(Filename), count=0 | fields Filename count ]
| stats sum(count) AS total BY Filename
| where total=0

Ciao.

Giuseppe

[CharlesC]

Thanks. There are results now with the updated search but the results are NOT for the missing files. Instead the new search lists almost the same files as the csv but drops a few of them. 67 files in the csv and 58 in the new search.

[gcusello]

Hi @CharlesC,

with the last search you list all the Filnames of your lookup that aren't in the main search results, so they are misseing.

If there's a relation between Accounts and Filenames, you have to put it into the lookup,

In other words, you have to put in the lookup, all the Filenames for each Account to check.

so lookup could have two fields (Account and Filename) and then you can modify your search in this way:

index=log source="/logs/file_tracking.log" (Accountname IN("Account1") AND Filename IN ("File1*","File2*","File3*","File4*","File5*","File6*","File7*")) 
| stats count BY Account Filename
| append [ | inputlookup filenames.csv | eval count=0 | fields Account Filename count ]
| stats sum(count) AS total BY Account Filename
| where total=0

 Ciao.

Giuseppe

[CharlesC]

I agree with adding the account name since there is a relationship between them in my search, but the output is still the same.  They both return the same results after this recent change, listing all the files that were found. Thanks.

[gcusello]

Hi @CharlesC,

please, use my search without the last row.

You'll have many results: the ones with total=0 are the missed ones, the others are the present ones.

So you can check if the results are correct.

Ciao.

Giuseppe

[CharlesC]

Unfortunately it's still the same output for me when running both searches. I've even tried to modify the filename search to look for values that I know don't exist. 

[gcusello]

Hi @CharlesC,

are you saying that all the rows have total=0 or that all the rows have total>0?

If all the rows have total=0, it means that there's a problem in the main search that hasn't results and you have to debug it.

If instead all the rows have total>0, it means that alla the pairs account/filename that you have in the lookup are matched, you can debug this adding to the lookup a new pair account/filename that surely ins't in the main search.

Could you share the search you're using and a screenshot of the results (with column header)?

The logic of this search is that from the main search you have all the pairs account/filename present in your data; the lookup gives you all the waited pairs with count=0.

So grouping the results from both main search and lookup you have a situation where total= means that the pair is present only in lookup, and total>0 means that the pair is present in both main search and lookup.

Ciao.

Giuseppe

[CharlesC]

Thank you for following up.

The first search that creates the csv lookup file is similar to what I have below. It creates the csv after Output results to lookup. File8 and File9 are values not expected that were added.

index=log source="/logs/file_tracking.log" (AccountName IN("Account1") AND Filename IN (("File1*","File2*","File3*","File4*","File5*","File6*","File7*”, “File8*”, “File9*"))

| table AccountName, Filename

The 2nd search is similar to:

index=log source="/logs/file_tracking.log" (AccountName IN("Account1") AND Filename IN (("File1*","File2*","File3*","File4*","File5*","File6*","File7*”))
| stats count BY AccountName Filename
| append [ | inputlookup Filenames.csv | eval count=0 | fields AccountName Filename count ]
| stats sum(count) AS total BY AccountName Filename
| where total=0 

They both return the same exact results (28) but the 2nd search has a 3rd column that shows a total of 0

[gcusello]

Hi @CharlesC,

At first, for testing, use an old time period (e.g. yesterday or last hour) to be sure that the incoming data don't modify resuts and they are fixed.

Comparing the main search results:

index=log source="/logs/file_tracking.log" (AccountName IN("Account1") AND Filename IN (("File1*","File2*","File3*","File4*","File5*","File6*","File7*”))
| stats count BY AccountName Filename

and the full search results (without the last row):

index=log source="/logs/file_tracking.log" (AccountName IN("Account1") AND Filename IN (("File1*","File2*","File3*","File4*","File5*","File6*","File7*”))
| stats count BY AccountName Filename
| append [ | inputlookup Filenames.csv | eval count=0 | fields AccountName Filename count ]
| stats sum(count) AS total BY AccountName Filename

You should see that the rows with total>0 are the same in both the results (only main search and full search) and that in the full search you have some additional rows with all the pairs in lookup that haven't any result in the main search (you can test this putting some test pairs in lookup).

Have you this situation?

Then for testing:

• limit the data to only one account and only two or three filenames in results (being sure to have all of them in the results) and note them,
• and in the lookup put the same pairs (that you're sure to have in the results) and one or two test pairs that you're sure that aren't present in main results;

You should have total>0 for the present pairs and total=0 for the additional testing pairs.

Ciao.

Giuseppe