Splunk Search

Inefficient Query - Same Base Query, Multiple Time Ranges earliest -24h, -30d, -60d

splunkyj
Path Finder

I need some suggestions on how to make this query more efficient.  We would like distinct count of workstation by sitename - with 3 columns of the count that shows within the past 24 hours, 30 days, and 60 days.  

 

index=sample sourcetype="SAMPLE:DATA" earliest=-24h

| stats dc(workstation_name) AS "Past 24 Hours EUD Count" by sitename

| appendcols

[ search index=sample sourcetype="SAMPLE:DATA" earliest=-30d

| stats dc(workstation_name) AS "Past 30 Days EUD Count" by sitename ]

| appendcols

[ search index=sample sourcetype="SAMPLE:DATA" earliest=-60d

| stats dc(workstation_name) AS "Past 60 Days EUD Count" by sitename ]

| appendcols

[ search index=sample sourcetype="SAMPLE:DATA" earliest=-90d

| stats dc(workstation_name) AS "Past 90 Days EUD Count" by sitename ]

Labels (1)
0 Karma

nickhills
Ultra Champion

Try something like this

index=sample sourcetype="SAMPLE:DATA" earliest=-90d@d latest=@d
| bin _time span=1d
| eval timeframe=case(_time<relative_time(now(), "-60d@d"), "90d", _time>relative_time(now(), "-60d@d") AND _time<relative_time(now(), "-30d@d"), "60d", _time>relative_time(now(), "-30d@d"), "30d", 1=1, "Other")
| stats dc(workstation_name) as EUD by timeframe, sitename

 

If my comment helps, please give it a thumbs up!
0 Karma

splunkyj
Path Finder

Hi @nickhills , I would like to see the different count of time ranges in it's own column by sitename.  First column sitename, 2nd column count of the past 24 hours, 3rd column count of the past 30 days, 4th column count of the past 60 days, and 5th column count of the past 90 days. Thanks for your suggestion, I tried playing around with it - but I couldn't get the results I'm looking for. Let me know if you have another suggestion, I truly appreciate it. 

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 ...