Data de hoje na consulta SQL

[pedrohsc.main]

~Trablhando em Lansweeper~

Gostaria que minha consultar trouxesse sempre a data atual.

Sql : 
Select Distinct tblAssets.AssetName As micro,
  tblAssets.Memory As memoria,
  tblAssets.Processor As Processador,
  tblAssets.IPAddress,
  tblAssets.Username,
  tblAssetCustom.Model,
  tsysOS.OSname,
  tblAssets.Lastseen
From lansweeperdb.dbo.tblAssets
  Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
      tblOperatingsystem.AssetID
  Inner Join lansweeperdb.dbo.tblAssetCustom On tblAssets.AssetID =
      tblAssetCustom.AssetID
  Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join lansweeperdb.dbo.tsysOS On tblAssets.OScode = tsysOS.OScode

 

A coluna *tblAssets.Lastseen* me tras a última varredura do sistema.

Como posso por condição nela, pra que ela me traga sempre a data atual de forma automática ??

Para compreender o motivo. eu criei um ALERTA no sistema do lansweeper, então queria que, disparasse somente os micros "varridos" do dia.

Alguém pode me ajudar por favor ?

 

Saudaçoes !

[luisgustavogomes]

@pedrohsc.main, boa tarde!

Como está montando a query podes utilizar um das seguintes funções diretamente. 

 

-- higher precision functions 
SELECT SYSDATETIME()       AS 'DateAndTime';        -- return datetime2(7)       
SELECT SYSDATETIMEOFFSET() AS 'DateAndTime+Offset'; -- datetimeoffset(7)
SELECT SYSUTCDATETIME()    AS 'DateAndTimeInUtc';   -- returns datetime2(7)
-- lesser precision functions - returns datetime
SELECT CURRENT_TIMESTAMP AS 'DateAndTime'; -- note: no parentheses   
SELECT GETDATE()         AS 'DateAndTime';    
SELECT GETUTCDATE()      AS 'DateAndTimeUtc'; 

 

Abraços

[pedrohsc.main]

Desculpa, sou inexperiente.

Não entendi nem como usar isso no script que trouxe pra explicar minha dúvida.

 

 Em termo de "sintaxe" estou dizendo.

 

Editado 29 de Novembro de 2021 por pedrohsc.main

[luisgustavogomes]

Opa

Segue.

Select Distinct tblAssets.AssetName As micro,
  tblAssets.Memory As memoria,
  tblAssets.Processor As Processador,
  tblAssets.IPAddress,
  tblAssets.Username,
  tblAssetCustom.Model,
  tsysOS.OSname,
  SYSDATETIME()  AS 'Lastseen'
From lansweeperdb.dbo.tblAssets
  Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
      tblOperatingsystem.AssetID
  Inner Join lansweeperdb.dbo.tblAssetCustom On tblAssets.AssetID =
      tblAssetCustom.AssetID
  Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join lansweeperdb.dbo.tsysOS On tblAssets.OScode = tsysOS.OScode

[pedrohsc.main]

Olha, trouxe outro exemplo pra mostrar.

Eu tenho um escript, onde ele me diz quais impressoras estão ficando sem TONER.

o Resultado resumido é esse.

Neste caso, se a impressora do dia 22 e 26 não foi trocado o toner, não me interessa mais sabe, só quero que o relatório me traga as do dia.

29/11/2021 10:08:15    
26/11/2021 10:07:54    
29/11/2021 10:07:35    
29/11/2021 10:27:04    
22/06/2021 10:25:16    

Da forma que me trouxe acima, ele põe todas essas datas em alias de forma que o dia 22, se transforma em dia 29 e saí no relatório.

Segue o script : 

Select Top 1000000 *,
  Case
    When ([Trocar Toner].Restando <= 5) Then 'red'
  End As foregroundcolor
From (Select tsysAssetTypes.AssetTypeIcon10 As icon,
      tblAssets.AssetName As Hostname,
      tblAssetCustom.Model As Modelo,
      Floor(tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum *
      100) As Restando,
      tblCustDevPrinter.Tonername As Insumos,
      tblAssetCustom.Location As Localizacao,
      tblAssets.IPAddress,
      tblAssets.Lastseen As [ultima visualizacao]
    From tblCustDevPrinter
      Inner Join tblAssets On tblCustDevPrinter.AssetID = tblAssets.AssetID
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
      Inner Join tsysAssetTypes On
          tblAssets.Assettype = tsysAssetTypes.AssetType
    Where   tblAssetCustom.State = 1 And tblCustDevPrinter.TonerRemaining >= 0 And
      tblCustDevPrinter.TonerMaximum > 0) As [Trocar Toner]
