Instructions on how to run

jhaines1989 commented 6 years ago

Hi Peter ,

This might be a silly question and I apologize in advance.

Is there any instruction on how I actually run this and what OS it needs to be run on ?

peterM commented 6 years ago

Hi Jordan The tool is mainly for Windows (10) OS.

You can run executable from console or install it as Windows service. How to use:

Download release zip file.
Unpack zip file somewhere
Open and update config file 'MalikP.Ubiquiti.DatabaseExporter.Service.exe.config' to fit your environment - check readme
Create destination folder for files,
Create database for exported data and create schema from script
Run Console (WIN+R and enter CMD)
Navigate to path where tool is extracted
run 'MalikP.Ubiquiti.DatabaseExporter.Service.exe'
data should be extracted to target folder (depending from configuration) & data should be written to database (depending from configuration)

jhaines1989 commented 6 years ago

Thanks for the info. I can't seem to find any files that end in service.exe in the .zip I downloaded. There is an app.config that I have found that seems to have the parameters mentioned in the readme.

Now all I need know which file will execute the program and I don't have one called "MalikP.Ubiquiti.DatabaseExporter.Service.exe"

peterM commented 6 years ago

Did you download zip file from here ?

jhaines1989 commented 6 years ago

Ah I was clearly downloading the source code !

Thanks for your help and sorry for bothering you with something so simple.

peterM commented 6 years ago

No problem. I hope it will work well also in your environment..

peterM commented 6 years ago

@abzkebabs Could you provide me some feedback ? like:

did it work in your environment ?
how do you like it ?
is it helpfull ? Thanks.

jhaines1989 commented 6 years ago

Hi Peter ,

I did give it a go and the CMD prompt just stayed on all day with out any data being written to my file location. I was just trying to do a dump to file as a test first before moving to SQL and the config had no SQL connection configured in it.

any ideas on why it would just hang and not do anything ? I was running it on Server 2016 FYI.

Was planning on coming back to have another go soon when I have some spare time at work.

Thanks

peterM commented 6 years ago

Could you send me your config file (without passwords) to p.malik@malikp.eu and also attach some information about your controller ?

Is it CloudKey or Windows based controller ?

or try this (valid in case you are using cloudkey):

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
    </startup>
    <appSettings>
        <add key="Use-Encrypted-Psswords" value="false"/>

        <!-- certificate have to be in certification store local machine -->
        <add key="Encryption-Certificate-Identifier" value="[Encryption-Certificate-Serial-Number]" />

        <add key="Backup-Path" value="C:\UnifiBackup" />
        <add key="Mongo-Connection-String" value="mongodb://127.0.0.1:27117" />

        <!-- Everytim turn off pooling by `Pooling=false;` -->
        <add key="Sql-Connection-String" value="Server=[Server];Database=Ubiquiti-Unifi-Backup;Pooling=false;" />
        <add key="Sql-User-Id" value="" />

        <!-- password encrypted by certificate -->
        <add key="Sql-User-Password" value="" />

        <!-- have to be greater than 0-->
        <add key="Sql-Batch-Size" value="100"/>

        <add key="Export-To-FS" value="true" />
        <add key="Export-To-DB" value="false" />

        <!-- 
            use ssl tunel when you want extract data from cloud key 
            in case you want to extract data from windows based controller set 'Use-SSH-Tunel' to 'False'
        -->
        <add key="Use-SSH-Tunel" value="true" />
        <add key="SSH-Tunel-Host" value="IP-OF-UNIFI-CONTROLLER" />
        <add key="SSH-Tunel-UserName" value="admin" />

        <!-- password encrypted by certificate -->
        <add key="SSH-Tunel-Password" value="YOUR-PASSWORD" />
        <add key="SSH-Forward-Host-From" value="127.0.0.1" />
        <add key="SSH-Forward-Port-From" value="27117" />
        <add key="SSH-Forward-Host-To" value="127.0.0.1" />
        <add key="SSH-Forward-Port-To" value="27117" />

        <add key="Blacklisted-Table-Names" value=""/>

    </appSettings>
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <assemblyIdentity name="System.Buffers" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral" />
                <bindingRedirect oldVersion="0.0.0.0-4.0.2.0" newVersion="4.0.2.0" />
            </dependentAssembly>
        </assemblyBinding>
    </runtime>
</configuration>

and do not forget change key:

<add key="SSH-Tunel-Host" value="IP-OF-UNIFI-CONTROLLER" />

and key :

<add key="SSH-Tunel-Password" value="YOUR-PASSWORD" />

juanpmox commented 5 years ago

Hello Peter, in first place, thanks for this awsome utility!

I'm using it on my network, and dumping data from CloudKey to file system is going wel.

I have problems when try to dump data to sql. I'm using a configuration based on the settings you suggest on Sept 17, 2018

