Can you help me answer a question with the chart command?

[krusovice]

Hi there,

I have this query formed and I can't the get expected result, but it's very close to what I want. The result of chart followed by transpose command is looking good, but under "column", I'm getting the value as NULL instead of "sourcetype". May I know how to fix it?

index=* sourcetype=*
| eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00")
| rex  "Total1\:\s(?<total1>[\d+]+)" 
| rex  "Total2\:\s(?<total2>[\d+]+)" 
| stats count(total1) count(total2) by date_hour
| eval granttotal = total1+total2
| fields - _time
| chart values(granttotal) over date_hour by sourcetype
| transpose header_field=date_hour

column       02:00         03:00         04:00
NULL           96.64         80.00         89.70

[woodcock · Accepted solution]

You have many mistakes. See this run-anywhere fixed example:

index=_* 
| eval date_hour=strftime(_time, "%H") 
| eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00") 
| eval total1=random(), total2=random()
| stats sum(total1) AS total1 sum(total2) AS total2 BY date_hour sourcetype
| eval grand_total = total1 + total2 
| chart values(grand_total) OVER date_hour BY sourcetype 
| transpose header_field=date_hour

Which means your fixed search should be:

index=* sourcetype=*
| eval date_hour=strftime(_time, "%H") 
| eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00")
| rex  "Total1\:\s(?<total1>[\d+]+)" 
| rex  "Total2\:\s(?<total2>[\d+]+)" 
| stats sum(total1) AS total1 sum(total2) AS total2 BY date_hour sourcetype
| eval grand_total = total1 + total2 
| chart values(grand_total) OVER date_hour BY sourcetype
| transpose header_field=date_hour

[kamlesh_vaghela]

@krusovice

Can you please try this?

index=* sourcetype=* 
| eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00") 
| rex "Total1\:\s(?<total1>[\d+]+)" 
| rex "Total2\:\s(?<total2>[\d+]+)" 
| stats count(total1) as total1, count(total2) as total2 by date_hour,sourcetype 
| eval granttotal = total1+total2 
| fields - _time 
| chart values(granttotal) over date_hour by sourcetype | transpose header_field=date_hour

I did some minor correction in your search and added sourcetype in stats. I think following search not getting sourcetype.

Please try and let me know.

Thanks

[inventsekar]

fillnull works fine with chart command.. not sure about transpose. anyhow, lets try:

index=* sourcetype=*
| eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00")
| rex "Total1\:\s(?[\d+]+)"
| rex "Total2\:\s(?[\d+]+)"
| stats count(total1) count(total2) by date_hour
| eval granttotal = total1+total2
| fields - _time
| chart values(granttotal) over date_hour by sourcetype
| transpose header_field=date_hour
| fillnull value=sourcetype

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fillnull

[krusovice]

Thanks for reply, I've moved the fillnull before and after transpose, it is not working.

[inventsekar]

for this search, what output you get(without the transpose):
index=* sourcetype=*
| eval date_hour=if(len(date_hour)==1,"0".date_hour.":00",date_hour.":00")
| rex "Total1\:\s(?[\d+]+)"
| rex "Total2\:\s(?[\d+]+)"
| stats count(total1) count(total2) by date_hour
| eval granttotal = total1+total2
| fields - _time
| chart values(granttotal) over date_hour by sourcetype

[krusovice]

I'm having result as below:
date_hour NULL
06:00 78.00
07:00 73.68