Splunk Search

Display table of top 5 URL with their status and percentage

Augustine_Vijay
Explorer

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

Labels (5)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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.

View solution in original post

Augustine_Vijay
Explorer

@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?

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Augustine_Vijay 

Can you please share your search with rex command ?

KV

0 Karma

Augustine_Vijay
Explorer

@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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

0 Karma

Augustine_Vijay
Explorer

@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%)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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 -----

 

🙂 😀

0 Karma

Augustine_Vijay
Explorer

@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%)

 

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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.

0 Karma

Augustine_Vijay
Explorer

@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.

0 Karma

Augustine_Vijay
Explorer

@kamlesh_vaghela It works!! thanks a lot!!

0 Karma

Augustine_Vijay
Explorer

@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.

Tags (3)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
Explorer

@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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
Explorer

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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_*

 

 

Screenshot 2021-06-04 at 11.32.59 AM.png

Thanks
KV
▄︻̷̿┻̿═━一

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

Tags (3)
0 Karma
Get Updates on the Splunk Community!

Testing out the OpenTelemetry Collector With raw Data

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...