Where [Trocar Toner].Restando >= 0 And [Trocar Toner].Restando <= (Case
    When [Trocar Toner].Modelo = 'MPS5501b' Then 5
    When [Trocar Toner].Modelo = 'MPS5502b' Then 5
    Else 10
  End)
Order By [Trocar Toner].Restando

 

Essa case " When [Trocar Toner].Modelo = 'MPS5501b' Then 5
    When [Trocar Toner].Modelo = 'MPS5502b' Then 5" É apenas uma condição que crirei para que a impressora só saia no relatório, ao atingir 5%.

 

 

Editado 29 de Novembro de 2021 por pedrohsc.main
novo script.

[luisgustavogomes]

Opa 

@pedrohsc.main, boa noite... 

 

Terias como fazer uma planilha com o result set, completo desse caso ? Pois preciso replicar a query para lhe ajudar... 

Outra questão, qual linguagem de banco de dados estamos falando ? Tsql, PLsql ou outra.

 

No aguardo.

 

Abraços 

[pedrohsc.main]

 

   Oi, estou anexando a planilha que solicitou.

A minha ideia é a seguinte. 
Através deste script que estou montando, vou gerar um gráfico onde ficará me mostrando as máquinas em operação .

 

Este anexo, é referente aos micros WINDOWS 7 que temos no parque.

 

Script na ABA 2

web50repf2fc84f25d52458ba3eebb963e214cd1.xlsx

[luisgustavogomes]

@pedrohsc.main, bom dia!

Criei esse script, conforme sua tabela... agora preciso entender qual é a lógica que você precisa aplicar! 

