How to Represent Custom Date Time Field on Weekly basis in Splunk

How to Represent Custom Date Time Field on Weekly basis in Splunk

Hey Guys, Do you know how easy it is to represent the custom “date time field” day wise. But it is not that easy to do the same if we want the data to be represented by “weekly”. So, hold your breadth and check out the below tips ( Splunk Query ) to get the data representation by “weekly”.

Below we have given an example of a custom “date time field” .

time_week

In the above query we have extracted time portion from every event as a ATIMESTAMP through interactive field extractor

Below we have given the query how to represent the custom “date time field” by “weekly” basis.

Query:

index=_internal sourcetype=splunkd_ui_access 
| table ATIMESTAMP,method 
| rex field=ATIMESTAMP "(?<ATIME>.*?):.*" 
| fields - ATIMESTAMP 
| eval ATIME=strptime(ATIME,"%d/%b/%Y") 
| eval A=ATIME 
| fields - ATIME 
| bin span=1w A 
| stats count by A,method 
| eval A=strftime(A,"%d-%m-%Y") 
| xyseries A,method,count 
| eval B=strftime(strptime(A,"%d-%m-%Y"),"%m") 
| sort B 
| fields - B

Result:

time_week2

 

Visualization:

time_week3

Explanation:

In the above query ATIMESTAMP and method are existing field names in _internal index and sourcetype name is splunkd_ui_access. By table command we have taken those two fields in a tabular format. We have taken upto second part as ATIME from ATIMESTAMP field by rex command. By the fields command we have removed ATIMESTAMP field from the result set. Now by the strptime function with eval command we have converted human readable format to epoch time format and store in A field . Then we have removed ATIME field from the result set by the fields command. We have used bin command to set time span as 1w for weekly basis. By the stats command we have taken A and method fields and by the strftime function we have again converted epoch time to human readable format. Then we have used xyseries command to change the axis for visualization. So that time field(A) will come into x-axis. Again we have month portion from the A field by strftime and strptime function and store the values in B field . At last we have sorted B field to get the values in calendar wise by sort command and then removed the B  field  from the result set by fields command.

Hope this has helped you in achieving the below requirement without fail !!

How to Represent Custom Date Time Field on Weekly basis in Splunk

 

Happy Splunking !!

Advertisements

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.