kv store search, send alert and also store the the alert sent information

[longmen]

Hi everyone, 

I am trying to use Splunk to catch a flag and also send an alert in a report if department = "business and economics" role = "staff" from the above spreadsheet. And I also want Splunk to return a report containing the employee_id, email, alert_sent_date, and also date_updated when I am running the spreadsheet in Splunk on a daily basis. Could anyone please advise? What should I look into to work on this logic? Thanks 

[longmen]

Hi ITWhisperer, Thank you for your kind response. 

I uploaded this csv file onto Splunk. 

my goal is to use KV store search to catch a flag if the condition below is met

and it will come out as below

Additionally, I want Splunk to send an alert email based on the flag in this format 

1. So, for example, on day 1, a cvs file will be uploaded onto Splunk and Splunk will run to see if there is a department = "Business and Economics" and Role = staff. If Splunk catches this flag, it means that there is a policy violation and Splunk will  send an alert to the admin immediately for a remediation. The report that is send to the admin will have the email and employee_id. Also, it will have the date that the alert_sent_date for the employee who violates the policy and also the date_updated. 

2. On the second day, when the second csv file is uploaded onto Splunk and if the same employee is caught violating the policy, there is no need to send an alert to the admin. However, there needs to be an update onto Splunk like this

3. On day 3, when the csv file is uploaded onto Splunk and if the same employee violating the policy, Splunk will send an alert to the admin and the report will need to look like this:

I know that I need to use eval like this :

source = “Splunk Questions.csv” Role = Staff Department = “Business and Economics”

| eval Alert_Sent_Date = 

| eval Alert_Updated_Date =

However, I do not know how to pick up the Alert_Sent_Date and Date_Updated from Splunk and add it to the alert report and also how to update it on the daily basis when I upload the csv file to Splunk everyday. 

Also,  it is only running for seven days. If the same employee has not violated the policy for the next seven days, I also want to have Splunk deleted that employee data from the database. This would be for later discussion.

Anything that I could look into?

Could you please advise? 

Very respectfully,

Long
 

[ITWhisperer]

For test purposes, I created 3 csv files and uploaded them to splunk as new lookup tables

condition.csv

Department,Name,Role,email,employee_id,watch
Business and Econonmics,John,Staff,bcd@yahoo.com,2345,Y

note the watch column to indicate that he is on the watch ilst

 audit.csv

Department,Name,Role,email,employee_id
Computer and Electrical Engineering,Juliette,Director,abc@yahoo.com,1234
Business and Econonmics,John,Staff,bcd@yahoo.com,2345
Computer and Electrical Engineering,Tony,Staff,dce@yahoo.com,3456
Business and Econonmics,Sarah,Director,cde@yahoo.com,6789

 This is your daily audit file - I am not 100% sure how you are loading this to splunk, perhaps as the result of a search or simply copying to the correct place on the servers - for test purposes, I am deleting the current lookup table and adding a new one.

alerts.csv

Alert_sent_date,Alert_updated_date,email,employee_id
1625995380.0,1625995380.0,a@b.c,0

 This is a history of alerts sent and updated. Note that this must have a blank line at the end to ensure the dummy line is properly terminated.

Once these files have been added as lookup tables, you can use this runanywhere example to process the audit.csv against the conditions.csv and update the alerts.csv

| 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]
| stats max(*) as * by email employee_id
| where isnotnull(Alert_date)
| eval _send_alert=if(isnull(Alert_sent_date) OR Alert_date - Alert_sent_date > 60 * 2, "Y",null())
| eval Alert_sent_date=if(_send_alert="Y", Alert_date,Alert_sent_date)
| eval Alert_updated_date=Alert_date
| fields - Alert_date 
| outputlookup append=true alerts.csv
| 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")

For test purpoes, I have aligned my times to the start of the minute, you might want to align to the start of the day; I set the _send_alert flag if there isn't a previous alert sent or if is more than 2 minutes since the last alert was sent, you might want to change this to 24 * 60 * 60 so alerts on consecutive days are not sent (as you specified); note the underscore (_) at the beginning of the flag field, this is so that it isn't written to the csv output table.

You can base your alarm on this search; if you add a where clause for send_alert = "Y", you should only get results at the end if an email needs to be sent, the alerts table will have already been updated before the where clause.

You can also schedule another search to input the alerts.csv and remove anything older than 7 days, but remember to put back the dummy entry.

[longmen]

Hi ITWhisper,

Thank you for your response. It was very helpful in taking me to the next step. I integrated your logic into my Splunk and there are a few more things that I would like to ask. 
1. Why is the Alert_sent_date is populated to 7/15/2021 00:00:00 when I have not enabled and set the triggered  alert to the current date at 00:00:00 yet? 
2. How do I connect this search to alert triggered? Could I just write a query alert into the search or should I just go to Save As > Alert? Which method is better and cleaner since I will have a lot more queries and logic to add to this search? 

Very respectfully,
Long 

[ITWhisperer]

| eval Alert_date=relative_time(now(),"@d")

sets the time to the beginning of today i.e. 00:00:00 so when Alert_sent_date is set, either because it is the first alert or it is at least one day after the previous alert was sent, the value is used.

Either way works, you can edit the search used by the alert afterwards if you need to.

[longmen]

Hi ITWhisperer,

Thank you for your prompt response. In regard to your search commands, I wonder if you could help me explaining how it works.

#1
| eval send_alert1=if(isnull(Alert_sent_date) OR Alert_date - Alert_sent_date > 24 * 60 * 60 , "Y" , null())

