topic Re: Need to find query from mobile(Android, IOS) device in Splunk Search

Need to find query from mobile(Android, IOS) device

sinha58 — Wed, 30 Sep 2020 03:34:04 GMT

Hello,

I am new in Splunk, Looking for result which is coming from Android and IOS devices, seeing android and IOS query in logs but need to count, How many queries are coming from such devices, so can easily make a dashboard for same.

if you guys suggest that query, it would be a great help for me.

Here it is logs below for reference which showing a result for android devices.

"{"cluster_id":"sc-a2","log":"11.16.39.12 - - [10/Jan/2020:10:05:48 +0000] \"GET /so/search?cat_id=1255027787111_1255027789273&client=us_gr&hd=false&ht=false&offset=10&page=1&prg=android&ps=30&sort=best_match&stores=1197"

Thanks,
ss

Re: Need to find query from mobile(Android, IOS) device

to4kawa — Wed, 30 Sep 2020 03:34:06 GMT

sample:

| makeresults 
| eval _raw="{\"cluster_id\":\"sc-a2\",\"log\":\"11.16.39.12 - - [10/Jan/2020:10:05:48 +0000] \"GET /so/search?cat_id=1255027787111_1255027789273&client=us_gr&hd=false&ht=false&offset=10&page=1≺g=android&ps=30&sort=best_match&stores=1197\""
| rex "(?<mobile>(?<=g=).+?(?=&))"

recommend:

index=np_search-be1559690845 kubernetes.container_name=reso-og stream=stdout
| rex "(?<mobile>(?<=g=).+?(?=&))" 
| rex "\[(?<time>\d{2}/\w{3}/\d{4}:\d{2}:\d{2}:\d{2} \+0000)\]"
| eval time=strptime(time,"%d/%b/%Y:%T %z")
| spath 
| eval log = mvindex(split(log," "),0)
| fieldformat time=strftime(time,"%c")
| table time cluster_id log mobile

Hi, @sinha58
If you can identify the string, you can extract in this way.

Explanation:

regex: cf. regex101.com
spath: extract JSON, cluster_id and log objects.
mvindex: extract IP address(split spaces)
fieldformat: change time(UNIX epoch) to readable. The reason I don't use strftime is that UNIX time is just fine for future aggregations.

Splunk Search Processing Language (SPL) is processed in order.
please try one by one line and check result.

cf. SearchReference/Commands by category

Re: Need to find query from mobile(Android, IOS) device

sinha58 — Wed, 30 Sep 2020 03:46:02 GMT

Thanks @to4kawa for your response, I appreciated it.

Here it is my query:-
"index=np_search-be1559690845 kubernetes.container_name=reso-og stream=stdout"

Result:- "{"cluster_id":"sc-a2","log":"11.16.39.12 - - [10/Jan/2020:10:05:48 +0000] \"GET /so/search?cat_id=1255027787111_1255027789273&client=us_gr&hd=false&ht=false&offset=10&page=1≺g=android&ps=30&sort=best_match&stores=1197"

Can you advise me on the above query to filter Android devices? I need to count how many queries are coming from Android and IOS.

I will be waiting for your response. Thank you again for your kind reply.

Re: Need to find query from mobile(Android, IOS) device

to4kawa — Mon, 13 Jan 2020 07:02:19 GMT

HI, @shinha58
my answer is updated, please check it.

Re: Need to find query from mobile(Android, IOS) device

sinha58 — Mon, 13 Jan 2020 11:06:23 GMT

thanks for your updated query but can't see the logs data associated with log and moble table.

For reference attached screenshot.

Re: Need to find query from mobile(Android, IOS) device

to4kawa — Mon, 13 Jan 2020 11:12:56 GMT

please provide sample log and your_result.
I can't see the screenshot.

Re: Need to find query from mobile(Android, IOS) device

sinha58 — Mon, 20 Jan 2020 09:44:04 GMT

Hi @to4kawa, thanks for your updated query, Could you please explain to me briefly those queries to understand. thanks in advance.

Re: Need to find query from mobile(Android, IOS) device

to4kawa — Mon, 20 Jan 2020 11:12:19 GMT

HI, @sinha58
my answer is updated, Happy splunking.

