I am displaying some data by Month for 2018/2019 (i.e. 01-2018, 02-2018) on a barchart.
Search Query:
( sourcetype=sourcetype1) OR (sourcetype=sourcetype2) OR (sourcetype=sourcetype3)
| chart sum(eval(if(sourcetype="sourcetype1",ICOS,NULL))) as Actuals sum(eval(if(sourcetype="sourcetype2",ICOS,NULL))) as Forecast sum(eval(if(sourcetype="sourcetype3",ICOS,NULL))) as Budget over "Month"
However I also want to be able to overlay 2017 data over the same period without changing the above x-axis of Month. The 2017 data will come from sourcetype1.
Any ideas how I could do that?
Please get in the habit of using ISO format for dates. In this case, "2018-01", so they will naturally sort in the correct order without any extra work. Here's one way to accomplish your request. This assumes that there is a _time
field in epoch format that establishes the month. If you need to reformat that information, then adjust the code as needed.
( sourcetype=sourcetype1) OR (sourcetype=sourcetype2) OR (sourcetype=sourcetype3)
| rename COMMENT as "Flag the budget type and the value to be summed up"
| eval myType=case(sourcetype="sourcetype1" AND some test for current year,"Actuals"
sourcetype="sourcetype1" AND some test for prior year,"PriorYear",
sourcetype="sourcetype2","Forecast",
sourcetype="sourcetype3", "Budget")
| eval myValue = if(myType="PriorYear", whatever the field is for prior years actual, ICOS)
| rename COMMENT as "Use the end of the report time range to determine the beginning of the year"
| rename COMMENT as "Advance prior year data by one years, and throw away any future data"
| addinfo
| eval _time = if(_time<relative_time(info_max_time,"@y"),relative_time(_time,"+1y"),_time)
| where _time <= info_max_time
| eval myMonth = strftime(_time, "%Y-%m")
| rename COMMENT as "Chart the results"
| chart sum(myValue) over myType by myMonth