How to add another column from the same index with stats function?

[Neel881]

Hello all,

How to add  another column from the same index with stats function?

| makeresults count=1 | addinfo | eval days=mvrange(info_min_time, info_max_time, "1d") | mvexpand days | eval _time=days
| join type=outer _time [ search index="*appevent" Type="*splunk" | bucket _time span=day | stats count by _time]
| rename count as "Total"
| eval "New_Date"=strftime(_time,"%Y-%m-%d")
| table "New_Date" "Total"| fillnull value=0 "Total"

I have used join because I need 30 days data even with 0. Please suggest. 

[richgalloway]

You can use append instead of join.

| makeresults count=1 
| addinfo 
| eval days=mvrange(info_min_time, info_max_time, "1d") 
| mvexpand days 
| eval _time=days, count=0
| append [ search index="*appevent" Type="*splunk" 
  | bucket _time span=day
  | stats count by _time ]
| stats max(count) as Total by _time
| eval "New_Date"=strftime(_time,"%Y-%m-%d")
| table "New_Date" "Total"

Or you can let timechart fill in the zeros.

index="*appevent" Type="*splunk" 
| timechart span=1d count as Total by _time
| eval "New_Date"=strftime(_time,"%Y-%m-%d")
| table "New_Date" "Total"

[Neel881]

Thank you for your response.

I need to add another column from the same index ('index="*appevent" Type="*splunk" ). Column name is 'Type'. My question is how to add column 'Type' with the existing query?

Expecting output- 

| makeresults count=1 
| addinfo 
| eval days=mvrange(info_min_time, info_max_time, "1d") 
| mvexpand days 
| eval _time=days, count=0
| append [ search index="*appevent" Type="*splunk" 
  | bucket _time span=day
  | stats count by _time ]
| stats max(count) as Total by _time
| eval "New_Date"=strftime(_time,"%Y-%m-%d")
| table "New_Date" "Total"

[richgalloway]

The stats command is a transforming command so it discards any fields it doesn't produce or group by.  Add new fields to stats to get them in the output.

| makeresults count=1 
| addinfo 
| eval days=mvrange(info_min_time, info_max_time, "1d") 
| mvexpand days 
| eval _time=days, count=0
| append [ search index="*appevent" Type="*splunk" 
  | bucket _time span=day
  | stats count by _time, Type ]
| stats max(count) as Total by _time, Type
| eval "New_Date"=strftime(_time,"%Y-%m-%d")
| table "New_Date" "Total" Type

[ITWhisperer]

Have you tried this: (timechart uses earliest and latest (info_min_time and info_max_time respectively) and should fill in the missing days automatically)

index="*appevent" Type="*splunk" 
| timechart span=1d count as "Total"
| eval "New_Date"=strftime(_time,"%Y-%m-%d")
| table "New_Date" "Total"

[Neel881]

Thank you for your response.

I getting I need to add another column from the same index ('index="*appevent" Type="*splunk" ). Column name is 'Type'. My question is how to add column 'Type' with the existing query?

Expecting output- 

[ITWhisperer · Accepted solution]

index="*appevent" Type="*splunk" 
| timechart span=1d count as "Total" by Type
| eval "New_Date"=strftime(_time,"%Y-%m-%d")
| untable New_Date Type Total

[Neel881]

Hi, 

How to add/join another column from the same search? Phase is the another column in the same index.

index="*appevent" Type="*splunk" 
| timechart span=1d count as "Total" by Type
| eval "New_Date"=strftime(_time,"%Y-%m-%d")
| untable New_Date Type Total

Pls suggest

[Neel881]

Its working thank you so much!