My thought is it create send_alert1 If
1# the Alert_sent_date column is blank
Or 2# if the Alert_date – Alert_sent_date is greater than 24*60*60 (in second)
Else:
Return null

#2
| eval Alert_sent_date=if(send_alert1="Y" , Alert_date,Alert_sent_date)
This command creates a column for Alert_set_date if send_Alert1 = “Y” and have Alert_sent_date = Alert_date. 

#3 
| outputlookup append=true alerts.csv
This command is updating the alerts.csv file with the append command

#4
| eval send_alert=if(send_alert1="Y", send_alert1, "N")
This command is creating the send_alert column and set it to send_alert 1 if sent_alert1 is “Y” else set it to “N”

These are my understanding in regard to your search commands. Please let me know if my understanding is aligning with yours or please explain the the difference.

Very respectfully,

Long 

[ITWhisperer · Accepted solution]

#1 sets send_alert1 if this is the first time (in recent memory i.e. the history of alerts sent stored in alerts.csv) or if the alert found in alerts.csv  is more than a day old. This effectively flags the events found in the audit.csv as needing to be sent

#2 creates or updates the alert sent date if we are going to be sending an alert (based on the calculation done in #1)

#3 this updates the history of alerts sent (or rather that will be sent)

#4 this is the flag that the alert should be based on i.e. if any events are left with the flag set to "Y", you should trigger the alert and send the results to admin.

So, yes, your understanding is correct.

[longmen]

Hi ITWhisperer,

Again, greatly appreciated for your response. As I went through your instruction, everything went smoothly through #2 line. 

However, I've got nothing in return for the third command 

As shown in the screenshot below,  inputlookup condition.csv has all the criteria that should be called in the above search. Could you please advise?

very respectfully,

Long 

[ITWhisperer]

In condition.csv you have John's role as "Staff" whereas in your audit.csv you have it as "staff" - these do not match, hence zero rows from the where clause.

[longmen]

---

@ITWhisperer wrote:

In condition.csv you have John's role as "Staff" whereas in your audit.csv you have it as "staff" - these do not match, hence zero rows from the where clause.

---

Hi ITWhisper,

Again thanks for your response, sir. I successfully extracted the employees that meet the condition.csv.  I have several other questions and I hope you could help me out. 

1. I set the alert to throttle and have it sent for every 5 minutes. However,  the Alert_updated_date and send_alert have not been populated with new data. How do I change this?

2. Also, if Splunk identifies the same employee as a flag continuously for the next seven days, I do not want Splunk to send an alert  everyday for the same employee. I want Splunk to send an alert for only the first time it flags the employee. What should I look into this?

3. In the case that Splunk flag an employee on the first day and Splunk does not catches the same employee again for the next 6 days, I would want Splunk to delete the employee information from its database. How could I achieve that?

So ideally, I am uploading a cvs file everyday onto Splunk and have it flagged the employees that meet the criteria in condition.csv. 
If employee A is flagged on the Day#1and disappear for the next 6 days, I would want Splunk to delete his profile from its database.

Also,  if Splunk flagged employee A on Day#1 Day#2......Day#7 continuously, I want Splunk to send the alert for only Day #1

However, if there is a break when Splunk flagged employee A for example it catches employee A on Day#1 but NOT Day #2 but flag again on Day#3. I would want Splunk to send an alert on Day#1 and Day#3. 

Since there are thousand of columns and rows in csv files, I wonder if there is a way to make the search more efficient since the time frame is 7 days. I wonder if there is a way that I could have Splunk deleted the flagged employees that is older than 7 days. 

Please advise!
Very respectfully,

Long 

[ITWhisperer]

The alerts.csv is updated each time the employee is found, so if after the alert search has run, if there is an employee in the alerts.csv which was not updated, you can remove them. You should also remove employees from the alerts.csv if the alert was sent 7 days ago. You can do this by inputlookup command, where clause, and outputlookup. This means that if the employee is picked up on the next scan, they will be seen as a new alert if it is the beginning of a 7 day repeat cycle, or if they weren't on the list in the previous scan. This also simplifies the alert generation search slightly as you don't have to check when the alert was sent - in my example:

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

becomes

| eval _send_alert=if(isnull(Alert_sent_date), "Y",null())

[longmen]

Hi ITWhisper,

Thank you for your response. It has been a really great help! However, I think I am missing a logic where Splunk flags an employee continuously. When Splunk flags an employee continuously in less than or equal to 7 days I want Splunk to send an alert for only once time, which is at the beginning. In the below example, Splunk flags James Julie continuously for seven days. And it sends the alert when (Alert_update_date - Alert_Sent_Date> 24*60*60) is true. I wonder if there is a way that I could write a query to make an exception when Splunk flags Jame Julie continuously without a break, I want Splunk to send a flag for only once. Also, this continuity is only for 7 days and Splunk will send another alert on Day8.In addition, I want Splunk to keep the original condition that it will send an alert when there is a break in between. Could you please advise?

Have a great day!

Very respectfully,

Long 

[ITWhisperer]

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,

Thank you for taking your time making the table. That is right, sir, you are correct, these are the situations that I hope to implement in Splunk. I wonder if there are ways that I could write queries based on these conditions in the search? 

Very respectfully,

Long 

[ITWhisperer]

Please can you clarify - is this kv store already in splunk and updated on a daily basis by some other process? are you looking to create/update the kv store from some other events in splunk? what do you want the alert triggered on, the presence of a user in a particular department? are you looking to track when alerts have been triggered (in the same kv store or elsewhere?