I want to calculate the amount of change in between today's score and yesterdays.
This is a file with a few days data. Using Score field, calculate calc I want to document the change in calc.
I'm just trying with below one but it's not showing the way I want.
| inputlookup FinalScore.csv
| where date = "2/14/2018" or date = "2/15/2018"
| eval _time= strptime(date, "%m/%d/%Y")
|eval nowstring=strftime(now(), "%m/%d/%Y")
| eval test = "2018-02-19"
| eval temp=if(_time='2018-02-15', "today","yesterday")
| eval percent=case(DataSource=="DoP", 0.317757009, DataSource=="VxN", 0.196261682, DataSource=="Phishing", 0.112149533, DataSource=="BCOAT", 0.121495327, DataSource=="IPORT", 0.252336449)
| eval calc = percent*Score
| timechart span=1d sum(calc) by User
Try this ...
| inputlookup FinalScore.csv
| where date = "2/14/2018" or date = "2/15/2018"
| eval _time= strptime(date, "%m/%d/%Y")
| eval percent=case(DataSource=="DoP", 0.317757009,
DataSource=="VxN", 0.196261682,
DataSource=="Phishing", 0.112149533,
DataSource=="BCOAT", 0.121495327,
DataSource=="IPORT", 0.252336449)
| eval calc = percent*Score
| stats sum(calc) as calc by User _time
| timechart span=1d sum(calc) by User
...or the last two lines can be replaced by...
| stats sum(calc) as calc by User _time
| eval Day=strftime(_time,"%Y-%m-%d")
| chart sum(calc) over User by Day
... or by ...
| eventstats max(_time) as today min(_time) as yesterday
| stats sum(eval(case(_time=yesterday,calc))) as calc1
sum(eval(case(_time=today,calc))) as calc2 by User
| eval calc3 = calc2-calc1
| rename calc1 as yesterday, calc2 as today, calc3 as chaange
Try this ...
| inputlookup FinalScore.csv
| where date = "2/14/2018" or date = "2/15/2018"
| eval _time= strptime(date, "%m/%d/%Y")
| eval percent=case(DataSource=="DoP", 0.317757009,
DataSource=="VxN", 0.196261682,
DataSource=="Phishing", 0.112149533,
DataSource=="BCOAT", 0.121495327,
DataSource=="IPORT", 0.252336449)
| eval calc = percent*Score
| stats sum(calc) as calc by User _time
| timechart span=1d sum(calc) by User
...or the last two lines can be replaced by...
| stats sum(calc) as calc by User _time
| eval Day=strftime(_time,"%Y-%m-%d")
| chart sum(calc) over User by Day
... or by ...
| eventstats max(_time) as today min(_time) as yesterday
| stats sum(eval(case(_time=yesterday,calc))) as calc1
sum(eval(case(_time=today,calc))) as calc2 by User
| eval calc3 = calc2-calc1
| rename calc1 as yesterday, calc2 as today, calc3 as chaange
Thanks for the reply,
Some how i was not able to see what i need, i will try to tune the search and will see.
@splunk_rocks - if you post what you want the output to look like, then maybe we can help more.
Your logic seems confusing. Could you post some sample data from your FinalScore.csv file (the data you want to process) and provide corresponding expected output?
Thanks for the reply, looks like i cant attach any thing here. but see if selected user here for different dates, i want see risk score change yesterday and today date like how much changed with another tab risk score changes last 7 days etc.
date User RiskScore
12/19/2017 xyllaj 421.2102834
12/8/2017 xyllaj 32.3053341
below one im using.
| inputlookup FinalScoresforDashboard_IX_Vvid_Test.csv
|eval percent=case(DataSource=="DLP", 0.317757009, DataSource=="VPN", 0.196261682, DataSource=="Phishing", 0.112149533, DataSource=="BLUECOAT", 0.121495327, DataSource=="IRONPORT", 0.252336449)
|eval calc = percent*Score
|stats sum(calc) as RiskScore by User date
|table date User RiskScore
|sort -User -date
|head 200
Does your lookup contains all the fields you reference in the search above?? (sample data doesn't show those fields). Also the field name in your sample data is RiskScore but in like 3 (eval calc) you're using just Score. Which one is correct?