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
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
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
@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
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
Thanks for reply, I've moved the fillnull before and after transpose, it is not working.
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
I'm having result as below:
date_hour NULL
06:00 78.00
07:00 73.68