Solved: Need to find query from mobile(Android, IOS) devic...

sinha58 · ‎01-10-2020

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

to4kawa · ‎01-10-2020

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

View solution in original post

to4kawa · ‎01-26-2020

1:

<dashboard>
  <label>test_chart</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>| makeresults count=2
| streamstats count
| eval _time=if(count=2,relative_time(_time,"-1d@d"),_time)
| timechart span=1m count
| eval count=random() % 20
| eventstats min(_time) as start max(_time) as end
| eval timerange=strftime(start,"%F %T")."〜".strftime(end,"%F %T")
| table _time count timerange</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
          <done>
            <set token="timerange">$result.timerange$</set>
          </done>
        </search>
        <option name="charting.axisTitleX.visibility">collapsed</option>
        <option name="charting.axisTitleY.visibility">collapsed</option>
        <option name="charting.data.fieldShowList">["_time", "count"]</option>
        <option name="charting.drilldown">none</option>
      </chart>
      <html>
        <div>
          <center>
            <h2>$timerange$</h2>
          </center>
        </div>
      </html>
    </panel>
  </row>
</dashboard>

2:　Some of the searched events do not have a mobile field.

sinha58 · ‎01-23-2020

alt text

Now getting the expected graph but need to change
1) _time as a Timerange name in graph
2) getting NULL data as well, not understanding what it means as per the query point of view.

@to4kawa, Can you assist me to change _time as a Timerange name in Graph. Thanks

to4kawa · ‎01-26-2020

I see your problem, but this question is finished.
please ask another question.

sinha58 · ‎01-26-2020

Thank you so much @to4kawa for your answers from the beginning, It helped me a lot

to4kawa · ‎01-23-2020

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

Hi, @sinha58
How about this?

sinha58 · ‎01-23-2020

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

to4kawa · ‎01-23-2020

 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?

sinha58 · ‎01-23-2020

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

to4kawa · ‎01-23-2020

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

sinha58 · ‎01-23-2020

@to4kawa Getting error while tried above query.
Error:- "Error in 'rex' command: The regex '(?(?<=g=).+?(?=&))' does not extract anything. It should specify at least one named group. Format: (?...)."

sinha58 · ‎01-23-2020

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 ?

to4kawa · ‎01-23-2020

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

sinha58 · ‎01-23-2020

@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

sinha58 · ‎01-23-2020

alt text

sinha58 · ‎01-23-2020

alt text

sinha58 · ‎01-12-2020

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.

to4kawa · ‎01-12-2020

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

sinha58 · ‎01-20-2020

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

to4kawa · ‎01-20-2020

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

sinha58 · ‎01-13-2020

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

For reference attached screenshot.

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

Splunk at Cisco Live 2025: Learning, Innovation, and a Little Bit of Mr. Brightside

Splunk App Dev Community Updates – What’s New and What’s Next

The Latest Cisco Integrations With Splunk Platform!

Are you a member of the Splunk Community?

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

Splunk at Cisco Live 2025: Learning, Innovation, and a Little Bit of Mr. Brightside

Splunk App Dev Community Updates – What’s New and What’s Next

The Latest Cisco Integrations With Splunk Platform!