topic Re: Display Graph Fields in Chronological Order in Splunk Dev

Display Graph Fields in Chronological Order

mhtedford — Thu, 27 Jul 2017 20:34:23 GMT

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

Re: Display Graph Fields in Chronological Order

cmerriman — Thu, 27 Jul 2017 20:39:47 GMT

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+)"

Re: Display Graph Fields in Chronological Order

niketn — Fri, 28 Jul 2017 09:08:20 GMT

@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)

Re: Display Graph Fields in Chronological Order

cmerriman — Fri, 28 Jul 2017 11:59:16 GMT

good catch! i even mentioned alphanumeric and then i go ahead and create an alphanumeric field! silly me.

Re: Display Graph Fields in Chronological Order

Richfez — Fri, 28 Jul 2017 13:51:46 GMT

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)