Splunk Search

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

longmen
Path Finder

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)."

DayAudit nameAlert name at startAlert sent date at startAlert name at endAlert sent date at endSend alert
1James  James1Y
 Michael  Michael1Y
2JamesJames1James1N
  Michael1   
3JamesJames1James1N
 Michael  Michael3Y
4JamesJames1James1N
 MichaelMichael3Michael3N
5JamesJames1James1N
 MichaelMichael3Michael3N
6JamesJames1James1N
  Michael3   
7JamesJames1James1N
 Michael  Michael7Y
8James  James8Y
  Michael7   
Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Yes, that is probably why. For test purposes, I created a audit1.csv, audit2.csv and audit3.csv as separate lookups and then had a dropdown to select which one I wanted to use

    <input type="dropdown" token="audit">
      <label>audit</label>
      <choice value="audit1.csv">audit 1</choice>
      <choice value="audit2.csv">audit 2</choice>
      <choice value="audit3.csv">audit 3</choice>
      <default>audit1.csv</default>
    </input>

I also had a couple of other lookups alertspre.csv and alertspost.csv where I store the contents of alerts.csv before and after it is updated so I can see the state going into the search and the state after.

      <table>
        <search>
          <query>| inputlookup $audit$
| lookup condition.csv country
| where condition="Y"
| fields email employee_id country condition
| eval Alert_date=relative_time(now(),"@m")
| append 
    [| inputlookup alerts.csv
    | table Alert_sent_date Alert_updated_date email employee_id
    | outputlookup alertspre.csv
    | where relative_time(now(),"@m")-Alert_sent_date &lt; 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 country as _country condition as _condition
| where isnotnull(_Alert_date) OR employee_id = 0
| outputlookup alerts.csv
| outputlookup alertspost.csv
| where isnotnull(_Alert_date)
| eval send_alert=if(_send_alert="Y",_send_alert,"N")
| rename _country as country _condition as condition
| fieldformat Alert_sent_date=strftime(Alert_sent_date,"%H:%M:%S")
| fieldformat Alert_updated_date=strftime(Alert_updated_date,"%H:%M:%S")</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>

View solution in original post

longmen
Path Finder

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. 

longmen_0-1627345788859.png

longmen_1-1627345825039.png

Very respectfully,

Long 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Path Finder

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". 

longmen_0-1627332463242.png

He disappears on Day2 

longmen_1-1627332545694.png

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

longmen_2-1627332609375.png

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 

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Path Finder

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. 

longmen_0-1627338953463.png

longmen_1-1627339027803.png

 

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. 

longmen_3-1627339128562.png

longmen_4-1627339159647.png

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).

longmen_5-1627339203067.png

longmen_6-1627339349287.png

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 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Path Finder

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. 

longmen_0-1627346571545.png

longmen_1-1627346596602.png

Very respectfully,

Long 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

Also, which version of splunk are you using?

longmen
Path Finder

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 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Path Finder

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. 

 

longmen_0-1627572405773.pnglongmen_1-1627572475727.pnglongmen_2-1627572524975.pnglongmen_3-1627572565929.png

Here is how I set up condition.csv file

longmen_4-1627573024532.pnglongmen_5-1627573114979.png

Could you please advise?

Very respectfully,

Long 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

longmen
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Path Finder

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 

longmen_2-1627613821787.png

 

longmen_0-1627613212664.pnglongmen_1-1627613251791.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

longmen
Path Finder

longmen_0-1627645420155.pnglongmen_1-1627645561073.png

longmen_2-1627645609036.pnglongmen_3-1627645642817.png

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 

 

0 Karma
Get Updates on the Splunk Community!

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...