Could you please help me to convert above excel formula into query ?? Thanks in advance.
Need to filter one date and status then count by the Week.
Like this:
index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo
| eval _time = relative_time(strptime(Created, "%m/%d/%Y"), "@w5")
| chart count BY _time Status
It becomes very easy to help when you specify your needs the way that we ask you to. The problem was fairly trivial once you got around to doing that.
Like this:
index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo
| eval _time = relative_time(strptime(Created, "%m/%d/%Y"), "@w5")
| chart count BY _time Status
It becomes very easy to help when you specify your needs the way that we ask you to. The problem was fairly trivial once you got around to doing that.
I am making the following assumtions:
- you have a field "created" which contains a date in the format "YYYY-MM-DD"
- you have a field called "status" which has the status values
If this applies you can search like this:
index=$yourindex sourcetype=$yoursourcetype | eval week=strftime(strptime(created,"%Y-%m-%d"),"%W") | chart count by week, status
If your creation date has another format, you need to adjust the strptime
parameters accordingly. If you need further assistance, feel free to comment.
Hi @DMohn
Thanks
My requirement is
I have 2 fields,
1.Status(Values are Status1,Status2..etc)
2.Created(MM/DD/YYYY)
So here I have to create Week Field(Should use Created Field).If created date is (9,10,11,12,13,14 or 15 then Week will be 15th of Feb i.e Friday).
Statistics should display like below
Week Status1 Status2 Status3 Status4
28-Sep 66 10 51 5
5-Oct 67 10 52 5
12-Oct 67 10 52 5
19-Oct 69 10 54 5
26-Oct 69 10 54 5
2-Nov 69 10 54 5
Okay, just for clarification - the "Week" field must reflect the Friday of the current week? Or could it be the calendar week number?
Friday of the week
Saturday to Friday is the week. Which ever created these days then that ticket will come under that week.
show us your raw events and the desired final output. Throw the Excel formula in the dumpster.
We have to get the status count by week.
Week should be based on Created field.
Week needs to be created using Created field.
Example if Tickets are created on 12,13 or 14th Feb then created week will be 14th Feb.
So now we have to get the status(Status1,Status2,Status3..etc) count by Week.