Splunk Search

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

kavyaa
Explorer

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**
0 Karma

woodcock
Esteemed Legend

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.

0 Karma

kavyaa
Explorer

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

woodcock
Esteemed Legend

Like this:

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

Or

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

Or

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

kavyaa
Explorer

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

0 Karma

vinitatsky
Communicator

Hi Kavyaa

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

Thanks
Vinit

0 Karma

kavyaa
Explorer

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

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!