Export session .csv

abbio90 · Sat Jun 22, 2024 8:12 pm

I would need to export the usermanager sessions into a.csv file. Has anyone ever managed to do this? which approach to use?

Amm0 · Sat Jun 22, 2024 10:17 pm

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.

abbio90 · Sun Jun 23, 2024 12:04 am

I also thought about using foreach but I thought there was a simpler way. I'll do some tests tomorrow. thanks for now

abbio90 · Sat Oct 05, 2024 8:24 pm

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)
}


}

Amm0 · Sat Oct 05, 2024 11:20 pm

I'm resuming this discussion after a long time as the project had been put on hold.

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"...

abbio90 · Sun Oct 06, 2024 6:37 pm

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]

}

Amm0 · Sun Oct 06, 2024 7:40 pm

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 viewtopic.php?t=182904&sid=c6be2ba2a526 ... 7dc30b04bd (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.

Export session .csv

Export session .csv

Re: Export session .csv

Re: Export session .csv

Re: Export session .csv

Re: Export session .csv

Re: Export session .csv

Re: Export session .csv

Who is online