topic Re: eventstats function issue in Splunk Search

eventstats function issue

guruwells — Fri, 27 May 2016 13:44:02 GMT

Hi,
For retriving data from iis logs, I have used various eval statements, eventstats, and stats functions.
When I am using eventstats in my query, I am seeing different results than when I have not used eventsttats in query (which gives me proper results).

This is my search:

index=main sourcetype=iis
| eval aspx_time_taken=if(cs_uri_stem LIKE "%aspx%" AND sc_status!="401",time_taken,null())
| eval csuri_time_taken=if(lower(cs_uri_stem)="/pages/default.aspx" AND sc_status!="401",time_taken,null())
| eval page_time_taken=if(lower(cs_uri_stem)="/view/pages/default.aspx" AND sc_status!="401",time_taken,null())
| eval time_taken_not401=if(sc_status!="401",time_taken,null())
| eval s_computername_all=if(cs_uri_stem LIKE "%aspx%" ,s_computername,null())
| eval s_computername_4s=if(cs_uri_stem LIKE "%aspx%" AND time_taken > 4000 ,s_computername,null())
|  eval s_computername_25s=if(cs_uri_stem LIKE "%aspx%"  AND time_taken > 2500,s_computername,null())
| eval u_name =replace(cs_username, "0#","")
| eval u_name1= replace(u_name, ".w|","")
|eval u_name2=replace(u_name1,"\|","")
| eval u_name2_503=if(sc_status="503",u_name2,null())
| eval s_computername_503=if(sc_status="503",s_computername,null())
|eval RPS=strftime(_time,"%Y-%m-%d %H:%M:%S")
|eval RPS_Not401= if(sc_status!="401", RPS,null())

    |eventstats count(RPS) as RPS_Count by RPS
    |eventstats count(RPS_Not401) as RPS_Not401_Count by RPS_Not401

| eval hitsfoursecond=if(time_taken > 4000,1,0)
| eval hitstwopointfiveseconds=if(time_taken > 2500,1,0)
| eval u_name2_yhp=if(lower(cs_uri_stem)="/view/pages/default.aspx",u_name2,null())
| eval s_computername_yhp=if(lower(cs_uri_stem)="/view/pages/default.aspx",s_computername,null())
|stats avg(RPS_Count) as "Avg. Requests Per Second"
           max(RPS_Count) as "Max Requests Per Second"
           avg(RPS_Not401_Count) as "Avg. Requests Per Second (excl 401)"
           max(RPS_Not401_Count) as "Max Requests Per Second (excl 401)"
           avg(aspx_time_taken) as "Avg. Response Time .aspx (ms)"
           avg(time_taken_not401) as "Avg. Response Time All (ms)"
           count(eval(csuri_time_taken>4000)) as "PageViewsfoureconds"
           count(eval(csuri_time_taken>2500)) as "PageViews_2point5_seconds"
           avg(page_time_taken) as "Standard Page Avg. Response Time (ms)"
           count(page_time_taken) as "Standard Page Views" avg(csuri_time_taken) as "Page Avg. Response Time (ms)"
           count(csuri_time_taken) as "Pageviews" count(s_computername_all) as "No_of_aspx_Hits"
           count(s_computername_4s) as "No_of_aspx_Hits_4_seconds"
           count(s_computername_25s) as "No_of_aspx_Hits_25_seconds" dc(u_name2_503) AS "Unique User 503"
           count(s_computername_503) as "Total 503 Errors" sum(hitsfoursecond) as "hitsfoursecond"
           sum(hitstwopointfiveseconds) as "hitstwopointfiveseconds" dc(u_name2) AS "Unique Users"
           count(s_computername) as "ElementsHits" dc(u_name2_yhp) as "YHP Unique User"
           count(s_computername_yhp) AS "YHP Elements/Hit"
|eval resultset= (hitsfoursecond/ElementsHits) *100
|eval resultset1=(hitstwopointfiveseconds/ElementsHits)*100
|eval resultset2=(PageViewsfoureconds/Pageviews)*100
|eval resultset3=(PageViews_2point5_seconds/Pageviews)*100
|eval resultset4=(No_of_aspx_Hits_4_seconds/No_of_aspx_Hits)
|eval resultset5=(No_of_aspx_Hits_25_seconds/No_of_aspx_Hits)
|eval resultset6=(PageViewsfoureconds/hitsfoursecond)
|rename resultset as "% Hits > 4 seconds"
                 resultset1 as "% Hits > 2.5 seconds"
                 hitsfoursecond as "# of Hits > 4 seconds"
                 hitstwopointfiveseconds as "# of Hits > 2.5 seconds"
                 resultset2 as "% Page Views > 4 seconds"
                 resultset3 as "% Page Views > 2.5 seconds"
                 PageViews as "Page Views"
                 PageViewsfoureconds as "Page Views > 4 seconds"
                 PageViews_2point5_seconds as "Page Views > 2.5 seconds"
                 No_of_aspx_Hits as "# of .aspx Hits"
                 No_of_aspx_Hits_4_seconds as "# of .aspx Hits > 4 seconds"
                 No_of_aspx_Hits_25_seconds as "# of .aspx Hits > 2.5 seconds"
                 resultset4 as "% .aspx Hits > 4 seconds"
                 resultset5 as "% .aspx Hits > 2.5 seconds"
                 resultset6 as "% Redirect Hits > 4 secs to Overall Hits > 4 secs"

