I have a graph of percentages by region by month:
However, the months are not displaying in the correct chronological order. Splunk is not recognizing my date_month field as containing time.
I need to change something with strptime or strftime, but I'm not exactly sure what.
Here is the code:
index=webex_sentiment
| eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M")
| eval Country=upper(Country)
| lookup CountryDetails Country OUTPUT Region
| stats count(Rating) as NumberRatings by date_month Rating Region
| eventstats sum(NumberRatings) as TotalRatings by date_month Region
| eval PercentageRatings=round(NumberRatings/TotalRatings,3)
| where Rating=1 OR Rating=2
| chart sum(PercentageRatings) as NegativeSentiment by date_month Region
Please advise
Splunk always displays that as alphanumeric. you need to write an eval to sort it properly. try something like this
EDIT:
index=webex_sentiment
| eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M")
| eval Country=upper(Country)
| lookup CountryDetails Country OUTPUT Region
| stats count(Rating) as NumberRatings by date_month Rating Region
| eventstats sum(NumberRatings) as TotalRatings by date_month Region
| eval PercentageRatings=round(NumberRatings/TotalRatings,3)
| where Rating=1 OR Rating=2
|eval monthNum=case(date_month="january",1,date_month="february",2,date_month="march",3,date_month="april",4,date_month="may",5,
date_month="june",6,date_month="july",7,date_month="august",8,date_month="september",9,date_month="october",10,date_month="november",11,
date_month="december",12)
| chart sum(PercentageRatings) as NegativeSentiment by monthNum Region
|sort 0 monthNum
|eval Month=case(monthNum=1,"January",monthNum=2,"February",monthNum=3,"March",monthNum=4,"April",monthNum=5,"May",
monthNum=6,"June",monthNum=7,"July",monthNum=8,"August",monthNum=9,"September",monthNum=10,"October",monthNum=11,"November",
monthNum=12,"December")|fields - monthNum
ORIGINAL:
index=webex_sentiment
| eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M")
| eval Country=upper(Country)
| lookup CountryDetails Country OUTPUT Region
| stats count(Rating) as NumberRatings by date_month Rating Region
| eventstats sum(NumberRatings) as TotalRatings by date_month Region
| eval PercentageRatings=round(NumberRatings/TotalRatings,3)
| where Rating=1 OR Rating=2
|eval date_month=case(date_month="january","1-January",date_month="february","2-February",date_month="march","3-March",date_month="april","4-April",date_month="may","5-May",date_month="june","6-June",date_month="july","7-July",date_month="august","8-August",date_month="september","9-September",date_month="october","10-October",date_month="november","11-November",date_month="december","12-December")
| chart sum(PercentageRatings) as NegativeSentiment by date_month Region
|sort 0 date_month
|rex field=date_month "-(?<date_month>\w+)"
@cmerriman, I think you will need to create fields like "01-January" etc to ensure 10 , 11 and 12 do not come after "1-January" after sort, since it will not be | sort num(date_month)
good catch! i even mentioned alphanumeric and then i go ahead and create an alphanumeric field! silly me.
Could it be significantly simplified if you use, to derive the date category information "01-January", some variation of ...
... | eval date_month=strftime(_time, "%m-%B") ...
See this run-anywhere example:
| makeresults
| eval date_month=strftime(_time, "%m-%B")
Which gives you a date_month
equal to, right now, 07-July
.
Refer to Splunk date and time format variables)