Dashboards & Visualizations

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

Path Finder

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

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

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

Path Finder

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

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

Path Finder

Thanks again!

0 Karma

Path Finder

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