Announcement

Collapse
No announcement yet.

CSV Extractor with massive CSV File / Windows HW Performance Stats

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • CSV Extractor with massive CSV File / Windows HW Performance Stats

    Hello Gravwell Team...

    I currently have an elaborate set up for documenting Windows Performance (CPU Load, CPU Temp, etc) and I am working on building an extractor for it...
    (attached is the TXT file, regularly named .csv but the Forum would not allow CSV extension)

    The Extractor will have this (however not complete) in the parameters section:
    Code:
    csv [1]  as Date-Time [2] as CPU01-Core01 [3] as CPU01-Core02 [4] as CPU-Core03 [5] as CPU01-Core04  [6] as CPU01-Core05  [7] as CPU01-Core06 [8] as CPU01-Total [9] as CPU01-TempCore01 [10] as CPU01-TempCore02 [11] as CPU01-TempCore02 [12] as CPU01-TempCore03 [13] as CPU01-TempCore04 [14] as CPU01-TempCore05 [15] as CPU01-TempCore06
    There is more to add - however I am trying to figure out if this the proper way to approach this?

    Name all the headers of the CSV file - even though there are technically two headers per column, then I'd need to graph them.

    Finally, the FIRST column on the CSV is the TIME/DATE stamp associated with the log line - this is clearly crucial to the log line so I know what CPU Load, Temp, and other items were at that moment.

    Does this make sense?







    Attached Files

  • #2
    Hey Dustin,

    Lets start with a simple AX definition to get you started with handling semi-opaque data. This is going to be a little rough because the openhardware output is pretty nasty and will be different on different machines with different CPU counts and/or hardware. Using something that can collect this data and output in a form that works across multiple hardware classes is probably a better long term solution.


    I am going to recommend a few things.

    First, setup the file follower to ignore the header values so that you don't constantly get the headers as entries any time the file refreshes. Add BOTH of the following lines to the file follower stanza:
    Ignore-Line-Prefix=","
    Ignore-Line-Prefix="Time"

    This will tell file follower to ignore any line that starts with a comma (basically the weird first header) and anything that starts with "Time" the real header

    I am also going to assume you are sending this data over under the tag "openhardware", if not, adjust as needed.
    ​​​​​​

    Next we are going to create an autoextractor to crack apart this CSV and make it easier to use. Navigate to t the Extractors page.

    Then create a new extractor (name and description are up to you), with the tag set to the tag you are using for ingest. In this case "openhardware"

    Then set the Module to "csv", and for the Parameters past in the first line of your CSV. You can add "time" to the start to also pull out the time column.

    This will create an autoextractor that will crack the CSV using the 'ax' module.

    This will allow an extremely simply query of: tag=openhardware ax | table to see all fields.

    Next you can go back to that extractor and clean up the fields to get better names, I am going to change them to the following:

    time,cpu_load_1,cpu_load_2,cpu_load_3,cpu_load_4, cpu_load_0,cpu_temperature_0,cpu_temperature_1,cpu _temperature_2,cpu_temperature_3,cpu_temperature_4 ,cpu_clock_1,cpu_clock_2,cpu_clock_3,cpu_clock_4,c pu_power_0,cpu_power_1,cpu_power_2,cpu_power_3,cpu _clock_0,ram_load_0,ram_data_0,ram_data_1,nvidiagp u_0_clock_0,nvidiagpu_0_clock_1,nvidiagpu_0_clock_ 2,nvidiagpu_0_smalldata_3,nvidiagpu_0_smalldata_2, nvidiagpu_0_smalldata_1,nvidiagpu_0_load_4,hdd_0_l oad_0


    Next, lets look at using the ax module with the stats module to get a graph of CPU on core 1:

    tag=openhardware ax cpu_load_1 | stats max(cpu_load_1) as cpu1_load | chart cpu1_load


    Or the system "ram load" over time:
    tag=openhardware ax ram_load_0 | stats max(ram_load_0) as ramload | chart ramload


    Long story short, this data format is pretty brutal given that its a CSV with inconsistent columns across systems with different hardware. The AX module can help make it hurt less, but the data format will change from openhardware depending on the hardware in the system.

    This should get you started for a single system though.

    Comment


    • #3
      OK, thank you.

      When I add the lines above - the gravwell service will not start. When I remove it - the service starts.

      Code:
      [Global]
      Ingest-Secret = IngestSecrets
      Connection-Timeout = 0
      Verify-Remote-Certificates = true
      Cleartext-Backend-target=10.10.1.21:4023 #example of adding a cleartext connection
      #Cleartext-Backend-target=127.1.0.1:4023 #example of adding another cleartext connection
      #Encrypted-Backend-target=127.1.1.1:4024 #example of adding an encrypted connection
      State-Store-Location="c:\\Program Files\\gravwell\\file_follow.state"
      #Ingest-Cache-Path="c:\\Program Files\\gravwell\\file_follow.cache"
      #Max-Ingest-Cache=1024 #Number of MB to store, localcache will only store 1GB before stopping. This is a safety net
      Log-Level=INFO #options are OFF INFO WARN ERROR
      
      
      #basic default logger, all entries will go to the default tag
      #no Tag-Name means use the default tag
      [Follower "NetCamHW"]
      Base-Directory="C:\\Users\\adminuser\\Documents\\OpenHa rdwareMonitor"
      File-Filter="*.csv"
      Tag-Name=NetCamHW
      Ignore-Line-Prefix=","
      Ignore-Line-Prefix="Time"
      Assume-Local-Timezone=true #Default for assume localtime is false


      Attached Files
      Last edited by dustinf; 08-31-2020, 07:40 PM.

      Comment


      • #4
        Could you show us what's in the error message just above the info message? I wonder if that might have more information.

        Comment


        • #5
          Originally posted by jfloren View Post
          Could you show us what's in the error message just above the info message? I wonder if that might have more information.
          I will get the screen shot, however its the same error text. that I recall.

          Just confirmed - both say Incorrect Function.
          Last edited by dustinf; 09-01-2020, 08:26 PM.

          Comment


          • #6
            OK, My Love for Gravwell grows so well...
            Click image for larger version