Re: Need to find query from mobile(Android, IOS) device

sinha58 — Wed, 22 Jan 2020 10:27:54 GMT

good explanation in brief @to4kawa, thank you so much for your valuable response. Is there any good way to learn Splunk other than Splunk doc.

Have a nice day man!!

Re: Need to find query from mobile(Android, IOS) device

to4kawa — Wed, 22 Jan 2020 10:58:00 GMT

In my case, I run the queries of Splunk answers line by line and check the result of the command.
Some people write cool SPL.

Happy Splunking.

Re: Need to find query from mobile(Android, IOS) device

sinha58 — Thu, 23 Jan 2020 09:28:58 GMT

Hi @to4kawa, Need one more question on this query, would like to add time on my dashboard? So I can easily co-relate my logs timestamp value. Could you please suggest to me ?

Re: Need to find query from mobile(Android, IOS) device

sinha58 — Thu, 23 Jan 2020 09:29:24 GMT

Re: Need to find query from mobile(Android, IOS) device

sinha58 — Thu, 23 Jan 2020 09:31:11 GMT

@to4kawa if you can see the above graph as per your query, It's perfect except timestamp value. need to add a timestamp for the same. waiting for your suggestions

Re: Need to find query from mobile(Android, IOS) device

to4kawa — Thu, 23 Jan 2020 09:49:12 GMT

x-axis: _time
y-axis: mobile
right?

Re: Need to find query from mobile(Android, IOS) device

to4kawa — Thu, 23 Jan 2020 09:58:11 GMT

 ....
 | table time cluster_id log mobile
 | rename time as _time
 | timechart count by mobile

Hi, @sinha58
How about this?

Re: Need to find query from mobile(Android, IOS) device

sinha58 — Thu, 23 Jan 2020 13:18:12 GMT

@to4kawa, thanks for your reply, I have tried but queries coming from devices are not shown as earlier. so it's not an ideal way to look at the graf and identify. Attached the screenshot for that added query.

Re: Need to find query from mobile(Android, IOS) device

sinha58 — Thu, 23 Jan 2020 13:27:55 GMT

Re: Need to find query from mobile(Android, IOS) device

to4kawa — Thu, 23 Jan 2020 13:36:43 GMT

 index=np_search-be1559690845 kubernetes.container_name=reso-og stream=stdout
 | rex "(?<mobile>(?<=g=).+?(?=&))" 
 | rex "\[(?<time>\d{2}/\w{3}/\d{4}:\d{2}:\d{2}:\d{2} \+0000)\]"
 | eval time=strptime(time,"%d/%b/%Y:%T %z")
 | spath 
 | eval log = mvindex(split(log," "),0)
 | fieldformat time=strftime(time,"%c")
 | table time cluster_id log mobile
 | rename time as _time
 | timechart count by mobile

your result is by this query?

Re: Need to find query from mobile(Android, IOS) device

sinha58 — Wed, 30 Sep 2020 03:54:33 GMT

Here it is the query which is working fine, I had only only "| stats count by mobile" with your updated queries.

index=np_search-be1559690845 kubernetes.container_name=reso-og stream=stdout | rex "(?(?<=g=).+?(?=&))"
| rex "[(?\d{2}/\w{3}/\d{4}:\d{2}:\d{2}:\d{2} +0000)]"
| eval time=strptime(time,"%d/%b/%Y:%T %z")
| spath
| eval log = mvindex(split(log," "),0)
| fieldformat time=strftime(time,"%c")
| table time cluster_id log mobile | stats count by mobile

Re: Need to find query from mobile(Android, IOS) device

to4kawa — Thu, 23 Jan 2020 17:49:53 GMT

index=np_search-be1559690845 kubernetes.container_name=reso-og stream=stdout | rex "(?(?<=g=).+?(?=&))"
| rex "[(?\d{2}/\w{3}/\d{4}:\d{2}:\d{2}:\d{2} +0000)]"
| eval time=strptime(time,"%d/%b/%Y:%T %z")
| spath
| eval log = mvindex(split(log," "),0)
| fieldformat time=strftime(time,"%c")
| table time cluster_id log mobile 
| table time mobile

viz > line chart