csv lookup for search query based on continuity and 7-day condition

[longmen]

Hi Splunk Experts, 

I wonder if you could help me putting the below logic in to a search query?

Here the link reference to my original question.
https://community.splunk.com/t5/Splunk-Search/kv-store-search-send-alert-and-also-store-the-the-aler...

Thanks 

"The logic of your requirement seems to be that there are two situations when a user appears in the audit (satisfying the conditions). Either, they are in the list of alerts from yesterday, or they are not. If they were not in the list from yesterday, send an alert and add them to the list (noting when they were added). If they were in the list, don't send an alert but note they were there. now, process the list and remove anyone who didn't appear today (so that an alert will be generated next time they appear on the list), Also, remove anyone who has been on the list for 7 days including today (so that an alert will be generated next time they appear on the list, even if it is tomorrow - day 8)."

Day	Audit name	Alert name at start	Alert sent date at start	Alert name at end	Alert sent date at end	Send alert
1	James			James	1	Y
	Michael			Michael	1	Y
2	James	James	1	James	1	N
		Michael	1
3	James	James	1	James	1	N
	Michael			Michael	3	Y
4	James	James	1	James	1	N
	Michael	Michael	3	Michael	3	N
5	James	James	1	James	1	N
	Michael	Michael	3	Michael	3	N
6	James	James	1	James	1	N
		Michael	3
7	James	James	1	James	1	N
	Michael			Michael	7	Y
8	James			James	8	Y
		Michael	7

[longmen]

Hi ITWhisperer,

Thanks for your response, sir. Yes, I also noticed that Robert appears in Day2. Here is the content of the condition.csv file. 

Very respectfully,

Long 

[ITWhisperer]

Using the csv files from the previous answer and taking a 7 minute rather than 7 day expiry on the alerts, the runanywhere example becomes

| inputlookup audit.csv
| lookup condition.csv Department Name Role email employee_id
| where watch="Y"
| fields email employee_id
| eval Alert_date=relative_time(now(),"@m")
| append 
    [| inputlookup alerts.csv
    | where relative_time(now(),"@m")-Alert_sent_date < 60*7 OR employee_id = 0]
| stats max(*) as * by email employee_id
| eval _send_alert=if(isnull(Alert_sent_date), "Y",null())
| eval Alert_sent_date=if(_send_alert="Y", Alert_date,Alert_sent_date)
| eval Alert_updated_date=Alert_date
| rename Alert_date as _Alert_date
| outputlookup alerts.csv
| where isnotnull(_Alert_date)
| eval send_alert=if(_send_alert="Y",_send_alert,"N")
| fieldformat Alert_sent_date=strftime(Alert_sent_date,"%H:%M:%S")
| fieldformat Alert_updated_date=strftime(Alert_updated_date,"%H:%M:%S")

The lines you would need to change for your case would be

| inputlookup audit.csv (replace with your search)

| eval Alert_date=relative_time(now(),"@d")
| append 
    [| inputlookup alerts.csv
    | where relative_time(now(),"@d")-Alert_sent_date < 60*60*24*7 OR employee_id = 0]

You might also want to filter the final result for send alert = Y so you only send an alert if any events are left.

[longmen]

Hi ITWhsiper,

Thanks a lot for your help.  There is one missing piece in this.  Robert appears on Day1 and spunk sends an alert "Y". 

He disappears on Day2 

He appears again on Day3 but Spunk does not send another alert. 

What query do I need to add here so that Splunk will send an alert for Robert on Day 3? You used to give me this 

| eval _send_alert=if(isnull(Alert_sent_date) OR Alert_date - Alert_sent_date > 60 * 2, "Y",null())

I wonder how do I integrate it into this to make it work. Could you please advise?

| inputlookup audit.csv
| lookup condition.csv Department Name Role email employee_id
| where watch="Y"
| fields email employee_id
| eval Alert_date=relative_time(now(),"@m")
| append 
    [| inputlookup alerts.csv
    | where relative_time(now(),"@m")-Alert_sent_date < 60*7 OR employee_id = 0]
| stats max(*) as * by email employee_id
| eval _send_alert=if(isnull(Alert_sent_date), "Y",null())
| eval Alert_sent_date=if(_send_alert="Y", Alert_date,Alert_sent_date)
| eval Alert_updated_date=Alert_date
| rename Alert_date as _Alert_date
| outputlookup alerts.csv
| where isnotnull(_Alert_date)
| eval send_alert=if(_send_alert="Y",_send_alert,"N")
| fieldformat Alert_sent_date=strftime(Alert_sent_date,"%H:%M:%S")
| fieldformat Alert_updated_date=strftime(Alert_updated_date,"%H:%M:%S")

Very respectfully,

Long 

[ITWhisperer]

Try changing these two lines

| outputlookup alerts.csv
| where isnotnull(_ALERT_DATE)

 to

| where isnotnull(_ALERT_DATE) OR EMPLOYEE_ID = 0
| outputlookup alerts.csv

[longmen]

