Splunk Search

Count of url hits for particular set of users

aditya22
New Member

i have set of users x,y,z and few url regex a,b,c.

I need to know how many time these users hit the url regex in chart format.

users in y axis and urls in x axis.

Any help?

Tags (2)
0 Karma

rmmiller
Contributor

Assuming your users are in a field named user and urls are in a field named url, the chart command should provide what you describe:

yoursearch
| chart count OVER user BY url

Here is some random, made-up sample data with this in action:

| makeresults count=20
| eval urls=split("URL1,URL2,URL3,URL4,URL1",",")
| eval url=mvindex(urls,random()%5)
| eval users=split("Bob,Susan,John,Alice,Anne",",")
| eval user=mvindex(users,random()%5)
| table user url
| rename COMMENT AS "Everything above this line is just random mocked up sample data"
| chart count OVER user BY url

Which produces output that looks like this on the Statistics tab:

user    URL1    URL2    URL3    URL4
Alice       1       0       1       0
Anne        1       1       0       0
Bob     1       1       2       2
John        3       1       0       0
Susan       1       0       2       3

Hope that helps!
rmmiller

rmmiller
Contributor

@aditya22 did this solve your need?

0 Karma

aditya22
New Member

Thanks for the answer.

Tried this but getting syntax error.

index=jira host="hostname" | makeresults count=20 | | eval urls=split(""/user/mention","/jira/secure/StructureBoard.jspa"")| eval url=mvindex(urls,random()%5) | eval users=split("abc,xyz",",") | eval user=mvindex(users,random()%5) | table user url | chart count OVER user BY url

0 Karma

aditya22
New Member

@rmmiller

0 Karma

aditya22
New Member

Error in 'SearchParser': Missing a search command before '|'. Error at position '72' of search query 'search index=jira host="***" | m...{snipped} {errorcontext = ount=20 | | eval urls}'.

0 Karma

aditya22
New Member

Thanks much.

127.0.0.1 870x3272848x26 username [08/May/2020:14:30:17 +0000]:104 "GET /jira/rest/greenhopper/1.0/xboard/work/allData.json?rapidViewId=4480&selectedProjectKey=RUA&etag=4480%2C1588948126000%2C%5B%5D%2C%5B%5D%2Cfalse%2C102&_=1588948215994 HTTP/1.1" 200 237 999 "https://xyn.com/jira/secure/RapidBoard.jspa?rapidView=4480&view=detail&selectedIssue=RUA-5246" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.129 Safari/537.36"

I have 10 usernames and 4-5 url regex and want to see how many times these urls was hit by user in a span of time.

sample url regex from above access log:"xboard/work/allData.json"

0 Karma

rmmiller
Contributor

@aditya22 OK, we're almost there.
1) For your 4-5 url regex, what field name(s) holds the URLs?
For the example above, what field holds the value "xboard/work/allData.json"?

2) Do the usernames show up in a field already? If so, what's the name of that field?

Once we have the names of these 2 fields, we'll get you where you need to be.

0 Karma

aditya22
New Member

Hi,

We have only field for user that is user_big.I dont have a field to store url and i have five regexes like this.

0 Karma

rmmiller
Contributor

@aditya22 OK, so that's 1 field figured out: user_big.

You say you have 5 regexes for the URLs? Can you show me your complete SPL with the sensitive bits wiped? We might need to construct a common field to store the URL for each event to get this to work, depending on how you've written your regular expressions.

0 Karma

aditya22
New Member

@rmmiller

Thanks .

I recently got a query to get the url hits based on regex based on 3 min time interval.

The same thing i need to check for a set of user list(e:10 users) i have.so basically regex remains same.

below query i used to get the hit count of url regexes based on 3 min time interval.

index=jira host="hostname" "rapidViewId=" OR "/user/mention" OR "/jira/secure/StructureBoard.jspa" OR "softwareplant-biggantt/1.0/ppm/program" OR "/lastVisited" source="/opt/atlassian/jira/logs/access_log.2020-04-28" | eval chartingField=case(match(_raw,"rapidViewId="),"board", match(_raw,"/user/mention"), "mention",match(_raw,"StructureBoard.jspa"), "structure",match(_raw,"softwareplant-biggantt/1.0/ppm/program"),"gantt",match(_raw,"/lastVisited"),"last visited" ) | timechart span=3m count by chartingField

0 Karma

rmmiller
Contributor

@aditya22 OK, you've created a field named chartingField that contains your URL of interest here! If the eval statement using case and regex is the same between this SPL and the one for your original question, then this will give you a table with URLs across the rows and users in the first column:

index=jira host="hostname" "rapidViewId=" OR "/user/mention" OR "/jira/secure/StructureBoard.jspa" OR "softwareplant-biggantt/1.0/ppm/program" OR "/lastVisited" source="/opt/atlassian/jira/logs/access_log.2020-04-28"
| eval chartingField=case(match(_raw,"rapidViewId="),"board", match(_raw,"/user/mention"), "mention",match(_raw,"StructureBoard.jspa"), "structure",match(_raw,"softwareplant-biggantt/1.0/ppm/program"),"gantt",match(_raw,"/lastVisited"),"last visited" )
| chart count OVER user_big BY chartingField

Please let me know what part isn't right if that isn't it.
--rmmiller

0 Karma

rmmiller
Contributor

@aditya22 Did this last update work for you?

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!