Dashboards & Visualizations

Need to add a token so "where" clause updates with a different time range

spluzer
Communicator

Hey spunksters,

My query below shows hosts/sourcetypes that have been silent for a week. I was wondering if there was a way to add a token to the where clause (currently set at one week) so that the user could update that query to show hosts/sourcetype that have been silent for a different time period ??? Any help is musch appreciated !

| tstats latest(_time) as lt by host sourcetype 
| eval NOW=now() 
| eval difftime=NOW-lt 
| rangemap field=difftime "0 - 60 Min"=0-3600 "1 - 24 Hours"=3601-86400 "1-7 Days"=86401-604800 default="Greater than 1 Week"

| eventstats count(host) as tots_hosts by sourcetype
| eventstats count(sourcetype) as tots_st by host
| where difftime >= 604801
| eventstats count(host) as ghost_hosts  by sourcetype
| eval percent_ghost_host = (ghost_hosts / tots_hosts) * 100
| eventstats count(sourcetype) as ghost_st by host
| eval percent_ghost_st = (ghost_st / tots_st) * 100
| dedup sourcetype
| table host sourcetype difftime range percent_ghost_host percent_ghost_st tots_hosts ghost_hosts tots_st ghost_st
Tags (4)
0 Karma
1 Solution

aberkow
Builder

So you can use the relative_time command: https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/DateandTimeFunctions#relative_tim..., which takes the difference between two time frames, and generate a time you want to check if there are logs later than.

In this sense, you can check for when lt (latest time) is greater than whatever time frame you're interested in based on the token. For example, your token can be a dropdown of "day", "week", "month" with corresponding values of 1, 7, 30, and the search could be something like:

| tstats latest(_time) as lt by host sourcetype
| eval timeLastSeenCheck=relative_time(now(), "-{tokenValue}d@d")
| where lt < timeLastSeenCheck
...other stuff from your search

This will allow you to input the number of days, or you can use a different time modifier found here: https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/SearchTimeModifiers along with token inputs to specify this.

Hope this helps (if so, please accept/upvote the answer)!

View solution in original post

aberkow
Builder

So you can use the relative_time command: https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/DateandTimeFunctions#relative_tim..., which takes the difference between two time frames, and generate a time you want to check if there are logs later than.

In this sense, you can check for when lt (latest time) is greater than whatever time frame you're interested in based on the token. For example, your token can be a dropdown of "day", "week", "month" with corresponding values of 1, 7, 30, and the search could be something like:

| tstats latest(_time) as lt by host sourcetype
| eval timeLastSeenCheck=relative_time(now(), "-{tokenValue}d@d")
| where lt < timeLastSeenCheck
...other stuff from your search

This will allow you to input the number of days, or you can use a different time modifier found here: https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/SearchTimeModifiers along with token inputs to specify this.

Hope this helps (if so, please accept/upvote the answer)!

spluzer
Communicator

Thanks aberkow! It worked pretty well. Any thoughts on how to make it more dynamic? for example limit the search results for no shows past 24 hours, but not more than 48 hours. Kind of like this,

| where lt < timeLastSeenCheck AND timeLastSeenCheck > 2*86400

but without the hardcoded cut off point (2*86400)??? Thanks again! If you prefer, I can repost the question separately..not sure what the forum etiquette is...

Also, for everyone else...

USED THIS LINK TO SET UP THE DROPDOWN*

https://answers.splunk.com/answers/516336/need-help-in-populating-the-time-ranges-in-dropdow.html

0 Karma

aberkow
Builder

Use a second token? Create a new variable with an eval statement and have the where clause be something like this?

| where earliestTime < lt AND lt < latestTime

I don't know what best practices are for reposting...this is tiny enough that I think it's fine, but always happy to get more karma points if you want a more full answer/see if other people have different thoughts. I typically go with the two token input model though.

0 Karma

spluzer
Communicator

Thanks again!

0 Karma

spluzer
Communicator

I couldn't quite figure out how to implement what you suggested, so I started a new thread with the xml laid out. That way you can get the points if you want to clarify. link to new question:

https://answers.splunk.com/answers/780522/need-to-add-a-second-token-to-narrow-the-dropdown.html?min...

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