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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...