Compare current events with events in csv

[ksharma7]

I have a csv file which has fields say
_time success_count failed_count. Every 5 min we have data in these fields. This data is for past say 4 months. Now what I need is to compare current data every 5 min by the data in csv to calculate week over week. Like say success_count today to be compared with the count one and two weeks back same time present in csv and calculate difference in them.

I have data in csv from December- February and now want to compare my current data( april data) with dec or jan or feb same time just a week before todays date in month say jan.

[shivanshu1593]

You can try something like this:

|inputlookup yourlookup_name.csv | hoursago(24) | fields + field1, field2, success_count | appendcols [|inputlookup yourlookup_name.csv | hoursago(336) | rename success_count as success_count1 | table field1, field2, success_count1] | eval difference=success_count1 - success_count | table difference

This search will pull your new values upto 24 hours. Then will pull values from 2 weeks back. Eval command will take care of subtraction, which you can play with as per your needs.

Let me know if it helps!

[richgalloway]

Is the CSV file indexed? It would be much easier to do what you want with indexed data.

[ksharma7]

Also I'm working on splunk enterprise

[ksharma7]

I probably cannot index my csv because of permissions issues but will check. If you can help with indexed and non indexed query that would be helpful

[ksharma7]

Well I have a.csv which has columns
_time ( having date and time) site1 sitepart2 success_count failure_count.
For say site1 I have values say x y z and for x i have sitepart2 as d f g and for y also d f g. I have one lookup b.csv which has all site1 in it.
What I want is to create an alert which will show me output say,
Site1 sitepart2 week1 week2 currentsuccess change
And trigger alert upon some condition say change>80 and week1 week2 success count should be picked from a.csv

For indexing csv , how can I do that in this case?

[richgalloway]

I don't understand your description of the CSV. Can you put it in table form?

[ksharma7]

And also is there any other advance way which can help me with comparison without may be using this dumped csv data. May be some advance query which can help me find any dips in my data without even using two three months back data

[ksharma7]

@richgalloway , Let me frame question again:
Below is a query which I use to get week over week comparison and get alerted:
index=rxc_connect event="Go" success=true host=rxc* sourcetype=abc earliest=-2w-30m@m latest=-2w@m tid=50 [|inputlookup quest.csv | fields tid source ] | fillnull value=0 | stats count as f2 by tid,source, prod| append [search index=rxc_connect event="Go" success=true host=rxc* sourcetype=abc earliest=-1w-30m@m latest=-1w@m tid=50 [|inputlookup quest.csv | fields tid source ] | fillnull value=0 | stats count as f1 by tid,source, prod] | append [search index=rxc_connect event="Go" success=true host=rxc* sourcetype=abc earliest=-30m@m latest=@m tid=50 [|inputlookup quest.csv | fields tid site ] | fillnull value=0 | stats count as f by tid,source, prod ] | fillnull value=0 | stats max(f1) as Week1,max(f2) as Week2, max(f) as Current by tid, source,prod | eval Average_2W = round(((Week1+Week2)/2),0) | where (Current< Average_2W) |eval change = round((((Average_2W - Current)/Average_2W)*100),0) | where (Average_2W > 30 AND change >=50)

Now I do not have data for last two weeks to compare with current in splunk but I have saved data in form of csv like below from month of Dec to Feb. Now I want that say today is 02/05/2020 01:00 till 02/05/2020 01:30 should be compared with same date same time same day of any other month for week over week calculation:

CSV I have is like xyz.csv:
_time ID source prod Success failed
01/12/2019 00:00:00. 1 a q 1. 0
01/12/2019 00:00:00 2 b. r 2 1
01/12/2019 00:05:00. 1 a q 3 0

Csv quest has combinations of ID source prod available with ID being unique and can have multiple prod associated to it
Id tid source prod