Dashboards & Visualizations

How to create a timechart that runs a search query every X minutes with a time range of Y?

GIS_M97
Explorer

Hello all, a Splunk newbie here.

For the company that I work for we want to monitor some licenses that are being used. The logs show the user the type of license that they have. The type can be for the most part IN (not using) or OUT (using the license) and sometimes DENIED but that is not of interest currently.

Because sometimes users forget to log off we want to take this into account by looking at the data over the past 2 weeks. I count the most recent type for each user and focus if the type is OUT. Because this means that the user is using a license. This gives a count of OUT over the past 2 weeks, which is pretty accurate with what the license manager shows.

This count of OUT over the past 2 weeks is needed to be shown every 5 minutes on a (time)chart. So, is it possible to have a (time)chart that runs a count over the past 2 weeks every 5 minutes?


For the query I have: base search | dedup 1 user sortby -_time | table user type _time | search type=out

This gives me only the users that have a type OUT, which means these are the ones that are using a license. Again, I would like to count the number of OUTS these past 2 weeks and have that number calculated every 5 minutes and shown on a (time)chart. I have tried loads of stuff (from other posts) but I did not manage to get it to work.

There already is a workaround where we use an ETL tool with the Splunk API as middleware, but I thought there should be a more efficient way to do it.

If any more info is needed I (hopefully) can provide that,

Thanks in advance,

M.

Labels (2)
0 Karma
1 Solution

GIS_M97
Explorer

So, I have been working on your proposals yesterday and today. I finally got it working. Your proposed query did not work, unfortunately. I did manage to create a report that runs every 5 minutes and calculate the count for the past 2 weeks. This is stored in the summary index and from there I create the timechart.  I had some trouble with the events being stored with a timestamp of 2 weeks earlier.  I managed to get that to work by adding | eval _time=now() at the end of my report search statement and that got me the correct timestamp in the summary index. 

 

Anyway, thank you for your help, I cannot accept your post as the solution since there is also the query you proposed but the summary index was the way to go!

View solution in original post

0 Karma

GIS_M97
Explorer

So, I have been working on your proposals yesterday and today. I finally got it working. Your proposed query did not work, unfortunately. I did manage to create a report that runs every 5 minutes and calculate the count for the past 2 weeks. This is stored in the summary index and from there I create the timechart.  I had some trouble with the events being stored with a timestamp of 2 weeks earlier.  I managed to get that to work by adding | eval _time=now() at the end of my report search statement and that got me the correct timestamp in the summary index. 

 

Anyway, thank you for your help, I cannot accept your post as the solution since there is also the query you proposed but the summary index was the way to go!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Splunk will process a pipeline of events, usually from an index search. Do you have events every five minutes when a user is using a licence or only events when they start using the licence and sometimes when they stop using the licence?

0 Karma

GIS_M97
Explorer

Yes, when a user is using a license or handing it back in that is all logged and onboarded. Thanks to the dedup I get the latest status of a user. But it basically comes down to the fact to fire a count of the past two weeks every 5 minutes. 

If there are no new events that just means that the status hasn't changed of the usage, which is fine.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You could set up a report that runs every 5 minutes looking back over the last 2 weeks (probably would need to convert that to minutes - 60*24*14) and store the results in a summary index, which you can then use as the basis for your timechart.

Or you could do something like this

<your index search over last 2 weeks>
| sort 0 _time
| timechart span=5m usenull=f useother=f latest(state) by user
| filldown
| foreach *
    [| eval "<<FIELD>>"=if('<<FIELD>>'!="OUT",null(),'<<FIELD>>')]
| untable _time user state
| timechart span=5m count

where state is either "OUT" or something else

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