/*

USE [MASTER]
GO 

;WITH TAB
AS
(
    SELECT micro, memoria,Processador,Model,OSname,Lastseen
    FROM 
    (
    VALUES 
     ('UCS010112D',4096,'Intel Core i5-4590S CPU @ 3.00GHz','OptiPlex 3030 AIO','Win 7','2021-12-13 10:07:23')
    ,('UCS009389D',4096,'Intel Core i3-4160T CPU @ 3.10GHz','HP ProOne 400 G1 AiO','Win 7','2021-12-13 10:07:23')
    ,('UCS009405D',4096,'Intel Core i5-4590S CPU @ 3.00GHz','OptiPlex 3030 AIO','Win 7','2021-12-13 10:07:11')
    ,('UCS006829D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-12-13 10:06:54')
    ,('UCS006138D',4096,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Compaq 4000 Pro SFF PC','Win 7','2021-12-13 10:06:53')
    ,('UCS006525D',2048,'Intel Core i3-2120 CPU @ 3.30GHz','HP Pro 3410 Series','Win 7','2021-12-13 10:06:49')
    ,('UCS006528D',4096,'Intel Core i3-2120 CPU @ 3.30GHz','HP Pro 3410 Series','Win 7','2021-12-13 10:05:58')
    ,('UCS009364D',4096,'Intel Core i3-4130T CPU @ 2.90GHz','HP ProOne 400 G1 AiO','Win 7','2021-12-12 12:00:19')
    ,('UCS006631D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 AiO BR 20 PC','Win 7','2021-12-12 12:00:19')
    ,('UCS006899D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 AiO BR 20 PC','Win 7','2021-12-12 12:00:19')
    ,('UCS009384D',4096,'Intel Core i3-4160T CPU @ 3.10GHz','OptiPlex 3020M','Win 7','2021-12-12 12:00:19')
    ,('UCS005709D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 AiO BR 20 PC','Win 7','2021-12-12 12:00:19')
    ,('UCS006819D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-12-12 12:00:19')
    ,('UCS006895D',4096,'Intel Core i3-3240 CPU @ 3.40GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-12-12 12:00:19')
    ,('UCS009470D',4096,'Intel Core i3-4160T CPU @ 3.10GHz','OptiPlex 3020M','Win 7','2021-12-12 12:00:19')
    ,('UCS005792D',4096,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Compaq 4000 Pro SFF PC','Win 7','2021-12-12 12:00:19')
    ,('UCS009360D',4096,'Intel Core i3-4130T CPU @ 2.90GHz','HP ProOne 400 G1 AiO','Win 7','2021-12-12 12:00:08')
    ,('ROS006316D',4096,'Intel Core i3-2120 CPU @ 3.30GHz','HP Pro 3410 Series','Win 7','2021-12-12 12:00:08')
    ,('UCS005674D',3072,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Pro 3000/3080','Win 7','2021-12-12 12:00:08')
    ,('UCS006030D',2048,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Compaq 4000 Pro SFF PC','Win 7','2021-12-12 12:00:08')
    ,('UCS006937D',4096,'Intel Core i3-3240 CPU @ 3.40GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-12-12 12:00:08')
    ,('UCS009390D',4096,'Intel Core i3-4160T CPU @ 3.10GHz','HP ProOne 400 G1 AiO','Win 7','2021-12-12 12:00:08')
    ,('UCS009418D',4096,'Intel Core i3-4160T CPU @ 3.10GHz','OptiPlex 3020M','Win 7','2021-12-12 12:00:08')
    ,('UCS009467D',4096,'Intel Core i3-4160T CPU @ 3.10GHz','OptiPlex 3020M','Win 7','2021-12-12 12:00:08')
    ,('UCS009411D',4096,'Intel Core i3-4160T CPU @ 3.10GHz','OptiPlex 3020M','Win 7','2021-12-10 12:00:46')
    ,('ROS006526D',4096,'Intel Core i3-2120 CPU @ 3.30GHz','HP Pro 3410 Series','Win 7','2021-12-10 12:00:33')
    ,('UCS005757D',3072,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Pro 3000 Small Form Factor PC','Win 7','2021-12-10 12:00:33')
    ,('UCS006019D',4096,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Compaq 4000 Pro SFF PC','Win 7','2021-12-10 12:00:33')
    ,('UCS006643D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-12-10 12:00:33')
    ,('UCS006926D',4096,'Intel Core i3-3240 CPU @ 3.40GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-12-10 12:00:33')
    ,('UCS009363D',4096,'Intel Core i3-4130T CPU @ 2.90GHz','HP ProOne 400 G1 AiO','Win 7','2021-12-10 12:00:33')
    ,('UCS009352D',4096,'Intel Core i3-4130T CPU @ 2.90GHz','HP ProOne 400 G1 AiO','Win 7','2021-12-08 12:00:47')
    ,('UCS006147D',4096,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Compaq 4000 Pro SFF PC','Win 7','2021-12-08 12:00:47')
    ,('UCS006894D',8192,'Intel Core i3-3240 CPU @ 3.40GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-12-05 12:00:58')
    ,('UCS006652D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-11-12 12:01:04')
    ,('UCS009391D',4096,'Intel Core i3-4160T CPU @ 3.10GHz','HP ProOne 400 G1 AiO','Win 7','2021-10-20 12:00:42')
    ,('UCS006707D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-08-29 12:00:22')
    ,('UCS009349D',4096,'Intel Core i3-4130T CPU @ 2.90GHz','F4J75LT#AC4','Win 7','2021-08-14 12:00:28')
    ,('UCS009354D',4096,'Intel Core i3-4130T CPU @ 2.90GHz','HP ProOne 400 G1 AiO','Win 7','2021-08-14 12:00:28')
    ,('UCS006204D',4096,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Compaq 4000 Pro SFF PC','Win 7','2021-07-07 10:28:00')
    ,('UCS009412D',4096,'Intel Core i3-4160T CPU @ 3.10GHz','OptiPlex 3020M','Win 7','2021-07-02 11:36:26')
    ,('UCS006756D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-06-25 12:01:08')
    ,('UCS006767D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-05-24 16:39:08')
    ,('UCS006418D',6144,'Intel Core i3-2120 CPU @ 3.30GHz','HP Pro 3410 Series','Win 7','2021-05-24 16:09:06')
    ,('UCS006948D',4096,'Intel Core i3-3240 CPU @ 3.40GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-05-24 16:09:06')
    ,('UCSTOTEMRO',4096,'Intel Pentium CPU G4400 @ 3.30GHz',NULL,'Win 7','2021-05-24 12:00:24')
    ,('ROS006337D',4096,'Intel Core i3-2120 CPU @ 3.30GHz','HP Pro 3410 Series','Win 7','2021-05-24 12:00:24')
    ,('UCS006832D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-05-24 12:00:24')
    ,('UCS006133D',4096,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Compaq 4000 Pro SFF PC','Win 7','2021-05-20 16:24:34')
    ,('ROS006771D',4096,'Intel Core i3-3220 CPU @ 3.30GHz','HP Compaq Pro 4300 SFF Brazil PC','Win 7','2021-05-19 12:00:37')
    ,('UCS005458D',4096,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Pro 3000 Small Form Factor PC','Win 7','2021-05-19 12:00:37')
    ,('UCS06033D',4096,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Compaq 4000 Pro SFF PC','Win 7','2021-05-18 13:54:23')
    ,('UCS005778D',4096,'Intel Core2 Duo CPU E7500 @ 2.93GHz','HP Compaq 4000 Pro SFF PC','Win 7','2021-05-07 10:50:04')
    ,('UCS009394D',4096,'Intel Core i3-4160T CPU @ 3.10GHz','HP ProOne 400 G1 AiO','Win 7','2021-05-04 10:47:10')
    ) AS T (micro, memoria,Processador,Model,OSname,Lastseen)
)
SELECT 
     micro
    ,memoria
    ,Processador
    ,Model
    ,OSname
    ,Lastseen 
FROM TAB

*/

 

Fico no aguardo.