Hi ITWhisper,

It is not fixed and the error still persists. James , Robert John, Michael has "COUNTRY=CAN" and Splunk sends them alert on Day1. 

James, William, David, and Richard are flagged on Day2 and Splunk only sends alert for William, David, and Richard. Splunk does not send an alert for James because his case is continuity.  That is perfect. 

On Day3, James, Robert, Thomas, and Charles are flagged. Splunk did not send an alert for James (This is good because his case is continuity). Splunk sends an alert for Charles and Thomas (This is also good). However, Splunk does not send an alert for Robert (which is not good because Robert is flagged on Day1, he is not flagged on Day2 but he is flagged again on Day3. Hence, Splunk is supposed to send an alert to Robert).

Could you please help me fixing Robert's condition? 

Also, if I need to split this search, for example, one search is for continuity under 7 days and another search is for 24-hour break, just like Robert here, and combine both search somewhere? Please advise!

Very respectfully,

Long 

[ITWhisperer]

It looks like for Robert to still be in Day 2, the condition is still Y despite him changing country. Please can you share the contents of condition.csv?

[longmen]

Hi ITWhisperer,

Thanks for your response, sir. Yes, I also noticed that Robert appears in Day2. Here is the content of the condition.csv file. 

Very respectfully,

Long 

[ITWhisperer]

Can you move this line

| eval ALERT_UPDATED_DATE=ALERT_DATE

to above this line

| rename ALERT_DATE as _ALERT_DATE​

so we can see whether the user is making it passed the first where command

[ITWhisperer]

It looks like the condition.csv mechanism isn't working for you.

Please try changing these lines

| fields EMAIL EMPLOYEE_ID

to

| fields EMAIL EMPLOYEE_ID​ COUNTRY CONDITION

| rename ALERT_DATE as _ALERT_DATE

to

| rename ALERT_DATE as _ALERT_DATE COUNTRY as _COUNTRY CONDITION as _CONDITION​

and after

| eval SEND_ALERT=if(_SEND_ALERT="Y",_SEND_ALERT,"N")

add 

| rename _COUNTRY as COUNTRY _CONDITION as CONDITION​​

[ITWhisperer]

Also, which version of splunk are you using?

[longmen]

Hi  ITWhisperer,

Thanks for all your responses. I am testing your logic on the paid Splunk virtual environment and Splunk still does not send another alert for the person on day3 when he is flagged and is sent an alert on Day1, disappear on Day2, and flagged again on Day3. I wonder if it works for you? 

Very respectfully,

Long 

[ITWhisperer]

It is working for me although I have slightly different commands to you. What I don't understand is why, for example, Robert appears in the results of the search on day 2. If as your output suggests his country is not CAN, he should have been removed by the first where command. If he has been removed, ALERT_DATE is not set when he is then added back in by the append inputlookup, which means he should have been removed (again) by the third where command. Can you rerun it with the changes I have suggested so we can see some more of the field values?

[longmen]

Hi ITWhisperer, 

Thanks for your prompt response. I implemented your logic on Splunk 8.2.1. and Robert still pops up on Day2 even though he is not flagged on Day2 csv file.  Also, no alert was sent to Robert on Day3 as well even though he is flagged on Day3. 
Additionally, I implemented your logic on paid Splunk virtual environment and another dataset and I've got the same result. 

Here is how I set up condition.csv file

Could you please advise?

Very respectfully,

Long 

[ITWhisperer]

What results do you get from just the first two line i.e. search + lookup?

(The results you have shown confirm that CONDITION is set to Y when we are not expecting it to be)

[ITWhisperer]

I am presuming CONDITION doesn't exist after the first line i.e. just the search?

[longmen]

I wonder if it is better for me to try working this on kv store instead? Thanks

[ITWhisperer]

OK I may have figured out what might be going on - the fields I was attempting to hide by renaming them with leading underscores are still getting written to the csv files, so try adding this

    | table ALERT_SENT_DATE ALERT_UPDATED_DATE EMAIL EMPLOYEE_ID

after the inputlookup of alerts.csv

[longmen]

Hi ITWhisperer,

Thanks for always responding to my questions. It did not work. On day 2, Robert still appears. I believe your queries are supposed to overwrite the alerts.csv on Day1 and on Day2 there should be James, David, Richard, and William appearing on the alerts.csv file. However, this query lookups everyone and keep them in the alerts.csv file on day2. 

On Day3, because the query also finds Robert on Day2 (which was supposed to be deleted), therefore, it is not sending another alert. In this case, Robert's case is just like James who appears for 3-day straight because Splunk does not overwrite the alerts.csv and delete Robert on Day2. Please advise and please let me know if I am wrong!

Very respectfully, 

Long 

[ITWhisperer]

What are the results of just the index search on day 2?

[longmen]

audit1.csv was the file that I uploaded on Day1 and audit2.csv was the file that I uploaded on Day2. Could it be that because Splunk search in both files that why is Robert appeared in Day 1 and also Day 2? 

Very respectfully, 

Long