Splunk Search

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

Taruchit
Contributor

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

Labels (5)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

Taruchit
Contributor

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?

-> 

HostSparklinefield2field3Total_Event_CountTotal_event_count_in_last_7daysTotal_event_count_in_last_24Hrs

 

Thank you

0 Karma

Taruchit
Contributor

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...