Splunk Search

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

sinha58
Explorer

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

0 Karma
1 Solution

to4kawa
Ultra Champion

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:

  1. regex: cf. regex101.com
  2. spath: extract JSON, cluster_id and log objects.
  3. mvindex: extract IP address(split spaces)
  4. 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
Ultra Champion

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.

0 Karma

sinha58
Explorer

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

0 Karma

to4kawa
Ultra Champion

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

0 Karma

sinha58
Explorer

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

0 Karma

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

Hi, @sinha58
How about this?

sinha58
Explorer

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

0 Karma

to4kawa
Ultra Champion
 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?

0 Karma

sinha58
Explorer

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

0 Karma

to4kawa
Ultra Champion
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

0 Karma

sinha58
Explorer

@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: (?...)."

0 Karma

sinha58
Explorer

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 ?

0 Karma

to4kawa
Ultra Champion

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

0 Karma

sinha58
Explorer

@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

0 Karma

sinha58
Explorer

alt text

0 Karma

sinha58
Explorer

alt text

0 Karma

sinha58
Explorer

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.

0 Karma

to4kawa
Ultra Champion

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

0 Karma

sinha58
Explorer

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

0 Karma

to4kawa
Ultra Champion

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

0 Karma

sinha58
Explorer

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

For reference attached screenshot. alt text

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...