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!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...