Display table of top 5 URL with their status and percentage

[Augustine_Vijay]

Need a table to show top 5 URL as given below in splunk. Is this possible in splunk? I tried many ways but I cant get all status of a URL as single row. Any help would be appreciated.

API	200	204	400	401	499	500	Total
/wodetails/?orderManagementSystem=ACP	895 (50%)	-	-	-	-	1 (<1%)	1792
/workorders//customerDetails/dropTags	-	15 (44%)	-	-	-	2 (6%)	34
/workorders//etc_timestamp/	373 (43%)	-	2 (<1%)	3 (<1%)	1 (<1%)	54 (6%)	866

[Augustine_Vijay]

@kamlesh_vaghela Last one thing where I am stuck.

I have url with userID's in between like /account/me/user/eypgxqnhgari/twofactorauth and I used rex to replace userID as /account/me/user/{user}/twofactorauth.
The userID is replaced successflly. But the url's are counted as seprate URL and displayed in table multiple times rather than counting as one.

Any help on this please?

[kamlesh_vaghela]

@Augustine_Vijay 

Can you please share your search with rex command ?

KV

[Augustine_Vijay]

@kamlesh_vaghela I have used multiple rex commands since I have few more URL's where userID comes in the urls

Below is the final search query I am having,

index=myaccount sourcetype=myaccountweb-master Response status=* url=* 
| chart count over url by status | addtotals 
| foreach * 
    [
    | eval <<FIELD>> = if('<<FIELD>>'==0,"-",'<<FIELD>>') 
    | eval p_<<MATCHSTR>> = if(isnull(tonumber('<<FIELD>>')),'<<FIELD>>',round(('<<FIELD>>'/Total)*100,2)) 
    | eval p_<<MATCHSTR>> = if('p_<<MATCHSTR>>'<1, "< 1",'p_<<MATCHSTR>>')
    | eval <<FIELD>> = if("<<FIELD>>"=="Total",'<<FIELD>>', case('<<FIELD>>'=="-","-",tonumber('<<FIELD>>')>1,'<<FIELD>>'." (".p_<<MATCHSTR>>."%)",1=1,'<<FIELD>>'))
    ]
| fields - p_* | eval url=lower(url)| rex mode=sed field=url "s/account\/(\d+)\//account\/me\//" | rex mode=sed field=url "s/\d+.\w+|\d+fm|\d+fs\d+/{user}/g" | rex mode=sed field=url "s/accounts\?ip=.*/accounts?ip=__/"| rex mode=sed field=url "s/[^\/]{30,}/*/g" | rex mode=sed field=url "s/(\d|\.){8,}/*/g" |  rex mode=sed field=url "s/..:..:..:..:..:../__/"  
| rex field="500" "\d+\s\((?<perc>.*)%\)" | sort - perc
| where perc>10

The userID I am having actually its alphanumeric. Few userID have only text, few text and numbers.

For few URL's, userID comes in middle, for few URL's userID comes in end. I have replaced few userID's with * for few URLs

The problem happens for all the URLs where userID is involving. It would be helpful like instead of all these rex command, for all URL one rexcommand which replaces userID with{user} or something like that and counts their set of URL's as one rather than each one separate.

[kamlesh_vaghela]

@Augustine_Vijay 

Is it possible to share all possible URLs having username?  Just replace USER_NAME with username.

eg.

/account/me/user/eypgxqnhgari/twofactorauth

to

/account/me/user/USER_NAME/twofactorauth

KV

[Augustine_Vijay]

@kamlesh_vaghela I have tried replacing. UserID is replaced by {user}. My main issue here is actually it takes count separately as below instead of counting all these as one. Any idea here?

/account/me/user/{user}/twofactorauth	-	-	-	-	-	4 (100.00%)
/account/me/user/{user}/twofactorauth	-	-	-	-	-	2 (100.00%)
/account/me/user/{user}/twofactorauth	-	-	-	-	-	3 (100.00%)

[kamlesh_vaghela]

@Augustine_Vijay 

Can you please try this?

index=myaccount sourcetype=myaccountweb-master Response status=* url=* 
| chart count over url by status | addtotals 

----- Place Replaced UserID by {user} -----

| foreach *
----- REST of the Search -----

🙂 😀

[Augustine_Vijay]

@kamlesh_vaghela I tried adding the rex commands out front before for each as you have said, but no luck 😞

Few URLs with user ID. I missed it to share in previous replies
/recommendations?an_hash=399fe594c49ad37e4df864abdac7e859
/account/me/user/JOJWHMKXCZ1618502145RI/twoFactorAuth
/account/8778101090026070/user/291850401514112007RL/payment
/account/me/user/QLALNASIHV1616624200RI/alternateEmail
/itg/91850401514112007R
/account/me/user/JHWERTYQMNVSJAIP/email/preferred

The rex commands successfully replaces the user ID's with the substitute I am giving but as I said each URL is counted separately. I have used  * to replace the userID below.

I am getting output  like below, but ideally it should count 5 and 4 together and display as 9 as a single URL. But since user ID's for each request changes, it counts seprately. Any idea on how to make these URLs to count as a single URL?

