topic Re: How to find yearlly,monthly, weekly wise data using single date value? in Splunk Search

How to find yearlly,monthly, weekly wise data using single date value?

kavyaa — Wed, 08 Jul 2015 13:30:44 GMT

Hi,
I have Transaction date format as below. I want to find yearlly,monthly, weekly wise data using single date value?.
Can we apply filters for that?. Please help me. Thanks in advance........

**2011-03-18
 2011-06-26
 2012-03-16
 2012-05-21
 2013-04-23
 2013-07-14
 2014-02-12
 2014-03-29
 2015-01-21**

Re: How to find yearlly,monthly, weekly wise data using single date value?

vinitatsky — Wed, 08 Jul 2015 13:48:21 GMT

Hi Kavyaa

Can you please explain in details on what your requirement is and what is expected output?

Thanks
Vinit

Re: How to find yearlly,monthly, weekly wise data using single date value?

woodcock — Wed, 08 Jul 2015 13:53:20 GMT

Like this:

... | rex "(?<date_year>\d{4})-(?<date_month>\d+)-(?<date_mday>\d+)" | stats count by date_year

... | rex "(?<date_year>\d{4})-(?<date_month>\d+)-(?<date_mday>\d+)" | where date_year="2014"

... | rex "(?<date_year>\d{4})-(?<date_month>\d+)-(?<date_mday>\d+)" | where date_year>="2000" AND date_year<="2015" | stats count by date_month

Re: How to find yearlly,monthly, weekly wise data using single date value?

kavyaa — Tue, 29 Sep 2020 06:39:06 GMT

Hi,
I have query as like this
**
|dbquery "CENTRO" "SELECT TransDate , ShowRoomCode ,
Num_of_Customers FROM Centro_Customer_Visit

**
The output is as follows

Transdate       ShowRoomCode   Number_of_Customers
 2011-03-18             C01                       26   
  2011-06-26            C02                       43
  2012-03-16            C01                       18
  2012-05-21           C03                        14
  2013-04-23           C02                        27
  2013-07-14           C01                        13
  2014-02-12           C03                         45
  2014-03-29          C02                         23
  2015-01-21           C01                        12

I want to apply 3 filters for TransDate. First filter for Year, Second filter for month, third filter for day using the above transdate column. How its work?. When i click on particular year, particular month, perticular day that regarding showroomcode,total customers placed into chart

Please help me. Thanks in advance.......

Re: How to find yearlly,monthly, weekly wise data using single date value?

kavyaa — Tue, 29 Sep 2020 06:39:09 GMT

Hi,
I have query as like this
**
|dbquery "CENTRO" "SELECT TransDate , ShowRoomCode ,
Num_of_Customers FROM Centro_Customer_Visit

**
The output is as follows

Transdate       ShowRoomCode   Number_of_Customers
 2011-03-18             C01                       26   
  2011-06-26            C02                       43
  2012-03-16            C01                       18
  2012-05-21           C03                        14
  2013-04-23           C02                        27
  2013-07-14           C01                        13
  2014-02-12           C03                         45
  2014-03-29          C02                         23
  2015-01-21           C01                        12

Re: How to find yearlly,monthly, weekly wise data using single date value?

woodcock — Thu, 09 Jul 2015 14:11:19 GMT

If I understand you correctly, you would like to drilldown on each piece of the date which you can do like this:

|dbquery "CENTRO" "SELECT TransDate , ShowRoomCode, Num_of_Customers FROM Centro_Customer_Visit" | rex field=TransDate "(?<date_year>\d{4})-(?<date_month>\d+)-(?<date_mday>\d+)" | table date_year date_month date_mday ShowRoomCode Number_of_Customers

This will create a table where you can click on any portion of the TransDate and drilldown into the raw data and from there you can click on the Visualization tab to chart.

Re: How to find yearlly,monthly, weekly wise data using single date value?

kavyaa — Fri, 10 Jul 2015 05:14:37 GMT

Hi woodcock garu,

Yes.Thank you for you reply. I want exactly put the filter on year, month, day using transdate.

|dbquery "CENTRO" "SELECT TransDate , ShowRoomCode, Num_of_Customers FROM Centro_Customer_Visit | rex field=TransDate "(?\d{4})-(?\d+)-(?\d+)" | table date_year date_month date_mday ShowRoomCode Number_of_Customers

when i execute the above query in search, i am getting error like

Error in 'dbqury' command : command ="dbquery", java.lang.IllegalArgumentException:illegal string-escape sequence:"\d".

Please help me. Please resolve this issue.
Thanks in advance,
A.kavya.

Re: How to find yearlly,monthly, weekly wise data using single date value?

woodcock — Fri, 10 Jul 2015 13:12:19 GMT

I copied your dbquery as-is from your example and it was missing a double-quote on the end. I have re-edited it; try it now.