Chart by hour using multiple fields

[cglowjr]

I am having trouble charting some data by hour and consoleID. Below is the search I used. I can use the stats function to count by hour, but it doesn't show well in my dashboard. I am looking to have this same format, but use the field date_hour in the chart count function.

| chart count over pedestalName by date_hour, consoleID ? I know this doesn't work, but in my head this is what should work.

index="sg_log" host=PACSTAPP1 "" "OUTGATE" "COMPLETE" "SMT" NOT "TROUBLE_LANE" 
| xmlkv 
| eval consoleID=if(consoleID="AUTO","AUTO","MANUAL") 
| chart count over pedestalName by consoleID
| eval total=round(AUTO+MANUAL) 
| WHERE pedestalName IN ("21","22","23","24","25","26") 
| eval autogate%=round(AUTO/(AUTO+MANUAL)*100,2)

[to4kawa · Accepted solution]

index="sg_log" host=PACSTAPP1 "" "OUTGATE" "COMPLETE" "SMT" NOT "TROUBLE_LANE" 
| xmlkv 
| eval consoleID=if(consoleID="AUTO","AUTO","MANUAL") 
| eventstats count by date_hour consoleID pedestalName
| eval tmp=date_hour.":".consoleID
| xyseries pedestalName tmp count
| WHERE pedestalName IN ("21","22","23","24","25","26") 
| rename pedestalName as _pedestalName
| addtotals fieldname=total
| foreach "*:AUTO" [ eval sums_auto=mvappend(sums_auto,'<<FIELD>>')]
| streamstats window=1 sum(sums_auto) as sums_auto
| eval autogate%=round(sums_auto/total*100,2)
| rename _pedestalName as pedestalName
| fields - sums_auto

When you ask a question, ask for a detailed explanation from the beginning.

[cglowjr]

Thank you for the help. I am receiving an error message of

Error in 'eval' command: The 'mvexpand' function is unsupported or undefined.

[to4kawa]

sorry, I have a typo. mvexpand → mvappend
I correct answer.

[cglowjr]

Thank you so much! This is great!

[to4kawa]

there is no detail. no one can answer this.

[cglowjr]

I am trying to use the same query, but use the chart function to show the data by hour and consoleID (consoleID consists of AUTO and MANUAL)

This is what it looks like now.

pedestalName AUTO MANUAL total autogate%
21 256 31 287 89.20
22 270 29 299 90.30
23 242 37 279 86.74
24 231 34 265 87.17
25 281 26 307 91.53
26 269 19 288 93.40

I want it to look like this, with the hour extending throughout the day... 0700, 0800, 0900 etc

                                     0700                          0800 

pedestalName AUTO MANUAL AUTO MANUAL total autogate%
21 256 31 256 52 287 89.20
22 270 29 300 24 299 90.30
23 242 37 301 13 279 86.74
24 231 34 320 26 265 87.17
25 281 26 187 27 307 91.53
26 269 19 186 28 288 93.40