/account/me/user/*/email/preferred	-	-	-	-	-	5 (100.00%)
/account/me/user/*/email/preferred	-	-	-	-	-	4 (100.00%)
/account/me/user/*/email/preferred	-	-	-	-	-	4 (100.00%)

[kamlesh_vaghela]

@Augustine_Vijay 

Can you please try this?

index=myaccount sourcetype=myaccountweb-master Response status=* url=* 
| rex field=url mode=sed "s/\/user\/(.*)\//\/user\/USER\//g"
| chart count over url by status | addtotals 
| foreach * 
    [
    | eval <<FIELD>> = if('<<FIELD>>'==0,"-",'<<FIELD>>') 
    | eval p_<<MATCHSTR>> = if(isnull(tonumber('<<FIELD>>')),'<<FIELD>>',round(('<<FIELD>>'/Total)*100,2)) 
    | eval p_<<MATCHSTR>> = if('p_<<MATCHSTR>>'<1, "< 1",'p_<<MATCHSTR>>')
    | eval <<FIELD>> = if("<<FIELD>>"=="Total",'<<FIELD>>', case('<<FIELD>>'=="-","-",tonumber('<<FIELD>>')>1,'<<FIELD>>'." (".p_<<MATCHSTR>>."%)",1=1,'<<FIELD>>'))
    ]
| fields - p_* | eval url=lower(url)| rex mode=sed field=url "s/account\/(\d+)\//account\/me\//" | rex mode=sed field=url "s/\d+.\w+|\d+fm|\d+fs\d+/{user}/g" | rex mode=sed field=url "s/accounts\?ip=.*/accounts?ip=__/"| rex mode=sed field=url "s/[^\/]{30,}/*/g" | rex mode=sed field=url "s/(\d|\.){8,}/*/g" |  rex mode=sed field=url "s/..:..:..:..:..:__/"  
| rex field="500" "\d+\s\((?<perc>.*)%\)" | sort - perc
| where perc>10

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[Augustine_Vijay]

@kamlesh_vaghela Still not working 😞 it still counts the URL's separate and I dont see any difference by this rex command which we are giving at first

rex mode=sed field=url "s/\/user\/(.*)\//\/user\/USER\//g"

If I remove this rex also I get the same output and if I include this also same output. No difference.

[Augustine_Vijay]

@kamlesh_vaghela It works!! thanks a lot!!

[Augustine_Vijay]

@kamlesh_vaghela Thanks for the help. Am pretty new to splunk so could you just let me know what will be filed name for <<FIELD>> and <<MATCHSTR>> ?

Actually I tried the below query 

index=myaccount sourcetype=myaccountweb-master Response status=* url=* | stats count as status_code_count by url status | eventstats sum(status_code_count) as total_hits by url | eval status_code_percent=round((status_code_count/total_hits)*100, 2) | eval{status}=status_code_percent | fields url,status,status_code_percent | stats values(*) as * by url | head 5

I couldn't get the desired output.

Your attached screenshot is the exact one I am expecting. But if you could explain me on <<Field>> in your code, it would be helpful.

[kamlesh_vaghela · Accepted solution]

@Augustine_Vijay 

Can you please try this for your scenario?

index=myaccount sourcetype=myaccountweb-master Response status=* url=* 
| chart count over url by status | addtotals | sort - Total | head 5 
| foreach * 
    [
    | eval <<FIELD>> = if('<<FIELD>>'==0,"-",'<<FIELD>>') 
    | eval p_<<MATCHSTR>> = if(isnull(tonumber('<<FIELD>>')),'<<FIELD>>',round(('<<FIELD>>'/Total)*100,2)) 
    | eval p_<<MATCHSTR>> = if('p_<<MATCHSTR>>'<1, "< 1",'p_<<MATCHSTR>>')
    | eval <<FIELD>> = if("<<FIELD>>"=="Total",'<<FIELD>>', case('<<FIELD>>'=="-","-",tonumber('<<FIELD>>')>1,'<<FIELD>>'." (".p_<<MATCHSTR>>."%)",1=1,'<<FIELD>>'))
    ]
| fields - p_*

Yes, <<FIELD>> and <<MATCHSTR>> are tokens of foreach command. 

Please go through below link for more info.

https://docs.splunk.com/Documentation/Splunk/8.2.0/SearchReference/Foreach

Please accept the answer if it is resolved to help Community. 

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[Augustine_Vijay]

@kamlesh_vaghela Is there anyway I can specifically select a status and set an alarm to trigger this table? Eg: if status 500 is more than so and so percent, trigger the table

[kamlesh_vaghela]

@Augustine_Vijay 

Are you looking for this?

OUR_UPTO_NOW_SEARCH
| rex field="500" "\d+\s\((?<perc>.*)%\)" 
| where perc>90

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated. 

[Augustine_Vijay]

Thanks @kamlesh_vaghela . Exactly what I needed!!! Thanks for your quick responses. Helped me a lot.

[kamlesh_vaghela]

@Augustine_Vijay 

You can try below sample search for your expected output. You have to replace the base search and field name as per your requirement.

index="_internal" status=* 
| chart count over source by status 
| addtotals 
| sort - Total 
| head 5 
| foreach * 
    [
    | eval <<FIELD>> = if('<<FIELD>>'==0,"-",'<<FIELD>>') 
    | eval p_<<MATCHSTR>> = if(isnull(tonumber('<<FIELD>>')),'<<FIELD>>',round(('<<FIELD>>'/Total)*100,2)) 
    | eval p_<<MATCHSTR>> = if('p_<<MATCHSTR>>'<1, "< 1",'p_<<MATCHSTR>>')
    | eval <<FIELD>> = if("<<FIELD>>"=="Total",'<<FIELD>>', case('<<FIELD>>'=="-","-",tonumber('<<FIELD>>')>1,'<<FIELD>>'." (".p_<<MATCHSTR>>."%)",1=1,'<<FIELD>>'))
    ]
| fields - p_*

 

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.