How do i push dbxquery data to an index?

[robertlynch2020]

Hi

I have been running the following query on a DB using dbxquery and i want to push the data to an INDEX.
I was thinking of setting up an alert with additional _time(| eval time = now() ) and then push the data to an index every X minutes.

So i am unsure how to do this?
Below is an example of the query.

| dbxquery query="select  ProcName=program_name,Status=status,
         LoginName=convert(char(12),suser_name(suid)),HostName=hostname,ProcessId=hostprocess,
         Blk=blocked,DBName=convert(char(15),db_name(dbid)),
         Cmd_TransName=rtrim(convert(char(15),cmd))+'/'+rtrim(convert(char(15),tran_name)),
      CPU=cpu,PhyIOCmd=physical_io,TimeBlk=time_blocked,NetWorkPSize=network_pktsz,
      m.SPID,creationTime=s.loggedindatetime,m.CPUTime,m.WaitTime,memUsageKB=m.MemUsageKB,m.LogicalReads,m.PhysicalReads,m.LocksHeld,m.TableAccesses,m.IndexAccesses
from master..sysprocesses s , master.dbo.monProcessActivity m
where m.SPID = s.spid order by DBName" connection="PAC-PLO-ENV"

Thanks in advance
Rob

[nickhills · Accepted solution]

You could use collect

| dbxquery query="select  ProcName=program_name,Status=status, LoginName=convert(char(12),suser_name(suid)),HostName=hostname,ProcessId=hostprocess, Blk=blocked,DBName=convert(char(15),db_name(dbid)), Cmd_TransName=rtrim(convert(char(15),cmd))+'/'+rtrim(convert(char(15),tran_name)), CPU=cpu,PhyIOCmd=physical_io,TimeBlk=time_blocked,NetWorkPSize=network_pktsz, m.SPID,creationTime=s.loggedindatetime,m.CPUTime,m.WaitTime,memUsageKB=m.MemUsageKB,m.LogicalReads,m.PhysicalReads,m.LocksHeld,m.TableAccesses,m.IndexAccesses
 from master..sysprocesses s , master.dbo.monProcessActivity m
 where m.SPID = s.spid order by DBName" connection="PAC-PLO-ENV"
|collect index=yourindex source=yoursource

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Collect

[robertlynch2020]

Brill and thanks 🙂

Can you change you comment to an answer and i will accept it please.