Get user who changed the state of a switch [Solved]

[wizzard72]

If you click on the Log button of a switch you see the following table:

TextLog.png (26.21 KiB) Viewed 1338 times

How can I read the User in dzVents? I only need to know the last user who changed the state of the switch.

In this case the User is = EventSystem/home/pi/domoticz/dzVents/runtime/dzVents.lua

[waaren]

If you click on the Log button of a switch you see the following table:
TextLog.png

How can I read the User in dzVents? I only need to know the last user who changed the state of the switch.

In this case the User is = EventSystem/home/pi/domoticz/dzVents/runtime/dzVents.lua

This feature is not completely implemented in domoticz yet. It is something that needs to picked up but might take some time.

[wizzard72]

Thanks for the information.

[maxim009]

Hello, any updates please?
Get username of last change could be pretty good to block changes by dzvents for a hour after user exceptional change...
Or is it possible detect that last change of device was not performed by dzvents?

[willemd]

You could launch a SQL query from DZvents on the lightinglog table to get the user.

For an example how to run a sql query from dzvents you can look in this thread:
viewtopic.php?t=39074

[maxim009]

Hello willemd, thanks a lot for hint.

Here is my snap of code - wait 60 minutes after user change...

Code: Select all

        local command = 'sqlite3 /path/to/domoticz.db \"SELECT User, Date FROM LightingLog WHERE DeviceRowID = 145 ORDER BY Date DESC LIMIT 1\" ' -- 145 is IDX of device
        local handle = assert(io.popen(command))
        for line in handle:lines() do
            -- domoticz.log(line, domoticz.LOG_INFO)

            i = 0
            user = ""
            date = ""
            for col in string.gmatch(line, "([^|]+)") do
                if (i == 0) then     -- User
                    user = col
                elseif (i == 1) then -- Date
                    date = col
                end                
                i = i + 1
            end

            logDate = Time(date)
            if (string.match(user, "dzVents.lua") ~= "dzVents.lua" and logDate.minutesAgo < 60) then
                -- some stuff....
                return 1
            end

        end
        handle:close()

[Bomans007]

Hi,

thanks for posting your findings to get the "action user" via a database query, is exactly what I was searching for.
I tried it out on my Windows system, but I cannot get it working.

I suppose it has something to with the reference of the domoticz.db location(windows is not linux) and tried out different things.
see below my code snippet(= not all the code for reading/focus convenience)

Code: Select all

[...]
	execute = function(domoticz, triggeredItem, info)
	    print("Script-getActionUser:sprint launched 1")
        local command = 'sqlite3 /path/to/domoticz.db \"SELECT User FROM LightingLog WHERE DeviceRowID = 364 ORDER BY Date DESC LIMIT 1\" ' -- 145 is IDX of device
        local handle = assert(io.popen(command))

        for line in handle:lines() do
            print("Script-getActionUser:sprint launched 3")
 [...]      

in this code I changed "/path/to/domoticz.db" in below listed flavours to define the right domoticz.db location in window:

• used the real windows path to the domoticz.db file -> "C:/ProgramData/Domoticz/domoticz.db" where I changed all the "\" to the linux variant "/" otherwise the command is not accepted which results in "C:/Users/Frank/Downloads/Domoticz.db"

• I removed the disk reference "C" -> "ProgramData/Domoticz/domoticz.db" -> s

• I made even a Domoticz.db file copy to the default windows home folder(\user\username) -> "User/Frank/Domoticz.db"

• added the disk reference to home location -> "C:/User/Frank/Domoticz.db" ->

• removed complete home path since the home level holds directly the DB file -> "/Domoticz.db or Domoticz.db" ->

With all above attempts I always get an empty "handle" object back without any lines because it never enters the for loop.
Any idea how to reference the right windows location in above salite3 command or what I'm doing wronghere?

[willemd]

Step by step try to find where the error is.

First test whether you can type the command from the commandline and see whether it opens de domoticz.db and executes the query. Once that is confirmed you can continue with the dzvents script.

After you have assigned the line to the local command variable in the script just write that variable to the log file to check whether it is formed correctly.

Once you have assigned the "local handle" also print that variable to the log file, or use dumptable or other commands if it is not a string. Just to see exactly what you are getting back.

Pay special attention to the / and \. In Dzvents the \ is a special character to make sure he next character is not interpreted as special but just taken as-is. for example the ".