I have fields like Transaction_Date and Status. How Can I get the count of transactions which where active (i.e Status=Active) where Transaction_Date > 2016-01-01. I tried the following and I am only getting the transactioncount for each day after 2016-01-01
status=Active Transaction_Date > 2016-01-01 |stats count as 'ActiveTransactions' by Transaction_Date
Also, how can I get the transaction count between 2 Dates (i.e Transaction_Date between 2016-01-01 and 2016-05-01)
Any help is Appreciated. Thank you !
Thanks for the above suggestions. Date comparison is not working as expected. TransactionDate has Date as the datatype. I want to get the transaction count for a given month. I tried with the following but it returns the count for months prior to 2016-01-01
| eval Transaction_Date = strptime(Transaction_Date,"%Y-%m-%d")
| eval startDate = strptime("2016-01-01","%Y-%m-%d")
| where Transaction_Date > startDate
| bucket span=1month _time
| stats count(startDate) by _time
How could I get the transaction count for a given month where TransactionDate (DataType - Date) is greater than 2016-01-31?
Expected results should be like the following
_time TransactionCount
2016-02 295
2016-03 300
2016-04 250
If you just want the count for all the days after January 1, then just do this
status=Active Transaction_Date > 2016-01-01 | stats count
and if you want the count between 2 dates, then do this
status=Active Transaction_Date > 2016-01-01 Transaction_Date < 2016-05-01 | stats count
But I am concerned about a couple of things. First, "2016-01-01" is not a date to Splunk, it is a string. I have no idea what Transaction_Date contains - it could be a string or a number or Linux epoch time. But even if it is a string, the "greater than" comparison may not work as you want. IMO, it would be better for both the Transaction_Date and the times to be in epoch time.
Assuming that both dates are strings, you could do this to convert them:
status=Active
| eval Transaction_Date = strptime(Transaction_Date,"%Y-%m-%d")
| eval startDate = strptime("2016-01-01","%Y-%m-%d")
| where Transaction_Date > startDate
| stats count
Second, you are using "greater than" and "less than." So in the first search, you will have no results on January 1. And for the second search, only transactions on the 2nd, 3rd and 4th will be counted. Perhaps you should consider using >=
and <=
?
Thanks for the above suggestions. Date comparison is not working as expected. TransactionDate has Date as the datatype. I want to get the transaction count for a given month. I tried with the following but it returns the count for months prior to 2016-01-01
| eval Transaction_Date = strptime(Transaction_Date,"%Y-%m-%d")
| eval startDate = strptime("2016-01-01","%Y-%m-%d")
| where Transaction_Date > startDate
| bucket span=1month _time
| stats count(startDate) by _time
How could I get the transaction count for a given month where TransactionDate (DataType - Date) is greater than 2016-01-31?
Expected results should be like the following
_time TransactionCount
2016-02 295
2016-03 300
2016-04 250