The SQL related lines, are

    <!-- Everytim turn off pooling by `Pooling=false;` -->
    <add key="Sql-Connection-String" value="Server=MYSERVER;Database=Ubiquiti-Unifi-Backup;Pooling=true;" />
    <add key="Sql-User-Id" value="user-sql-login" />

    <!-- password encrypted by certificate -->
    <add key="Sql-User-Password" value="user-sql-password" />

Monitoring the process, I've found that none connections are made from the MalikP.Ubiquiti.DatabaseExporter.Service.exe to the SQL Server.

Am I missing some configuration or pre-req installation?

Thank you some much, in advance. Best regards.

peterM commented 5 years ago

Hi @juanpmox

Please try these steps.:

create empty database (best is to use this name Ubiquiti-Unifi-Backup)
- or you can use script 1. Create_db_script.sql in install folder https://github.com/peterM/Ubiquiti-Unifi-Data-Exporter/tree/master/install
execute script 2. Create_schema.sql also from https://github.com/peterM/Ubiquiti-Unifi-Data-Exporter/tree/master/install
use this new db in your ConnectionString

please let me know if it helped or not

juanpmox commented 5 years ago

Unfortunately, same result :(

DB Ubiquiti-Unifi-Backup, created with script
DB schema, created, with the script too

Ran the tool again, from Windows cmd.exe, but none data is imported to DB

peterM commented 5 years ago

Hmm .... 🤔 I have several questions:

Did you try run it as administrator ?
Did you run from command prompt / console or as windows service ?
did you allow export to db ? <add key="Export-To-DB" value="true"/>
could you send me your config (anonymized)

juanpmox commented 5 years ago

my comments below: Did you try run it as administrator ? => Yes Did you run from command prompt / console or as windows service ? => Yes, form console, and with scheduled task did you allow export to db ? => Yes could you send me your config (anonymized) => Yes, I'll sending you the config file to p.malik@malikp.eu

peterM commented 5 years ago

@juanpmox Thank you for mail. I checked config, and i do not see any error. Is exactly the same as mine. I think there have to be something wrong in code.

Could you try with SA user ?

For example i use something like this:

<add key="Sql-Connection-String" value="Server=sql.domain.local;Database=Ubiquiti-Unifi-Backup;Pooling=false;"/>
<add key="Sql-User-Id" value="sa"/>
<add key="Sql-User-Password" value=""/>

I think I have to add some more logging.

EDIT Could you try new version 1.3.1.0 from here ?

juanpmox commented 5 years ago

Hello Peter, thanks for being here!

I've tried with the "sa" user, with the same result

Below, you will see the output of the process (from the console). I've got the information exported on FS, but none records are saved on the SQL server...even worse, none process connection detected form the .exe to SQL, checked with SQL Activity monitor, and Systernals Procees Monitor :(

D:\UniFi Database Exporter>MalikP.Ubiquiti.DatabaseExporter.Service.exe Current collection name: stat_minute Collection Name stat_minute saved to file system Current collection name: system.indexes FS: Blacklisted table: [ace_stat.system.indexes] Current collection name: stat_5minutes Collection Name stat_5minutes saved to file system Current collection name: stat_hourly Collection Name stat_hourly saved to file system Current collection name: stat_daily Collection Name stat_daily saved to file system Current collection name: stat_monthly Collection Name stat_monthly saved to file system Current collection name: stat_life Collection Name stat_life saved to file system Current collection name: stat_archive Collection Name stat_archive saved to file system Current collection name: stat_dpi Collection Name stat_dpi saved to file system Current collection name: startup_log Collection Name startup_log saved to file system Current collection name: heatmap Collection Name heatmap saved to file system Current collection name: system.indexes FS: Blacklisted table: [ace.system.indexes] Current collection name: voucher Collection Name voucher saved to file system Current collection name: admin Collection Name admin saved to file system Current collection name: networkconf Collection Name networkconf saved to file system Current collection name: rogueknown Collection Name rogueknown saved to file system Current collection name: privilege Collection Name privilege saved to file system Current collection name: radiusprofile Collection Name radiusprofile saved to file system Current collection name: scheduletask Collection Name scheduletask saved to file system Current collection name: setting Collection Name setting saved to file system Current collection name: routing Collection Name routing saved to file system Current collection name: portconf Collection Name portconf saved to file system Current collection name: alarm Collection Name alarm saved to file system Current collection name: firewallrule Collection Name firewallrule saved to file system Current collection name: payment Collection Name payment saved to file system Current collection name: tag Collection Name tag saved to file system Current collection name: event Collection Name event saved to file system Current collection name: wlangroup Collection Name wlangroup saved to file system Current collection name: map Collection Name map saved to file system Current collection name: verification Collection Name verification saved to file system Current collection name: rogue Collection Name rogue saved to file system Current collection name: stat Collection Name stat saved to file system Current collection name: portalfile Collection Name portalfile saved to file system Current collection name: wlanconf Collection Name wlanconf saved to file system Current collection name: firewallgroup Collection Name firewallgroup saved to file system Current collection name: hotspotop Collection Name hotspotop saved to file system Current collection name: portforward Collection Name portforward saved to file system Current collection name: heatmappoint Collection Name heatmappoint saved to file system Current collection name: site Collection Name site saved to file system Current collection name: task Collection Name task saved to file system Current collection name: hotspot2conf Collection Name hotspot2conf saved to file system Current collection name: dynamicdns Collection Name dynamicdns saved to file system Current collection name: hotspotpackage Collection Name hotspotpackage saved to file system Current collection name: usergroup Collection Name usergroup saved to file system Current collection name: broadcastgroup Collection Name broadcastgroup saved to file system Current collection name: guest Collection Name guest saved to file system Current collection name: mediafile Collection Name mediafile saved to file system Current collection name: device Collection Name device saved to file system Current collection name: user Collection Name user saved to file system Current collection name: account Collection Name account saved to file system Current collection name: virtualdevice Collection Name virtualdevice saved to file system Current collection name: dhcpoption Collection Name dhcpoption saved to file system Current collection name: dpigroup Collection Name dpigroup saved to file system Current collection name: dpiapp Collection Name dpiapp saved to file system Current collection name: wall Collection Name wall saved to file system Current collection name: dashboard Collection Name dashboard saved to file system Finnished

Thanks and best regards.

Juan Pablo

El dom., 2 jun. 2019 a las 4:15, Peter Malík (notifications@github.com) escribió:

@juanpmox https://github.com/juanpmox Thank you for mail. I checked config, and i do not see any error. Is exactly the same as i personally use it. I think there have to be some defect or something wrong.

Could you try with SA user ?

For example i use something like this:

I think I have to add some more logging.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/peterM/Ubiquiti-Unifi-Data-Exporter/issues/1?email_source=notifications&email_token=AJH6ZJI2XSFQA5K3QXR5U4DPYNXQDA5CNFSM4FTATXC2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODWXPQJQ#issuecomment-498006054, or mute the thread https://github.com/notifications/unsubscribe-auth/AJH6ZJMVBV53VYA4ZK2HTK3PYNXQDANCNFSM4FTATXCQ .

peterM commented 5 years ago

@juanpmox this log is from not the most new version right ? I think the problem is that sql Exporter instance is not created so is not used.

In the most new version i replaced 'IoC' library and also add log to show you what exporters are created. Please try this new version and sorry for these problems.

In case SQL exporter is created you should see as firs lines in console after start.

There are [2] resolved exporters.
        There is [UnifiToFileSystemExporter] exporter resolved.
        There is [UnifiToSqlDatabaseExporter] exporter resolved.

juanpmox commented 5 years ago

Hi Peter,

I'm back just to say that my problem is SOLVED now. My issue was related to UAC on the Windows 10 computer that I used to get the Unifi data. To get the Exporter working, in my case, I need to do that:

Download release zip file.
Right click the downloaded, zip file, -> Properties -> mark the checkbox "Unblock" -> then Ok
Unpack zip file somewhere
...

Now I' ve get information exported to DB and FS, without any problems.

Thank you very much for this tool, and you support.

Best regards

peterM commented 5 years ago

Hi. Oh niice. Thank you also for patience feedback and warming words :) Peter

