topic Re: Inputlookup trend for 24hrs,7 days showing same graph in Splunk Search

Inputlookup trend for 24hrs,7 days showing same graph

gopiven — Wed, 30 Sep 2020 02:48:48 GMT

Hi experts!

Since I am new to Splunk, I understand that we cannot use a time chart with inputlookup(?).
But I am using (outputlookup + scheduled report) concept to run every hr and using that as inputlookup in the dashboard to draw the trends for 24 hrs and 7 days.

Here the format of the lookup:

Inputlookup name: customerslog.csv
Userid Clientid date_year date_month date_mday date_wday date_hour date_minute channel
abc1 clientid1 2019 november 1 friday 17 55 ONLINE
abc2 clientid2 2019 november 1 friday 19 25 MOBILE
abc3 clientid1 2019 october 31 thursday 11 44 ONLINE
abc4 clientid1 2019 november 1 friday 13 26 MOBILE
abc5 clientid2 2019 october 31 thursday 12 4 MOBILE
abc6 clientid2 2019 november 2 saturday 16 23 MOBILE
abc7 clientid1 2019 november 1 friday 18 2 MOBILE
abc8 clientid2 2019 november 1 friday 17 53 MOBILE
abc9 clientid2 2019 october 31 thursday 11 47 MOBILE
abc10 clientid2 2019 november 1 friday 20 14 ONLINE
abc11 clientid1 2019 october 30 wednesday 19 10 MOBILE
abc12 clientid2 2019 november 1 friday 18 3 ONLINE
abc13 clientid2 2019 november 1 friday 12 1 MOBILE
abc14 clientid2 2019 november 1 friday 19 26 MOBILE
abc15 clientid1 2019 november 1 friday 11 59 ONLINE
abc16 clientid2 2019 october 31 thursday 11 45 MOBILE
abc17 clientid1 2019 october 31 thursday 12 8 MOBILE
abc18 clientid2 2019 october 30 wednesday 11 56 MOBILE
abc19 clientid1 2019 october 30 wednesday 16 57 ONLINE

I would like to show the clientid volume as AVERAGE VOLUME FOR LAST 7 days and Userid volume for last 24 hrs with respect to current time with interval (span=15m) by using the same above inputlookup.

To show the average of Client volume across the channel for last 7 days :

|inputlookup customerslog.csv | dedup clientid | stats count(clientid) AS CLIENTS by date_hour, date_wday, channel | chart eval(round(avg(CLIENT),0)) AS "AVERAGE NO.OF CLIENTS LOGGED IN" over date_hour by Channel

To show the trend for last 24 hrs:

|inputlookup customerslog.csv | stats count(Userid) AS USERSONLY by date_hour, date_wday, channel | chart eval(round(avg(USERSOLNY),0)) AS "AVERAGE NO.OF USERS LOGGED IN" over date_hour by Channel --> this is giving same result similar to 7 days trend
|inputlookup customerslog.csv | timechart avg(USERONLY) span=15m --> Not Working

|inputlookup customerslog.csv where earliest=-24h@h latest=now | timechart sum(Userid) as USERSONLY by channel --> Not working

|inputlookup customerslog.csv | timechart span=15m sum(Userid) As "USERSONLY" by channel --> Not working

Should I really use earliest and now for the 24hrs trend?

Kindly help me with this please and share the search if you have?

Re: Inputlookup trend for 24hrs,7 days showing same graph

arjunpkishore5 — Mon, 04 Nov 2019 12:42:44 GMT

timechart requires _time variable. So you need to convert your date fields to an epoch timestamp.

Here's how

| inputlookup customerslog.csv
| strcat date_year "-" date_month "-" date_mday " " date_hour ":" date_minute timestamp
| eval _time=strptime(timestamp, "%Y-%B-%d %H:%M")
| timechart avg(USERONLY) span=15m

The important part is this

| strcat date_year "-" date_month "-" date_mday " " date_hour ":" date_minute timestamp
| eval _time=strptime(timestamp, "%Y-%B-%d %H:%M")

In this, you are concatenating your different date columns to a single timestamp column and then your're converting that timestamp to an epoch timestamp and assigning it to _time using strptime. I've just used one of your searches as an example. You need to apply this to all your other searches.

If you need to build a different format string for your strptime, you can use this - https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/Commontimeformatvariables

Hope this helps
Cheers

Re: Inputlookup trend for 24hrs,7 days showing same graph

gopiven — Wed, 06 Nov 2019 03:39:54 GMT

Hi Arjun

Thanks for your response!
However the trend for last 24hrs showing similar to 7 days ( Mean even If I opt the last 24hrs in time window)
Please let me know how to pickup the last 24hrs data from the input lookup.

Thanks