Freeradius-Server Abfrage

[disem]

Moin Moin,

Ich brauche für meinen Radius-Server (freeradius) eine Abfrage.

Im moment ist es :
sqlcounter noresetcounter {
counter-name = Max-All-Session-Time
check-name = Max-All-Session
sqlmod-inst = sql
key = User-Name
reset = never
query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{%k}'"
}


http://www.pic-upload.de/view-8370166/1.jpg.html



http://www.pic-upload.de/view-8370183/2.jpg.html


Das ganze wird für einen WLAN-Hotspot benötigt, der z.B. auch Monatszugänge verwalten soll. Die Abfrage macht Probleme... wenn ich mich einlogge nimmt er immer nur die Zeit von der aktuellen Session, also hätte man einen ganzen Monat in Sekunden zum Surfen (Hier in dem Beispiel sind es 3600 Sekunden). Ich bin leider nicht der MySQL Guru. Ich hoffe es kann mir jemand helfen.

Accstarttime(Bild2) + Value 3600(Bild1) = Zugang Ende ! Wäre sowas mögliche?

Ich danke im Voraus.

Gruß

[thomas_w]

Also wirklich verstanden habe ich das Problem nicht. Wenn ich mir Bild 1+2 betrachte, dann wechselt der UserName bei jeder Session. Verstehe ich nicht.

Hier wird nur ein UserName abgefragt

SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{%k}

Eventuell wäre es eine Lösung mehrere UserName (hier drei) abzufragen und deren Summe zusammen zu zählen.

SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct 
 WHERE UserName IN  ( 'Fritz', 'Kurt', 'Emil' );

Grüße
Thomas

[disem]

Nein, Ich habe es etwas schlecht erklärt...

Bleiben wir bei User : ix7dh68v5cyu

In Bild1 :

Ein Account besteht immer aus 2 Einträgen
1. Nutzer und Passwort
2. Nutzer und Länge der Nutzung (3600)

Bild2 :

Ist das Logging...

Das Problem ist das er bei dem beispiel query "acctseesiontime" zusammenzählt und wenn da die 3600 erreicht sind ist der Acc ix7dh68v5cyu(3600sekunden) abgelaufen.

Ich möchte aber das er nicht die sessions zählt, sondern acctstarttime+3600 und dann soll er abgelaufen sein. Ich hoffe das ist verständlich.


//// Edit

acctstarttime ist das erste mal Anmelden an den Hotspot

[thomas_w]

Wie immer ist es äußerst hilfreich:

a) die Tabellenstrukturen der beteiligten Tabelle liefert (CREATE TABLE ...)
b) und ein paar sinnvolle Testdaten zu liefern (INSERT INTO ... )

Dann reden wir alle nachvollziehbar vom Gleichen. Es ist leider mühselig, die Daten von den Bilder abzuschreiben, insbesondere weil ein JOIN zwischen den beiden Tabellen notwendig ist, um beendete SESSIONs pro User zu erkennen.

Nur soviel zu Berechnung des Sessionende (acctstarttime + 3600 Sekunden)

/* 1 Stunde = 3600 Sekunden */
SELECT ADDTIME('2011-01-03 10:27:31','01:00:00') AS session_ende;
+---------------------+
| session_ende        |
+---------------------+
| 2011-01-03 11:27:31 |
+---------------------+
1 row in set (0.00 sec)

mysql>



/* 1 Stunde = 3600 Sekunden */
SELECT ADDTIME(acctstarttime,'01:00:00') FROM radacct 
 WHERE UserName='%{%k}'

Das Problem ist aber jetzt, die noch nicht beendete Session zu finden.

Grüße
Thomas

[disem]

