I would need to export the usermanager sessions into a.csv file. Has anyone ever managed to do this? which approach to use?
JSON could work using :serialize:
:put [:serialize to=json [/user-manager/session/print as-value ]]
Or for a CSV, it’s often easier to use :foreach with a :put or :set $list ($list,“$user,$started,…”) inside. Or alternatively abusing “print as-value where” to make it one line:
/user-manager/session/print as-value where [:put "$".id",$user,$"acct-session-id",$"nas-ip-address",$"calling-station-id",$download,$started,$uptime,$($status->0),$"last-accounting-packet"" ]
*1,test,84000018,127.0.0.1,192.168.xx.148,0,2024-06-21 14:29:12,00:00:00,start,2024-06-21 14:29:12
*2,test,84000019,127.0.0.1,192.168.xx.148,0,2024-06-21 14:30:24,00:00:00,start,2024-06-21 14:30:24
Third potential, in 7.16beta… :serialize to=dsv seperator=“,” was added … but it does not work with /user-manager/session/print’s array – I think, since the $status is itself an array…so rows are not one-dimentional & “CSV” does not really supported lists-in-lists.
I also thought about using foreach but I thought there was a simpler way. I’ll do some tests tomorrow. thanks for now
Hi, I’m resuming this discussion after a long time as the project had been put on hold. Now I have resumed and from the proposed string I have created a script that inserts the session rows into a .csv and keeps it updated. the problem is that in each row the header is inserted again and some rows are duplicated. How could I improve it?
{
:local filename "session_data.csv"
:local fileExists ""
:set fileExists [/file find where name=$filename]
:if ($fileExists = "") do={
/tool fetch url="http://127.0.0.1/" dst-path=$filename mode=http
/file set $filename contents="ID,User,Acct Session ID,NAS IP Address,Calling Station ID,Download (MB),Started,Uptime (s),Status,Last Accounting Packet\r\n"
}
:foreach session in=[/user-manager/session/print as-value] do={
:local id ($session->"id")
:local user ($session->"user")
:local acctSessionId ($session->"acct-session-id")
:local nasIpAddress ($session->"nas-ip-address")
:local callingStationId ($session->"calling-station-id")
:local download ($session->"download")
:local started ($session->"started")
:local uptime ($session->"uptime")
:local status ($session->"status")
:local lastAccountingPacket ($session->"last-accounting-packet")
# Formatta i dati in formato CSV
:local csvLine "$id,$user,$acctSessionId,$nasIpAddress,$callingStationId,$download,$started,$uptime,$status,$lastAccountingPacket\r\n"
# Aggiungi la nuova riga al contenuto esistente del file
:local currentContent [/file get $filename contents]
/file set $filename contents=($currentContent . $csvLine)
}
}
I was hoping the new [:serialize $array to=dsv delim=“,”] introduced in v7.16 would help. But it does not like the data from [/user-manager/session/print].
And I thought your issue was not using a [:tostr $var] in places – you can run into troubles like “duplicated lines” … if variable going into a string is an array type.
But I think your issue may have been using “filename” as your variable name… If any command along the chain uses same name you, can get into trouble. I missed at first (since I’m not sure filename is used) — I thought this a “5 minute problem”, but half hour later to try renaming “filename” to “infile”.
In the process, I updated to not use /tool/fetch to create file as recent V7 has a “/file/add name=”. Since I thought that might be an issue.
Anyway here is a cleaned up version of your script:
{
:local infile "sessions.csv"
:local csvstr ""
:onerror e in={ :set csvstr [/file get $infile contents] } do={
/file add name=$infile
/file set $infile contents="ID,User,Acct Session ID,NAS IP Address,Calling Station ID,Download (MB),Started,Uptime (s),Status,Last Accounting Packet\r\n"
}
:local rows ""
:foreach session in=[/user-manager/session/print show-ids as-value] do={
:local id ($session->".id")
:local user ($session->"user")
:local acctSessionId ($session->"acct-session-id")
:local nasIpAddress ($session->"nas-ip-address")
:local callingStationId ($session->"calling-station-id")
:local download ($session->"download")
:local started ($session->"started")
:local uptime ($session->"uptime")
:local status ($session->"status")
:local lastAccountingPacket ($session->"last-accounting-packet")
# Formatta i dati in formato CSV
:local csvLine "$[:tostr $id],$[:tostr $user],$[:tostr $acctSessionId],$[:tostr $nasIpAddress],$[:tostr $callingStationId],$[:tostr $download],$[:tostr $started],$[:tostr $uptime],$[:tostr $status],$[:tostr $lastAccountingPacket]\r\n"
:set rows "$rows$csvLine"
}
/file set $infile contents="$csvstr$rows"
:put [/file get $infile contents]
}
Dunno what ROS version you’re using, but the “:onerror” is relatively newer than /file add… so could use an :if or :do {} on-error= instead if older V7. If V6, well, I don’t think it has a “/file add”…
Thanks for your contribution, by directly running the indicated script I found that the header was overwritten when presenting new entries. I modified the script as follows.
It seems to work very well, now I need to figure out how I would enter decimals in the download and upload field. Your script showed the field in bytes.
Furthermore, I would like to ensure that every month a file is generated with only the sessions of that month. I think this is easy by integrating into the foreach ~ date.
Thanks for your advice
{
:local infile "sessions.csv"
:local csvstr ""
:onerror e in={ :set csvstr [/file get $infile contents] } do={
/file add name=$infile
:delay 2s;
/file set $infile contents="ID,User,Acct Session ID,NAS IP Address,Calling Station ID,User Address,Started,Ended,Terminate Cause,Uptime (s),Download (MiB),Upload (MiB)\r\n"
:set csvstr [/file get $infile contents]
}
:delay 2s;
# Controlla se l'intestazione esiste già
:if ([:len $csvstr] <= 0) do={
/file set $infile contents="ID,User,Acct Session ID,NAS IP Address,Calling Station ID,User Address,Started,Ended,Terminate Cause,Uptime (s),Download (MiB),Upload (MiB)\r\n"
:set csvstr [/file get $infile contents]
}
:local rows ""
:foreach session in=[/user-manager/session/print show-ids as-value] do={
:local id ($session->".id")
:local user ($session->"user")
:local acctSessionId ($session->"acct-session-id")
:local nasIpAddress ($session->"nas-ip-address")
:local callingStationId ($session->"calling-station-id")
:local useraddress ($session->"user-address")
:local started ($session->"started")
:local ended ($session->"ended")
:local terminatecause ($session->"terminate-cause")
:local uptime ($session->"uptime")
:local download ($session->"download")
:local upload ($session->"upload")
:local status ($session->"status")
:local lastAccountingPacket ($session->"last-accounting-packet")
:local downloadMib ($download / 1048576)
:local uploadMib ($upload / 1048576)
# Formatta i dati in formato CSV
:local csvLine "$[:tostr $id],$[:tostr $user],$[:tostr $acctSessionId],$[:tostr $nasIpAddress],$[:tostr $callingStationId],$[:tostr $useraddress],$[:tostr $started],$[:tostr $ended],$[:tostr $terminatecause],$[:tostr $uptime],$[:tostr $downloadMib],$[:tostr $uploadMib]\r\n"
:set rows "$rows$csvLine"
}
# Aggiungi nuove righe al file CSV senza sovrascrivere
/file set $infile contents="$csvstr$rows"
:put [/file get $infile contents]
}
I’m still not sure why using “filename” as variable likely cause the issue. Good to hear, I ended up re-writing half your script so never know if works… .
So you’re new downloadMib / uploadMib will just round and lose the “after the decimal part”, since RouterOS only plain integers.
So need to more math and “string building” using the download/upload, before doing the :set, inside the :foreach loop. While this is possible, it’s not so easy, RouterOS script does not support “decimals” (or “float”/“floating point”) numbers. See http://forum.mikrotik.com/t/function-to-convert-b-kib-mib-or-gib-in-a-script/155540/1 (and other if you search forum)- which isn’t exactly what you want but shows the kinda math needed. Basically you have to use the % modulo operator in order build-you-own “fake” a decimal/“floating point” in scripting. Note: you are starting from “Bytes”, not bits in /user-manager/session.