topic Re: compare 6 months ago to start date(can be any specific date) in Splunk Enterprise

compare 6 months ago to start date(can be any specific date)

rivkah01 — Thu, 17 Jun 2021 07:59:35 GMT

we need to check which courses were started during the month of last half a year.

the code I wrote for it:

"start date" - format : 2021-03-22 f

"last_6_months". - calculated now()-6 month

below the query we tried but its not working

could you please advise?

`ssdlc_base("all time back")`
| where email_address!="NA"
| lookup ssdlc_bu_courses.csv BU output courses
| mvexpand courses
| lookup ssdlc_bu_courses.csv BU courses output "start date"
| rename "start date" as "start_date1"
| mvexpand "start_date1"
| eval start_date=strptime(strftime(start_date1,"%Y-%m-%d")."-01","%Y-%m-%d")
| eval last_6_months=strptime(strftime(relative_time(now(),"-6mon"),"%Y-%m")."-01","%Y-%m-%d")
| fields start_date ,last_6_months , start_date1

Re: compare 6 months ago to start date(can be any specific date)

ITWhisperer — Thu, 17 Jun 2021 08:18:29 GMT

Try changing these two lines

| eval start_date=relative_time(start_date1,"@mon") | eval last_6_months=relative_time(now(),"-6mon@mon")

Re: compare 6 months ago to start date(can be any specific date)

rivkah01 — Sun, 20 Jun 2021 12:02:26 GMT

not working the dates are not in the same format.

Re: compare 6 months ago to start date(can be any specific date)

ITWhisperer — Sun, 20 Jun 2021 12:04:55 GMT

| eval start_date=relative_time(strptime(start_date1,"%Y-%m-%d"),"@mon") | eval last_6_months=relative_time(now(),"-6mon@mon")

Re: compare 6 months ago to start date(can be any specific date)

rivkah01 — Sun, 20 Jun 2021 12:11:20 GMT

still no change:

Re: compare 6 months ago to start date(can be any specific date)

ITWhisperer — Sun, 20 Jun 2021 12:16:20 GMT

The field you are showing is "start date" not start_date. What is is start_date and start_date1?

Re: compare 6 months ago to start date(can be any specific date)

rivkah01 — Mon, 21 Jun 2021 13:09:24 GMT

no data for start_date

Re: compare 6 months ago to start date(can be any specific date)

ITWhisperer — Mon, 21 Jun 2021 13:39:30 GMT

That's because you removed the rename you had previously. Either put it back in or use

| eval start_date=relative_time(strptime('start date',"%Y-%m-%d"),"@mon") | eval last_6_months=relative_time(now(),"-6mon@mon")

Re: compare 6 months ago to start date(can be any specific date)

Micheal_S — Mon, 21 Jun 2021 15:15:09 GMT

I had a couple questions in your start date format, what does the "f" at the end of the date format mean?

Can you provide a sample value of "start_date1" from just after mvexpand "start_date1" but before you perform any evals on it?

Re: compare 6 months ago to start date(can be any specific date)

Micheal_S — Mon, 21 Jun 2021 15:44:20 GMT

Playing around with some results it doesn't seem to affect anything, but I was also concerned about the ."-01" in this line from your original post, the resulting output of the strftime() function wouldn't fit the format. You'd end up with something like 2021-03-22-01.

| eval start_date=strptime(strftime(start_date1,"%Y-%m-%d")."-01","%Y-%m-%d")