Splunk Search

How can I improve the accuracy and performance of my search that uses appendcols multiple times?

New Member

Hi All,

I am writing various Splunk searches to get result set from iis logs. For each search, I have different where conditions and custom conditions. I am using the appendcols command to combine the searches and display it in a table format.

Now the issue is: When I am running a long search, it's taking a very long time and results are not matching within individual Splunk search statements. I am thinking this is because of subsearch timeout limitations. Please provide me a better solution for my search below:

index=main sourcetype=iis| where sc_status!="401" AND cs_uri_stem LIKE "%aspx%" |stats avg(time_taken) as "Avg. Response Time .aspx (ms)"|appendcols[search index=main sourcetype=iis |where sc_status!="401" |stats avg(time_taken) as "Avg. Response Time All (ms)"]|appendcols[search index=main sourcetype=iis |where sc_status!="401" |stats avg(time_taken) as "Avg. Response Time All (ms)"]|appendcols[search index=main sourcetype=iis |eval csuri=lower(cs_uri_stem)| where csuri="/pages/default.aspx" AND  sc_status!="401"|stats  count(eval(time_taken>4000)) as "Page Views > 4 seconds" count(eval(time_taken>2500))  as "Page Views > 2.5 seconds" ]|appendcols[search index=main sourcetype=iis |eval csuri=lower(cs_uri_stem)| Where csuri="/view/pages/default.aspx" AND  sc_status!="401"|stats avg(time_taken) as "Page Avg. Response Time (ms)", count as "Page views"]| appendcols[search index=main sourcetype=iis| eval csuri=lower(cs_uri_stem)| eval csuri= lower(cs_uri_stem)| where cs_uri_stem LIKE "%aspx%"| stats count(s_computername) as "# of .aspx Hits"]|appendcols[search index=main sourcetype=iis|where time_taken > 4000 AND cs_uri_stem LIKE "%aspx%"|stats count(s_computername) as "# of .aspx Hits > 4 seconds"] |appendcols[search main=index sourcetype=iis|where time_taken > 2500 AND cs_uri_stem LIKE "%aspx%" |stats count(s_computername) as "# of .aspx Hits > 2.5 seconds"]|appendcols[search index=main sourcetype=iis|where sc_status!="401" AND cs_uri_stem LIKE "%aspx%" |stats avg(time_taken) AS "Avg. Response Time .aspx (ms)"]| appendcols[search index=main sourcetype=iis| where sc_status =503 |eval u_name =replace(cs_username, "0#","")| eval u_name1= replace(u_name, ".w|","")|eval u_name2=replace(u_name1,"\|","")|stats dc(u_name2) AS "Unique User 503", count(s_computername) as "Total 503 Errors"]|appendcols[search index=main sourcetype=iis| eval u_name =replace(cs_username, "0#","")| eval u_name1= replace(u_name, ".w|","")|eval u_name2=replace(u_name1,"\|","")|stats count(eval(time_taken>4000)) as "hitsfoursecond", count(eval(time_taken>2500)) as "hitstwopointfiveseconds", dc(u_name2) AS "Unique Users", count(s_computername) as "ElementsHits" |eval resultset= (hitsfoursecond/ElementsHits) *100 |eval resultset1=(hitstwopointfiveseconds/ElementsHits)*100 |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"  |table "Unique Users","ElementsHits","# of Hits > 4 seconds", "% Hits > 4 seconds", "% Hits > 2.5 seconds", "# of Hits > 2.5 seconds"]|appendcols[search index=main sourcetype=iis|eval csuri= lower(cs_uri_stem)| where csuri ="/view/pages/default.aspx" |eval u_name =replace(cs_username, "0#","")| eval u_name1= replace(u_name, ".w|","")|eval u_name2=replace(u_name1,"\|","")|stats dc(u_name2) as "YHP Unique User", count(s_computername) AS "YHP Elements/Hit"]

Your help is appreciated.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Give this a try (there were some duplicate queries which won't help in performance, I've removed them as well)

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)="/view/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 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(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 "Page Views > 4 seconds" count(eval(csuri_time_taken>2500)) as "Page Views > 2.5 seconds" avg(page_time_taken) as "Page Avg. Response Time (ms)", count(page_time_taken) as "Page views" count(s_computername_all) as "# of .aspx Hits" count(s_computername_4s) as "# of .aspx Hits > 4 seconds" count(s_computername_25s) as "# of .aspx Hits > 2.5 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 |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"

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

Give this a try (there were some duplicate queries which won't help in performance, I've removed them as well)

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)="/view/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 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(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 "Page Views > 4 seconds" count(eval(csuri_time_taken>2500)) as "Page Views > 2.5 seconds" avg(page_time_taken) as "Page Avg. Response Time (ms)", count(page_time_taken) as "Page views" count(s_computername_all) as "# of .aspx Hits" count(s_computername_4s) as "# of .aspx Hits > 4 seconds" count(s_computername_25s) as "# of .aspx Hits > 2.5 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 |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"

View solution in original post

0 Karma

New Member

Thanks for the reply and prompt response will try this optimized query.

0 Karma

New Member

will u able to help on below query?
I have another requirement; I need to get “_time” count.
Means I am using query like this.

Stats count by _time. So that I will get time field data and count of particular record( how many times this particular time stamp repeated).
Example:
_time count
2016-1-10:22:10:59 2
2016-1-10:22:12:40 4
Now I am trying to get avg(count) and max(count). So that I used same appendcols command. I wanted to combine above result also with same query.
Please help us on this.
I have multiple stats commands.

0 Karma

SplunkTrust
SplunkTrust

Average count of event for that _time OR just the average number of count for whole result set?
Give this a try. You don't need appendcols if you're using same data set with same set of filters.

your base search | stats count by time | eventstats avg(count) as averagecount max(count) as max(count)

0 Karma

Explorer

Hi,

I am looking for Average count of event for that _time
Example:
result:
1
3
5
7
8
9
10

avg(count)
6.14
max ( count)
10
you are query right. I have tried this. but single query I have multiple "stats" commands. will that work.?

0 Karma

SplunkTrust
SplunkTrust

From your query, you'll get just one record per _time, so if you're calculating avg and max by _time, it'll result in same value. You can have multiple stats in one query without problem. You can replace eventstats with just stats as well if you don't want to see the count.

0 Karma

Explorer

If am using multiple "stats" I am not getting result set.
Example for testing:

source = "C:\\Program Files\\Splunk\\var\\spool\\splunk\\u_ex160110-91.log"|eval aspx_time_taken=if(cs_uri_stem LIKE "%aspx%" AND sc_status!="401",time_taken,null())|stats avg(aspx_time_taken) as "Avg. Response Time .aspx (ms)"|stats count by _time | stats avg(count) as "Avg. Requests Per Second", max(count) as "Max Requests Per Second"
0 Karma

Explorer

Hi,
In your experience, Have you faced this kind of queries? when I am using multiple stats, either no results or first stats results are coming.

0 Karma

Path Finder

Stats aggregates the data into only what is affected by the stats command then pipes that data forward. So if there is something outside of the stats command that you need after the command, Splunk will not be able to find it.

0 Karma