Splunk Search

How do I perform count logic on all entries for a specific line?

andrewtrobec
Motivator

Hello,

I am trying to determine the number of entries that have a field date that is before or equal to _time. My data set is:

_time, Opened, Closed
2016-06-01 00:20:21,2016-06-01 00:20:21,2016-06-02 02:18:23
2016-06-01 00:37:16,2016-06-01 00:37:16,2016-06-02 02:25:14
2016-06-01 01:12:07,2016-06-01 01:12:07,2016-06-03 02:44:41
2016-06-01 01:17:27,2016-06-01 01:17:27,2016-06-03 03:00:08
2016-06-02 14:01:28,2016-06-02 14:01:28,2016-06-03 02:02:50
2016-06-02 14:04:06,2016-06-02 14:04:06,2016-06-03 02:23:52
2016-06-02 14:08:53,2016-06-02 14:08:53,2016-06-04 02:05:21
2016-06-03 18:46:25,2016-06-03 18:46:25,2016-06-04 02:47:58
2016-06-03 18:47:18,2016-06-03 18:47:18,2016-06-04 02:39:18

(during import _time was created based on Opened)

My goal is to get a table that will tell me for a given day, how many entries have an Opened value equal to or before that day along with the number of entries that have a Closed value equal to or before that day (but only for the entries that have already been counted due to their Opened value):

_time,openedCount,closedCount
2016-06-01,4,0
2016-06-02,7,2
2016-06-03,9,4

I have tried as much as I can to use the join command, but I'm not having much luck. Right now I am experimenting with the append command to see whether I can achieve my goal. I'm opening this question in the meantime to see whether I can get any pointers...

Thank you!

Andrew

0 Karma

sundareshr
Legend

How about this. I am assuming given_day=today()

... | eval e_closed=strptime(Closed, "%Y-%m-%d %H:%M:%S") | eval given_day=relative_time(now(), "@d") | eval o_count=if(_time<=given_day, 1, 0) | eval c_count=if(e_closed<=given_day, 1, 0) | timechart span=1d sum(o_count) as opened sum(eval(if(c_count=1 AND o_count=1, 1, 0))) as closed
0 Karma

andrewtrobec
Motivator

Thanks for the answer. It's not quite what I'm looking for but it's given me something to work with. In reality the given_day should be the the _time value for a particular entry.

What I'm looking for is:

Given the _time of an entry, how many objects were opened on or before that time, and how many of those same objects have been closed on or before that time?

Some pseudo-code:

FOR EVERY ENTRY BEFORE _time
    INCLUDE IF [Opened] < [_time]
    INCLUDE IF [Closed] < [_time]

What makes it difficult is that I have to be able to search through all previous data to make my calculation, and I am struggling to figure out how to do that.

Regards,

Andrew

0 Karma

sundareshr
Legend
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 ...