rkwtemp1 commented 3 years ago

1st. thanks a lot for this utility. unfortunately i do not get any traffic stats exported which would be in in mongodb ace_stats. like daily a or hourly traffic statistics. i only receive a folder named "ace" in the UnifiBackup-Folder. by the waay .. i'm using the cloudkey g2+ please give me a tip about what maybe is set up wrong.

peterM commented 3 years ago

Hi @rkwtemp1 i am not sure. I did not test it with Gen 2 or UDM/UDM-Pro. I have UDM-Pro now at home so maybe if i will have spare time i will try it. For now i could not say more about it. Maybe during time they changed something. P.

EDIT:

I know where the problem is .. i will try export my data and then will write you workaround until i fix it :)

EDIT2:

Blacklist these tables like is bellow:

<add key="Blacklisted-Table-Names" value="ace.ipsalert;ace.featuremigration;ace.storeddpistats" />

And i think you already used to login root instead of admin

<add key="SSH-Tunel-UserName" value="root" />

Then it should work.

EDIT3:

Download new version 1.4.0.0 Do NOT overwrite your config. At least if you do not want to configure everything again ;) And do not forget to update your SQL DB Schema if you use also SQL DB. How to do that please check Readme. I added section to end of the document.

EDIT4:

Last version of controller: 6.0.41 has also new table ace.apgroup whitch is not supported now by utility. Add it to blacklist.

Whaagh4800 commented 4 months ago

Hi There, I see this is a bit old. I am wondering does this work on a device that is linked to the unifi cloud dashboard?

peterM commented 4 months ago

Yes its old .. i did not have lot of time to upgrade it to most new version of UniFi Network Unfortunately No .. it will not work .. It works the way that you connect to internal MongoDB database and just export data. So cloud version of Dashboard will not work.

Whaagh4800 commented 4 months ago

Thank you very much for the quick response, appreciate that.

peterM / Ubiquiti-Unifi-Data-Exporter