Graphing Weighted Values

[nickhills]

we have log files which record incoming connections from our clients and the device_id.
we would like to graph a representative number of incoming connections weighted by the number of devices they have. The idea being that if a customer has an unusually high number of connections for the devices they have, it would indicate an issue.

our log files look like this:

2011-10-25 12:06:44.829 : DLG : INFO   : DeviceConnection [device_id:088243] [client:abc.com] new connection event with client id

i have field extractions for device_id and client_id, but cant work our how to graph this.

what i want is number of connectons for client_id / number of device_ids (where number of devices come from occurrences in the logs across the same time period)

i think i need an eval statement, but cant quite figure out the syntax. Can anyone help, or point me in the right direction.

thanks very much.

[nickhills]

gkanapathy,

thanks for your comment, it was enough to push me in the right direction.... this is what worked.

sourcetype=DLOG "new connection" |stats count as connections,dc(device_id) as num_devices by client_id| eval ratio = connections/num_devices|stats avg(ratio) AS Connection_Ratio by client_id|sort -Connection_Ratio limit=10

thanks very much!

[gkanapathy]

Your second stats command does not do anything at all other than remove and rename some fields, and you can do this much more clearly and efficiently with the fields command, and by selecting the right field names in the first place (in the eval).

[gkanapathy · Accepted solution]

I don't really understand what you're asking or why you're taking min(countdev), but seems to me that

ssourcetype=DLOG "new connection" 
| stats count as connections, dc(device_id) as num_devices by client_id
| eval ratio = connections/num_devices

gives you the connections for each client, and the number of devices for each client, and divides them. You can add a | sort - ratio to see the most suspicious ones.

[nickhills]

I have now got as far as the following:

 sourcetype=DLOG "new connection" client_id=536352|eventstats dc(device_conn_id) AS numdev|eventstats count(device_conn_id) AS countdev|eval ratio=(countdev/numdev)|stats min(countdev) by client_id,numdev,ratio

this gives me a ratio field, which is number of events / number of devices.

but i want to run this against all client ids, but when i remove the client id from the search, it returns totals (across all accounts), and thus 1 identical ratio and number of events for all clients.