CREATE TABLE `radacct` (
  `radacctid` bigint(21) NOT NULL auto_increment,
  `acctsessionid` varchar(64) NOT NULL default '',
  `acctuniqueid` varchar(32) NOT NULL default '',
  `username` varchar(64) NOT NULL default '',
  `groupname` varchar(64) NOT NULL default '',
  `realm` varchar(64) default '',
  `nasipaddress` varchar(15) NOT NULL default '',
  `nasportid` varchar(15) default NULL,
  `nasporttype` varchar(32) default NULL,
  `acctstarttime` datetime default NULL,
  `acctstoptime` datetime default NULL,
  `acctsessiontime` int(12) default NULL,
  `acctauthentic` varchar(32) default NULL,
  `connectinfo_start` varchar(50) default NULL,
  `connectinfo_stop` varchar(50) default NULL,
  `acctinputoctets` bigint(20) default NULL,
  `acctoutputoctets` bigint(20) default NULL,
  `calledstationid` varchar(50) NOT NULL default '',
  `callingstationid` varchar(50) NOT NULL default '',
  `acctterminatecause` varchar(32) NOT NULL default '',
  `servicetype` varchar(32) default NULL,
  `framedprotocol` varchar(32) default NULL,
  `framedipaddress` varchar(15) NOT NULL default '',
  `acctstartdelay` int(12) default NULL,
  `acctstopdelay` int(12) default NULL,
  `xascendsessionsvrkey` varchar(10) default NULL,
  PRIMARY KEY  (`radacctid`),
  KEY `username` (`username`),
  KEY `framedipaddress` (`framedipaddress`),
  KEY `acctsessionid` (`acctsessionid`),
  KEY `acctsessiontime` (`acctsessiontime`),
  KEY `acctuniqueid` (`acctuniqueid`),
  KEY `acctstarttime` (`acctstarttime`),
  KEY `acctstoptime` (`acctstoptime`),
  KEY `nasipaddress` (`nasipaddress`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

CREATE TABLE `radcheck` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `username` varchar(64) NOT NULL default '',
  `attribute` varchar(64) NOT NULL default '',
  `op` char(2) NOT NULL default '==',
  `value` varchar(253) NOT NULL default '',
  `kunde` varchar(100) NOT NULL,
  `time` int(14) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `username` (`username`(32))
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

Was passieren soll "kleinster accstarttime Wert" + 3600 (Max-All-Session-attribute) < aktuelle Zeit.

Es kann aber auch deutlich höher werden als eine Stunde. Wenn das "True" ist, dann soll folgendes passieren :



RADIUS-Serverlog :

[sql_log]       expand: INSERT INTO radacct (AcctSessionId, 
UserName,
NASIPAddress, 
FramedIPAddress,
 AcctStartTime, 
AcctStopTime,    
 AcctSessionTime, 
AcctTerminateCause) VALUES                         
 ('%{Acct-Session-Id}',
 '%{User-Name}',
 '%{NAS-IP-Address}',
'%{Framed-IP-Address}', 
'0', '%S',
 '%{Acct-Session-Time}',
'%{Acct-Terminate-Cause}'); ->

 INSERT INTO radacct 
(AcctSessionId, 
UserName,    
NASIPAddress, 
FramedIPAddress, 
AcctStartTime, 
AcctStopTime,         
 AcctSessionTime, 
AcctTerminateCause) VALUES                    
 ('4d245cf100000000',
 'i4mnpj', '0.0.0.0',       
'192.168.6.2', 
'0',
 '2011-01-05 12:08:29', 
'62',    
 'Session-Timeout');

Ein INSERT AcctTerminateCause = Session-Timeout. Kann man sowas überhaupt in eine Abfrage verpacken?


////EDIT

Vlt. hilft das noch weiter! Das wäre z.B. wenn man eine begrenzte Zeit am Tag hat.

http://wiki.freeradius.org/Rlm_sqlcounter


sqlcounter dailycounter {

driver = "rlm_sqlcounter"
counter-name = Daily-Session-Time
check-name = Max-Daily-Session
sqlmod-inst = sqlcca3
key = User-Name
reset = daily
query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"

}

[thomas_w]

Okay, ich habe mal die Tabellen erzeugt (CREATE) und den einen Datensatz eingefügt (INSERT).
Hat funktioniert, aber hast Du nicht mehr Testdaten (so jeweils 10 sinnvolle Datensätze pro Tabelle). So einen Dump kannst Du aus phpMyAdmin erzeugen (soweit ich weiß).

Anhand der Testdaten kann Du dann nochmal erklären, was das gewünschte Ergebnis sein soll.


Grüße
Thomas

[disem]

Ich hab 2 Ticket angelegt -
Ein Ticket brauch immer 2 Einträge!

1 Stunde und 1 Mintue

INSERT INTO `radcheck` (`id`, `username`, `attribute`, `op`, `value`, `kunde`, `time`) VALUES (1, 'mruhnt', 'Cleartext-Password', ':=', 'b610VM', '5', 1294407926),
(2, 'mruhnt', 'Max-All-Session', ':=', '3600', '5', 1294407926),
(7, 'bw97b9', 'Cleartext-Password', ':=', 'sW6VfE', '5', 1294408038),
(8, 'bw97b9', 'Max-All-Session', ':=', '60', '5', 1294408038);

Mit dem 3600er Ticket hab ich mich 2 mal eingeloggt! Das 60 Sekunden Ticket hab ich ablaufen lassen.

INSERT INTO `radacct` 
(`radacctid`,
 `acctsessionid`,
 `acctuniqueid`,
 `username`,
 `groupname`,
 `realm`,
 `nasipaddress`,
 `nasportid`,
 `nasporttype`,
 `acctstarttime`,
 `acctstoptime`,
 `acctsessiontime`,
 `acctauthentic`,
 `connectinfo_start`,
 `connectinfo_stop`, 
`acctinputoctets`, 
`acctoutputoctets`, 
`calledstationid`, 
`callingstationid`,
 `acctterminatecause`,
 `servicetype`,
 `framedprotocol`,
 `framedipaddress`,
 `acctstartdelay`, 
`acctstopdelay`, 
`xascendsessionsvrkey`) 
VALUES (1,
 '4d27246c00000000',
 '5f3ee25b58c0870f',
 'mruhnt', 
'',
 '',
 '0.0.0.0', 
'0', 
'Wireless-802.11', 
'2011-01-07 14:46:26',
 '2011-01-07 14:46:43',
 17, 
'',
 '',
 '',
 8447, 
26684,
 '00-25-9C-D0-00-E1',
 '00-26-82-1B-9F-1B', 
'User-Request',
 '', 
'',
 '192.168.6.2',
 0,
 0, 
''),


(2, 
'4d27275300000000',
 'c1a717008c270275', 
'mruhnt',
 '',
 '',
 '0.0.0.0',
 '0', 
'Wireless-802.11',
 '2011-01-07 14:47:03', 
'2011-01-07 14:47:28',
 25, 
'',
 '',
 '',
 4031, 
13883, 
'00-25-9C-D0-00-E1',
 '00-26-82-1B-9F-1B', 
'User-Request', 
'',
 '', 
'192.168.6.2',
 0, 
0, ''),


(3, 
'4d27278000000000', 
'd785f9e2b7af8e9c',
 'bw97b9', 
'',
 '',
 '0.0.0.0',
 '0',
 'Wireless-802.11',
 '2011-01-07 14:47:52', 
'2011-01-07 14:48:55', 
63, 
'',
 '', 
'', 
1590,
 9293,
 '00-25-9C-D0-00-E1', 
'00-26-82-1B-9F-1B',
 'Session-Timeout',
 '',
 '',
 '192.168.6.2',
 0
, 0, '');

Das Ergebnis müsste "Session-Timeout" bei acctterminatecause! In dem Link stehen einige Variablen die man nutzen kann, aber ich denke das ist erstmal nicht wichtig!

Ich danke dir schon mal vielmals!

[thomas_w]

Nur mal auf die Schnelle die Ausgabe der offenen Session und die Berechnung des Sessionende.

SELECT ra.acctsessionid, rc.username, rc.attribute, rc.value, ra.acctstarttime, ADDTIME(ra.acctstarttime,'01:00:00') AS sess_ende 
  FROM radacct ra
  JOIN ( SELECT rc.username
           FROM radcheck rc
          WHERE rc.value <> '3600' 
         GROUP BY rc.username
         HAVING COUNT(*) = 2  ) os
    ON os.username = ra.username
  JOIN radcheck rc
    ON rc.username = os.username
ORDER BY ra.acctsessionid, rc.attribute;

+------------------+----------+--------------------+--------+---------------------+---------------------+
| acctsessionid    | username | attribute          | value  | acctstarttime       | sess_ende           |
+------------------+----------+--------------------+--------+---------------------+---------------------+
| 4d27278000000000 | bw97b9   | Cleartext-Password | sW6VfE | 2011-01-07 14:47:52 | 2011-01-07 15:47:52 |
| 4d27278000000000 | bw97b9   | Max-All-Session    | 60     | 2011-01-07 14:47:52 | 2011-01-07 15:47:52 |
+------------------+----------+--------------------+--------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql>

Stimmt der Ansatz?

Ich melde mich heute abend wieder..

Grüße
Thomas

[disem]

sess_ende Ist nur zur berechnung, oder?

WHERE rc.value <> '3600' Es können durchaus auch 1-6 Monate in Sekunden kommen. Aber das war vermutlich nur zum testen?

Sieht sehr gut aus! Vielen dank!

Ich hab ja mal ein wenig auf deiner HP geguckt (:

"Pendler müsste man sein...(Richtung Sylt)"

Bis vor 2 Monaten war ich das noch!

[thomas_w]

Zitat von disem

sess_ende Ist nur zur berechnung, oder?

WHERE rc.value <> '3600' Es können durchaus auch 1-6 Monate in Sekunden kommen. Aber das war vermutlich nur zum testen?

Sieht sehr gut aus! Vielen dank!

Ja, ist eben ein Beispiel, wie man an die Daten herankommen kann. Der Weg sollte jetzt klarer sein.

Zitat von disem

Ich hab ja mal ein wenig auf deiner HP geguckt (:

"Pendler müsste man sein...(Richtung Sylt)"

Bis vor 2 Monaten war ich das noch!

[OT]
Nach "Sylt" jeden Tag pendeln? Hast Du auf auf Sylt gearbeitet oder gewohnt oder geurlaubt?

Grüße
Thomas