Splunk Search

=SUM(COUNTIFS(F38:F800,"<"&[@Week],$D38:$D800,"<>Status1",D38:D800,"<>Status2",D38:D800,"<>Status3",D38:D800,"<>Status4"))

kvr
New Member

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.

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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.

View solution in original post

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

DMohn
Motivator

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.

0 Karma

kvr
New Member

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

0 Karma

DMohn
Motivator

Okay, just for clarification - the "Week" field must reflect the Friday of the current week? Or could it be the calendar week number?

0 Karma

kvr
New Member

Friday of the week

Saturday to Friday is the week. Which ever created these days then that ticket will come under that week.

0 Karma

woodcock
Esteemed Legend

show us your raw events and the desired final output. Throw the Excel formula in the dumpster.

0 Karma

kvr
New Member

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...