topic Re: Create a summary table with usernames /last 7 days /last 30 days in Splunk Search

Create a summary table with usernames /last 7 days /last 30 days

raviteja029 — Mon, 11 Dec 2017 20:03:38 GMT

Hi Everyone,

I am trying to create a report where I am able to get the list of username's / number for calls for last 7 days but unable to add another field number for calls for last 30 days. the list should look something like this
i.e list of username's / number for calls for last 7 days/number for calls for last 30 days.

Re: Create a summary table with usernames /last 7 days /last 30 days

sbbadri — Mon, 11 Dec 2017 21:11:08 GMT

@raviteja029

try this,

your search earliest=-7d@d latest=0d@d | eval weeknum="Last 7 days" | append [ search your search earliest=-30d@d latest=0d@d | eval weeknum="Last 30 days" ] | chart count over weeknum by username

i hope this helps

Re: Create a summary table with usernames /last 7 days /last 30 days

raviteja029 — Mon, 11 Dec 2017 21:44:50 GMT

Hi ,

Thank you for the reply,
I kind of did few tweaks and was able to get the response but only last 7 days value is correct and for last 30 days value its coming some value,

Search -
My Search | eval weeknum="Last 7 days" |
append [ search My Search | eval weeknum="Last 30 days" ]
| chart count over CustomerName by weeknum

With this I am getting out as below -
CustomerName | Last 30 days | Last 7 days |
abc | 77 | 92385 |
def | 87 | 235235 |

Here Last 30 days value is incorrect

Re: Create a summary table with usernames /last 7 days /last 30 days

somesoni2 — Mon, 11 Dec 2017 21:57:38 GMT

Give this a try

your base search earliest=-30d@d 
| eval Last7days=if(_time>=relative_time(now(),"-7d@d"),1,0)
| stats sum(Last7days) as "number for calls for last 7 days" count as "number for calls for last 30 days" by username

Re: Create a summary table with usernames /last 7 days /last 30 days

raviteja029 — Mon, 11 Dec 2017 22:18:53 GMT

Hi,
Thank you very much.

The search worked pretty well but I am getting a little extra number in last 7 days, it's taking for last 8 day's looks like changing "-7d@d" to "-6d@d" got much closer but I am assuming the start time has some lag now, can you confirm the start time is from last min to 7 days ?

EX-
Getting 238,121 for last 7 days but actual no. 242,408

And for last 30 days is coming correct.

Re: Create a summary table with usernames /last 7 days /last 30 days

raviteja029 — Tue, 12 Dec 2017 23:24:55 GMT

Could you help me how to get the change in percentage for the results I get from a current week of calls to last week calls?

my Search earliest=-14d@d latest=-7d@d | eval weeknum="Last Week" |
append [ search my Search earliest=-7d@d latest=-1m@m | eval weeknum="Current Week" ] | chart count over CustomerName by weeknum