topic Re: Chart examples in Splunk Search

Chart examples

chintan_shah — Tue, 23 May 2017 19:44:15 GMT

Hi,
I have the data in the below format i.e i have calculated base on Type A,B,C per month and the data looks like
Jan 2017,A, 1,2
Jan 2017,B, 3,4
Jan 2017,C, 5,6
Feb 2017,A, 7,8
Feb 2017,B, 10,12
Feb 2017,C, 13,16
Mar 2017,A, 1,2
Mar 2017,B, 3,4
Mar 2017,C, 5,6

I want to convert this data in to the below format:
Jan 2017,Feb 2017,Mar 2017
A, 1,2 7,8 1,2

B, 3,4 10,12 3,4

C, 5,6 13,16 5,6

is there any simple method to do so?
Thanks for your help.

Re: Chart examples

woodcock — Tue, 23 May 2017 20:16:45 GMT

Like this:

| makeresults 
| eval raw="Jan 2017,A, 1,2::Jan 2017,B, 3,4::Jan 2017,C, 5,6::Feb 2017,A, 7,8::Feb 2017,B, 10,12::Feb 2017,C, 13,16::Mar 2017,A, 1,2::Mar 2017,B, 3,4::Mar 2017,C, 5,6" 
| makemv delim="::" raw 
| mvexpand raw
| rename raw AS _raw
| rex "^(?<Month>[^,]+),(?<Type>[^,]+),(?<Value>.*)$"
| fields - _*

| rename COMMENT AS "Everything above fakes your data; everything below is your solution"

| chart list(Value) AS Value OVER Type BY Month

At this point, you may be interested in my (as yet unaccepted) answer here:

https://answers.splunk.com/answers/522959/cannot-sort-dynamic-column-in-date-format.html#answer-522684

Re: Chart examples

chintan_shah — Tue, 29 Sep 2020 14:14:11 GMT

Hi Woodcock,

the above solution will works when we have only three components but in my case i have 4 components such as 1 and 2 which wont work.

I have the below query
index=guistats_summary DIVISION_CD=E GUI_Enable=Y COUNTRY_CD="EE" OR COUNTRY_CD="LT" OR COUNTRY_CD="LV" OR COUNTRY_CD="LB" | bin span=1mon _time | eval GUI_Count=if(GUI_IND="Y",Total_Count,0), Non_GUI_Count=if(GUI_IND="N",Total_Count,0)|rename _time as Date|eval Date=strftime(Date,"%b-%y")|chart sum(GUI_Count) as GUI_Count,sum(Non_GUI_Count) as Non_GUI_Count OVER COUNTRY_CD By Date
which give the below results

COUNTRY_CD GUI_Count: Feb-17 GUI_Count: Jan-17 GUI_Count: Mar-17 Non_GUI_Count: Feb-17 Non_GUI_Count: Jan-17 Non_GUI_Count: Mar-17

EE 344 237 249 262 304 251

LB 2576 2173 2800 251 9 2

LT 367 347 411 292 489 354

LV 587 449 449 387 108 75

is it possible to arrange the column so that GUI_Count and Non GUI for same months comes together so that i dont need to run tables /fields command?

Re: Chart examples

woodcock — Fri, 26 May 2017 01:37:28 GMT

I do not understand at all. It might help if you just extend the original fake data to be "four components" as you put it (which I also do not understand). It would be even better if you modify my "fakes your data" stuff so that it generates fake data more like you are saying you need.

Re: Chart examples

jiaqya — Wed, 10 Oct 2018 08:33:41 GMT

|table Value _time host counter

value _time host counter
42 somedate test idle
43 somedate test notidle
52 anotherdate test idle
55 anotherdate test notidle

i want to see below, how to do it with chart..

host _time idle notidle
test somedate 42 43
test anotherdate 52 55