Splunk Search

Join two stats searches and run stats/group on the result

I'd like to join two searches and run some stats to group the combined result to see how many users change/update browsers how often.

In my IIS logs I have one search that gives me a user agent string ( cs_User_Agent) and a SessionId; then another that has the SessionId and the UserId

search 1 retrieves a table of cs_User_Agent and SessionId:
host=HOST1 index=iis sc_status=200 getLicense | sistats dc(SessionId) count by cs_User_Agent

SessionId           cs_User_Agent                                                                                                                         count
0014D886099319E6    Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/64.0.3282.140+Safari/537.36+Edge/17.17134        12
0014D953D99FD234    Mozilla/5.0+(Windows+NT+6.1;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/69.0.3497.100+Safari/537.36                        5
0014D953D99FD234    Mozilla/5.0+(Windows+NT+6.3;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/69.0.3497.100+Safari/537.36                             5
00154D82F471A7AA    Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/69.0.3497.100+Safari/537.36                       2
0015B3CAC0EC3940    Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/68.0.3440.106+Safari/537.36                      30
0015C53D737B2991    Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/68.0.3440.106+Safari/537.36+OPR/55.0.2994.61     16

search 2 retrieves a table of SessionId and UserId:
host=HOST1 index=iis sc_status=200 authorize | stats count by SessionId, UserId

SessionId           UserId                      count
00061D40BFAB4208    4BJKKEAWGYXEAJH0F5F9DHSC0024    2
0008F091D8E8BE1A    1I7WKS9XIMZ92DCZF6CVKA4E001Q    2
000FC56381D4EA4B    3PH0F08V00SY9GFPGVCQBIQN006N    3
00106C907ED66683    JALM1LNJ8SV72BNHE1C5H0I50020    3
0013143CBC157C26    ETW9HL7L71PQJB7P492LLFEM006E    4
001E25B42A554F79    702EBB0O8MKG0VI94VIQ01ZE0031    1

I need to join these together to see how many different cs_User_Agent strings the users had during the period and count those.
Basically to see how many of the users change/update browsers very often.

So, my result should look like this:

Number of UA Strings in the period     |   Number of Users in grouping
>20                                        1
>10                                        3
>5                                         4
5                                         10
4                                          3
3                                          3
2                                          1
1                                         14

The results for search 1 & 2 would return A LOT of data, and from reading the subsearch info it sounds like that's not ideal as the whole subsearch would have to stay in RAM? Is there a better way than subsearch to do this?

0 Karma