3

I am using case statements in Log parser to find out the browser names like, case strcnt(cs(user-agent), 'MSIE') when 1 THEN 'IE'

But some of the user agent fields have 'MSIE' more than one time, is there any way to check >=1

case strcnt(cs(user-agent), 'MSIE') when >=1 THEN 'IE'

or

case when strcnt(cs(user-agent), 'MSIE') >=1 THEN 'IE'

I tried these statement iis giving syntax error.

Please give some other workaround to check more than one occurance.. Thanks

user1905397
  • 171
  • 1
  • 3
  • 9

4 Answers4

4

This is what mine looks like... It doesn't seem to support multiple when's so you need to nest them

case strcnt(cs(user-agent), 'iPhone') WHEN 1 THEN 'iPhone' ELSE 
    case strcnt(cs(user-agent), 'Android') WHEN 1 THEN 'Android' ELSE
        case strcnt(cs(user-agent), 'iPad') WHEN 1 THEN 'iPad' ELSE 
            'Other'
        END
    END
END
Phillip Fleischer
  • 1,023
  • 12
  • 14
0

NOTE: The actual released versions of Log Parser do not support the CASE statement form shown below. @Gabrielle Guiseppe mentions this in a comment.

There are two forms of the CASE statement - one that only allows you to do equaility (the one you're using) and one that allows you to use any expression:

SELECT CASE WHEN STRCNT(cs(user-agent), 'MSIE') >=1 THEN 'IE' ELSE 'FOO' END

Also note the use of END at the...end of the CASE statement.

Another option is:

SELECT CASE WHEN INDEX_OF(cs(user-agent), 'MSIE') IS NOT NULL THEN 'IE' WHEN INDEX_OF(cs(user-agent), 'Google') IS NOT NULL THEN 'CHROME' ELSE 'UNKNOWN' END

Ross Presser
  • 6,027
  • 1
  • 34
  • 66
Gabriele Giuseppini
  • 1,541
  • 11
  • 19
  • What version of logparser is the second option supposed to work with? I'm on 2.2.10, and I've tried several times to get such a query to parse without error. – beavel Dec 20 '13 at 22:49
  • Both queries use the same CASE statement syntax, so if the first one works for you, the second also should. What error do you get exactly? – Gabriele Giuseppini Dec 30 '13 at 09:26
  • To be clear, I can never get the CASE statement that allows any expression to work. To test, I took your test case and tried to run the following: `LogParser -i:W3C -o:CSV "SELECT CASE WHEN INDEX_OF(cs(user-agent), 'MSIE') IS NOT NULL THEN 'IE' WHEN INDEX_OF(cs(user-agent), 'Google') IS NOT NULL THEN 'CHROME' ELSE 'UNKNOWN' FROM C:\logs\*"` which results in `Error: Syntax Error: : expecting WHEN keyword instead of token 'INDEX_OF(cs(user-agent),'` It appears to me that it is parsing it as the equality only syntax as there is a WHEN preceding the token it is complaining about. – beavel Dec 30 '13 at 16:24
  • 3
    You are absolutely right, please accept my apologies. I was using a version of LogParser that never saw the light and which supported the second form of CASE. Sorry about that. Back to your problem, you may then try something along the lines of: `SELECT CASE INDEX_OF(cs(uri-agent), 'MSIE') WHEN -1 THEN 'FOO' ELSE 'IE' END` – Gabriele Giuseppini Jan 02 '14 at 09:42
  • Thanks for the response and clarification! I've worked around this in a number of ways, but have tried going back to this each time to see if I had missed something or made a syntax error. I now know not to waste my time. I don't suppose there is any chance of that version seeing the light of day now. Is there anyway I might get or find a copy? – beavel Jan 02 '14 at 13:56
  • Unfortunately there is no way to get the unofficial version, sorry. – Gabriele Giuseppini Jan 03 '14 at 13:58
0

I had this same issue, but with iPhone being repeated in the user agent string. Eg:

Mozilla/5.0+(iPhone;+CPU+iPhone+OS+10_2_1+like+Mac+OS+X)+AppleWebKit/602.4.6+(KHTML,+like+Gecko)+Version/10.0+Mobile/14D27+Safari/602.1

My solution was to use the REPLACE_IF_NOT_NULL function, and take advantage of the fact that INDEX_OF returns null if the search string is not found:

...     
case REPLACE_IF_NOT_NULL(INDEX_OF(cs(user-agent),'iPhone'),1) when 1 THEN 'iPhone' else
case REPLACE_IF_NOT_NULL(INDEX_OF(cs(user-agent),'iPad'),1) when 1 THEN 'iPad' else
case REPLACE_IF_NOT_NULL(INDEX_OF(cs(user-agent),'Android'),1) when 1 THEN 'Android' else
...

Using this format ran about 10% slower than the equivalent query using the strcnt(...) construct seen in other examples, but it solves the iPhone issue and I believe is generally more robust.

psh
  • 1
  • 1
-1

The way I solved this was to use multiple when keywords

CASE strcnt(TO_LOWERCASE(User-Agent),'mobile') when 1 THEN 'Mobile' when 2 THEN 'Mobile' when 3 THEN 'Mobile' else CASE strcnt(TO_LOWERCASE(User-Agent),'feed')  when 1 THEN 'Feed'  when 2 THEN 'Feed'   when 3 THEN 'Feed' ELSE 'Normal' End End

This covers it for word mobile appearing up to 3 times.

Yotam Omer
  • 15,310
  • 11
  • 62
  • 65
Rupert Dick
  • 1