Re: eventstats function issue

somesoni2 — Fri, 27 May 2016 14:26:28 GMT

What are you trying to calculate using eventstats? On lighter note, if you're getting proper result without eventstats, just don't use it 😉

Re: eventstats function issue

woodcock — Fri, 27 May 2016 14:57:41 GMT

I will demonstrate your problems by an example.

Let us imagine that you have these events:

RPS
  a
  b
  b
  c
  c
  c

After your call to |eventstats count AS RPS_Count BY RPS, you will have this:

RPS    RPS_count
  a            1
  b            2
  b            2
  c            3
  c            3
  c            3

After your call to |stats avg(RPS_Count) as "Avg. Requests Per Second", you will have this:

"Avg. Requests Per Second"
(1+2+2+3+3+3)/6=2.3

Surely you can see that this is utterly nonsensical.

To top it off, you have not done any math with your timeframe so you cannot possibly be accurate in naming this Per Second!

Re: eventstats function issue

guruwells — Mon, 30 May 2016 08:55:18 GMT

Thanks for the mail. I am not able to merge queries without eventstats. Even I can use multiple stats to get the result set, but problem is if we use multiple stats the result set changing. Because stats will take it from interim table. If I use eventstats, I can able to merge the statement, but result count is different. This is what I am exploring.

How we get exact result.

Sorry I typed big message.

Re: eventstats function issue

guruwells — Mon, 30 May 2016 08:57:03 GMT

Hi,
Your explanation is absolutely right. But when I using eventstats function in query, the result set is varying.

Re: eventstats function issue

woodcock — Mon, 30 May 2016 13:04:06 GMT

Show me your sample data, then show me a mockup of your desired output with a little bit of plain math (not SPL Splunk commands) and I will try to get you a full solution.

Re: eventstats function issue

guruwells — Mon, 30 May 2016 14:54:20 GMT

Thanks for the mail.

I am taking count(_time) and max(_time) and computer count. Since I need the timespan, I use eventstats function to get it.
This is my query:

index=tmportal2010prod sourcetype=iis
| eval RPS=strftime(_time,"%Y-%m-%d %H:%M:%S")
| eval RPS_Not401= if(sc_status!="401", RPS,null())
| eventstats count(RPS_Not401) as RPS_Not401_Count by RPS_Not401_Count
| stats avg(RPS_Not401_Count) as "Avg. Requests Per Second" 
           max(RPS_Not401_Count) as "Max Requests Per Second"
           count(s_computername) as "ElementsHits"

My Result set is:

avg(count)       Max(count)     computer_Count
100                     150              62000

Without eventstats:

index=tmportal2010prod sourcetype=iis
| eval RPS=strftime(_time,"%Y-%m-%d %H:%M:%S")
| eval RPS_Not401= if(sc_status!="401", RPS,null())
| stats avg(RPS_Not401_Count) as "Avg. Requests Per Second"
           max(RPS_Not401_Count) as "Max Requests Per Second"
           count(s_computername) as "ElementsHits"

avg(count)     max(count)     computer_count
0                      0             5058164

When I call eventstats, my count disappears.

Please help me here.

Re: eventstats function issue

woodcock — Mon, 30 May 2016 16:48:52 GMT

I will answer your question but I have grave concern that what you are doing is not at all what you are intending to do. Your field names have count in the names but you are claiming that these are actually time values. I find that very hard to believe so I think you are going to end up with absolute garbage.

In any case, you should be using evenstats to create a NEW field name, not to overwrite an existing one (which is causing your other values to drop to zero). Try this:

 ... | eventstats count(RPS_Not401) AS Some_Other_Count BY RPS_Not401_Count

Re: eventstats function issue

guruwells — Mon, 30 May 2016 17:38:23 GMT

Thanks for your suggestion. I am getting the proper count of computer_count, but I am getting values of avg(count) and max(count) is 0. At the same time I am looking avg(count) and max(count) as well. I will try the options meantime.

Re: eventstats function issue

woodcock — Mon, 30 May 2016 18:43:00 GMT

You really, really, REALLY, REALLY need to show the RAW events and explain what it is that you are trying to do (include a mockup of the final visualization). I am reasonably confident that you are WAY off track and should back all the way up to the beginning.

Re: eventstats function issue

guruwells — Tue, 29 Sep 2020 09:49:50 GMT

sorry. In simple scenario I will explain what I am doing here.

Query which I am executing here: example:
index=tmportal2010prod sourcetype=iis |eval RPS=strftime(_time,"%Y-%m-%d %H:%M:%S")| stats count by RPS
Here I am converting built-in timestamp into my required timestamp and then taking "_time" count using stats function.
Result will be looks like:

RPS↕ count↕

2016-05-30 22:32:00 69

2016-05-30 22:32:01 81

2016-05-30 22:32:02 48

2016-05-30 22:32:03 99

Using result what I am trying to say is: "2016-05-30 22:32:00" is repeated 69 times.
Now using those counts, I am trying to retrieve average count of _ time and max (count) of _time.

example:
69+ 81+48+99/4= 74.24
avg(count) is 74.24 and "max" value is number of times repeated one is 99.

If I am executing stats function individually I am getting result without any issue. If I want combine these statements into main query, since I will use multiple stats the result set will vary ( stats will work with interim table).
That's the reason I have used eventstats to get the result in main query, but even that result set also giving different result set.

Is that clear now woodcock or you need more information on this?