Archive
Highlighted

how to get the count of transactions monthly where Transaction_Date is greater than 01/01/2016 ?

New Member
  1. I have fields like TransactionDate and Status. How Can I get the count of transactions which where active (i.e Status=Active) where TransactionDate > 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
    
  2. 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 !

Tags (1)
0 Karma
Highlighted

Re: how to get the count of transactions monthly where Transaction_Date is greater than 01/01/2016 ?

Legend

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 TransactionDate 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 TransactionDate 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 <=?

0 Karma
Highlighted

Re: how to get the count of transactions monthly where Transaction_Date is greater than 01/01/2016 ?

New Member

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 TransactionDate = strptime(TransactionDate,"%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

0 Karma
Highlighted

Re: how to get the count of transactions monthly where Transaction_Date is greater than 01/01/2016 ?

New Member

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 TransactionDate = strptime(TransactionDate,"%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

0 Karma