All Apps and Add-ons

How do i push dbxquery data to an index?

robertlynch2020
Motivator

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

0 Karma
1 Solution

nickhills
Ultra Champion

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

If my comment helps, please give it a thumbs up!

View solution in original post

0 Karma

nickhills
Ultra Champion

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

If my comment helps, please give it a thumbs up!
0 Karma

robertlynch2020
Motivator

Brill and thanks 🙂

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...