How to create a Splunk query to more easily narrow down the exact page where a device ID reset occurred?

[msmith58]

Here is the SPL:

index=name reqHost="host" | rex field=cookie "care_did=(?<care_did>[a-z0-9-]+)" | rex field=cookie "n_vis=(?<n_vis>[a-z0-9-\.]+)" | stats avg(_time) as _time, dc(care_did) as care_did_count, values(care_did) by n_vis

Any help on this is appreciated.

[msmith58]

Hi ITWhisperer,

Thanks for responding, I'm trying to help our engineers craft a splunk query that would help narrow down to a particular application or web page. I don't think the device id changes and here is the full SPL query they came me to play around with and some output. They also want to calculate a rate rather than an absolute number.

index=akamai reqHost="www.care.com"
| rex field=cookie "care_did=(?<care_did>[a-z0-9-]+)"
| rex field=cookie "n_vis=(?<n_vis>[a-z0-9-\.]+)"
| stats avg(_time) as _time, dc(care_did) as care_did_count, values(care_did) by n_vis
| where care_did_count > 1
| timechart count

2022-02-28 15:00:00	34
2022-02-28 15:30:00	46
2022-02-28 16:00:00	52
2022-02-28 16:30:00	54
2022-02-28 17:00:00	55
2022-02-28 17:30:00	63
2022-02-28 18:00:00	62
2022-02-28 18:30:00	62
2022-02-28 19:00:00	69
2022-02-28 19:30:00	77
2022-02-28 20:00:00	76
2022-02-28 20:30:00	64
2022-02-28 21:00:00	55
2022-02-28 21:30:00	56
2022-02-28 22:00:00	59

[ITWhisperer]

Presumably, care_did is the device id? But, what is n_vis? How does this relate to the problem they are trying to solve? Would it help to know when a care_did changed for a n_vis?

| streamstats dc(care_did) as care_did_count list(care_did) as care_dids min(_time) as previous_time window=2 global=f by n_vis
| where care_did_count > 1

This assumes your events are in increasing time order

[msmith58]

The understanding, I have is they want to know when the ( care_did ) changes what associated page was involved and I believe the ( n_vis ) is the number of visitors. And it would it help to know when a care_did changed for a n_vis. And it would help to know when a care_did changed for a n_vis.

Thanks ITWhisperer for your patience, I'm learning on the fly.

[msmith58 · Accepted solution]

Hi ITWhisperer,

We figured out the Splunk query that works for us.

index=akamai reqHost="www.care.com" 
| rex field=cookie "care_did=(?<care_did>[a-z0-9-]+)" 
| rex field=cookie "n_vis=(?<n_vis>[a-z0-9-\.]+)" 
| stats dc(care_did) as care_did_count, values(care_did) by n_vis 
| stats count(eval(care_did_count <= 1)) as single_care_did_count, count(eval(care_did_count > 1)) as multiple_care_did_count, count as total
| eval multiple_care_did_percentage = multiple_care_did_count / total

[ITWhisperer]

Please can you explain what you mean by "exact page" in this context?

How do you determine from your events that the device id reset has happened? Is this when it changes within the time period of the search?

Can the device id change more than once?

Can you share some example events so we can see what it is you are dealing with?