1

I need to count how many DIFFERENT MAC´s that have accessed my IIS webserver and have a state value of 1.

The "MAC" adress and the "state" value are in the actual query (cs-uri-query)

So the log can look like this (only the cs-uri-query field are shown):

mac=00-20-c2-41-7e-b6&state=1&div=10
mac=00-20-c2-41-7e-b6&state=1&div=10
mac=00-20-c2-41-7e-b6&state=0&div=10
mac=00-10-c3-41-7e-b7&state=1&div=10

In this case the LogAnalyzer should count=2. 2 times have DIFFERENT MAC´s visited with the state of 1.

Can someone help me with the SLQ to use with LogAnalyzer?

Juw
  • 127
  • 1
  • 10
  • I deleted my answer since I'm not 100% clear on what you're asking (and I was trying to do more than logparser SQL can handle...). Can you add a sample data set and what you want the output to be? – squillman Nov 20 '13 at 17:09
  • Thx...i will change my initial post so it´s more obvious what i am looking for. I´ll be right back with a changed text. – Juw Nov 21 '13 at 12:32
  • Hope you can understand it better now. – Juw Nov 21 '13 at 12:43

1 Answers1

3

Try this out:

select count(distinct extract_value(cs-uri-query, 'mac'))
from *.log
where extract_value(cs-uri-query, 'state') = '1'

You might hve to adjust field names depending on your log format.

Gabriele Giuseppini
  • 156
  • 1
  • Thank you! Seems to work great! The "extract_value" function? seems to do it. Is that a native SQL function? – Juw Nov 25 '13 at 13:48
  • 1
    No, EXTRACT_VALUE was designed exclusively for the cs-uri-query scenario, to extract values out of key-value pairs in strings. – Gabriele Giuseppini Nov 26 '13 at 14:50