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