How to make a table with columns getting results for different time ranges?

[Taruchit]

Hi All,

I want to write a search which gives me total event counts for each host as per the time range picker. Additionally, I want to had two more columns which give event counts for each host in last 7 days and last 24 hours. 

My SPL is in the format: -

index="xxx" field1="dummy_value" field2="dummy_value"
|stats sparkline(sum(event_count)) AS sparkline, max(_time) AS _time, sum(event_count) AS "Total_Event_Count" BY field2, field3, field4
|table field2, sparkline, field3, field4

I tried using append command but it does not help me get proper results. 

Thus, I need your help to build the SPL.

Thank you

[ITWhisperer]

Please can you clarify:

is host one of field2, field3 or field4?

is the timepicker range going to be more than 7 days, or less than 24 hours or something else?

Can you give an example of what you want the result to look like?

[Taruchit]

Hi @ITWhisperer,

Thank you for your response.

Regarding your questions: -

is host one of field2, field3 or field4?

-> Yes, host is one of the fields in field2, field3, field4.

is the timepicker range going to be more than 7 days, or less than 24 hours or something else?

-> In the dashboard, default time range picker is 24 hours, however, user can change it to see the change of results for different panels as per their requirements.

Can you give an example of what you want the result to look like?

-> 

Host

Sparkline

field2

field3

Total_Event_Count

Total_event_count_in_last_7days

Total_event_count_in_last_24Hrs

Thank you

[Taruchit]

I got a solution to my problem in https://community.splunk.com/t5/Splunk-Search/merge-two-search-results/m-p/66449 

index="xxx" field1="dummy_data" field2="dummy_data"
|stats sparkline(sum(event_count)) AS sparkline max(_time) AS _time, sum(event_count) AS "Total_event_count" BY field3, field4
|append
[search index="xxx" field1="dummy_data" field2="dummy_data" earliest=-60m@m latest=now
|stats sparkline(sum(event_count)) AS sparkline max(_time) AS _time, sum(event_count) AS "1Hour_event_count" BY field3, field4]
|append
[search index="xxx" field1="dummy_data" field2="dummy_data" earliest=-24h@h latest=now
|stats sparkline(sum(event_count)) AS sparkline max(_time) AS _time, sum(event_count) AS "24Hours_event_count" BY field3, field4]
|stats values(*) AS * BY field4, _time
|table field4, sparkline, _time, Total_event_count, 24Hours_event_count, 1Hour_event_count

Please share if you can help with a more better and efficient SPL.

Thank you