Name:	Gravwell_CPU_Load.PNG
Views:	30
Size:	196.6 KB
ID:	51

            I find I have this trouble in other queries - how do I get "units" of measure ?
            This would be in percentage load, I know I can adjust the chart to show scale and have it show the 100 Mark - but I dont know how to get labels for such.

            Comment


            • #7
              Oh nice!

              Looks like you got it sorted and eating, I would still like to run down the error you saw where the service wouldn't start. Any exact error messages would be greatly appreciated.

              As far as adding units, the easiest is typically to just do it when reassigning the output value of the stats operation.

              The charting display does not currently support forcing the scale or appending units on the axis legend. I am going to forward this chain to our UX team to see if there is a path do doing that.

              Thank you for the update!
              Kris

              Comment


              • #8
                Originally posted by traetox View Post
                Oh nice!

                Looks like you got it sorted and eating, I would still like to run down the error you saw where the service wouldn't start. Any exact error messages would be greatly appreciated.
                I was able to get this window pop up when I start the service manually or restarted the service after editing the lines into the follower section:
                Click image for larger version  Name:	Gravwell_File_Follower_Service_Error_After_Restarting_Service_After_editing_ignore_Config.png Views:	1 Size:	43.7 KB ID:	60

                I also have another issue....

                It looks like the file follower fails at some point and restarting the service doesnt fix - its been requiring a full windows system restart.

                I have no idea when it stops (EDIT: Yes, I know when it stops cuz of the data stops and the graphs stop)
                I just run the query Gravwell and then have missing data... This looks like its more of the setup than the file follower and I do wish I knew how to figure out if the file follower is running or doesnt see the log updating...
                . Click image for larger version  Name:	Gravwell_CPU_Load__DataMissing.PNG Views:	1 Size:	142.6 KB ID:	59




                Attached Files
                Last edited by dustinf; 09-03-2020, 08:59 PM.

                Comment

                Working...
                X